Tag Archives: delete in dynamic

Dynamic SQL in Oracle

Dynamic SQL in Oracle

Dynamic SQL is used to execute the things at run time. It is used for executing DDL Statement in the PL/SQL Blocks becauze you can not execute DDL directly in PL/SQL Blocks.

Following are the examples for using the Dynamic SQL
Following is the table used in the examples
Name: TEST
DATA:
SQL> select * from TEST;
NAME ID
—— ——–
RAM 1
SHAM 2

SELECT in Dynamic SQL
Pass the ID column and table name TEST as parameter in which dynamic query return the NAME column value as return output..

set serveroutput on
Declare
p_id varchar2(100) := '1';
p_table_name varchar2(100) := 'TEST';
VALUE VARCHAR2(100);
BEGIN
EXECUTE IMMEDIATE 'select name from ' || p_table_name ||' where id = :P' INTO VALUE USING p_id;
DBMS_OUTPUT.put_line(value);
END;
/

OUTPUT:
------------
RAM

INSERT in Dynamic SQL

DECLARE
query_text VARCHAR2(1000) := 'insert into test(id, NAME) values (:P_ID, :P_VAL)';
id NUMBER := 3;
VALUE VARCHAR2(100) := 'RAMESH';
BEGIN
EXECUTE IMMEDIATE query_text USING id, VALUE;
END;
/

OUTPUT:
SQL> select * from TEST;
NAME ID
------- -------
RAMESH 3
RAM 1
SHAM 2

Update values in dynamic SQL

DECLARE
query_text VARCHAR2(1000) := 'update test set name = :P_VAL where id = :P_ID';
id NUMBER := 3;
VALUE VARCHAR2(100) := 'RAKESH';
BEGIN
EXECUTE IMMEDIATE query_text USING VALUE,ID;
END;
/

OUTPUT:
SQL> select * from test;
NAME ID
---------- ----------
RAKESH 3
RAM 1
SHAM 2

NOTE: It means you should correct the sequence of input as query written inside the dynamic query clauses. otherwise following error occurred:
Error:
EXECUTE IMMEDIATE query_text USING id, VALUE;
— Error occurred due to update query use varchar2 first in sequence then number
DECLARE
*
ERROR at line 1:
ORA-01722: invalid number
ORA-06512: at line 6
Solution:
Correct the sequence of parameter passing in dynamic SQL.

Delete value in Dynmaic SQL

DECLARE
query_text VARCHAR2(1000) := 'delete from test where id = :P_ID';
id NUMBER := 3;
BEGIN
EXECUTE IMMEDIATE query_text USING ID;
END;
/

OUTPUT:
SQL> select * from test;
NAME ID
---------- ----------
RAM 1
SHAM 2

DDL Statements execute with Dynamic SQL

BEGIN
EXECUTE IMMEDIATE 'create table TEST_BKP (id number, column_value varchar2(100))';
END;
/

OUTPUT:
SQL> desc test_bkp;
Name Null? Type
-------------- -------- ---------------
ID NUMBER
COLUMN_VALUE VARCHAR2(100)

Advertisements