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)
