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)