Example of using dynamic SQL statements in Oracle

Dynamic SQL query run the SQL code at run time in Oracle. It use EXECUTE IMMEDIATE for dynamic SQL. It will return value with INTO clause or pass value with USING clause.

Note: You can execute DDL queries with help of Dynamic SQL in PL/SQL.

Syntax:

EXECUTE IMMEDIATE 'query' INTO <out parameter> USING <in parameter>;

Example of using DDL operation of Dynamic SQL:

BEGIN
EXECUTE IMMEDIATE 'create table test (id number, name varchar2(50))';
END;
/

Example of using Select query in Dynamic SQL:

-- Create function 
CREATE OR REPLACE FUNCTION get_value(p_table_name VARCHAR2, p_id NUMBER) 
RETURN VARCHAR2 IS
v_emp_name VARCHAR2(100);
BEGIN
EXECUTE IMMEDIATE 'select first_name from ' || p_table_name ||' where employee_id = :P' INTO v_emp_name USING p_id;
RETURN v_emp_name;
END;
/

-- Execute the function
SQL> select hr.get_Value('hr.employees',206) from dual;
HR.GET_VALUE('HR.EMPLOYEES',206)
--------------------------------------
William

Example of DML statement in Dynamic SQL like INSERT, UPDATE or DELETE:

-- Create table for example
CREATE TABLE test (id NUMBER, name VARCHAR2(100));
INSERT INTO test VALUES (1, 'William');
INSERT INTO test VALUES (2, 'Scott');

-- PL/SQL blocks for showing example for all DML operations:
DECLARE
v_id NUMBER;
v_name VARCHAR2(100);
v_insert_query VARCHAR2(1000);
v_update_query VARCHAR2(1000);
v_delete_query VARCHAR2(1000);
BEGIN
-- Used for Insert Query
v_id := 3;
v_name := 'Brad';
v_insert_query := 'insert into test(id, name) values (:P_ID, :P_VAL)';
EXECUTE IMMEDIATE v_insert_query USING v_id, v_name;

--Used for update query
v_id := 2;
v_name := 'RAM';
v_update_query := 'update test set name = :P_VAL where id = :P_ID';
EXECUTE IMMEDIATE v_update_query USING v_name,v_id;

--for Delete Query
v_id := 1; 
v_delete_query := 'delete from test where id = :P_ID';
EXECUTE IMMEDIATE v_delete_query USING v_id;
END;
/