PL/SQL - Execute Immediate

About

The immediate statement is used in several areas :

SQL dynamic for DML (SELECT, UPDATE, INSERT, DELETE) and DDL (CREATE, ALTER, DROP)
Execution of PlSql program.

Articles Related

Example DML, DDL, PLSQL Call

DECLARE
   sql_stmt    VARCHAR2(200);
   plsql_block VARCHAR2(500);
   emp_id      NUMBER(4) := 7566;
   salary      NUMBER(7,2);
   dept_id     NUMBER(2) := 50;
   dept_name   VARCHAR2(14) := 'PERSONNEL';
   location    VARCHAR2(13) := 'DALLAS';
   emp_rec     emp%ROWTYPE;
BEGIN
   
   -- DDL Create
   EXECUTE IMMEDIATE 'CREATE TABLE bonus (id NUMBER, amt NUMBER)';
   
   -- DDL Alter
   EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE TRUE';

   -- DML Insert
   sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)';
   EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location;
   
   -- DML Select
   sql_stmt := 'SELECT * FROM emp WHERE empno = :id';
   EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;
   
   -- PLSQL Call
   plsql_block := 'BEGIN emp_pkg.raise_salary(:id, :amt); END;';
   EXECUTE IMMEDIATE plsql_block USING 7788, 500;
   
   -- DML Update
   sql_stmt := 'UPDATE emp SET sal = 2000 WHERE empno = :1
      RETURNING sal INTO :2';
   EXECUTE IMMEDIATE sql_stmt USING emp_id RETURNING INTO salary;
   
   -- DML Delete
   EXECUTE IMMEDIATE 'DELETE FROM dept WHERE deptno = :num'
      USING dept_id;

END;
/

-- DML Delete with condition
CREATE OR REPLACE PROCEDURE delete_rows (
   table_name IN VARCHAR2,
   condition IN VARCHAR2 DEFAULT NULL) AS
   where_clause VARCHAR2(100) := ' WHERE ' || condition;
BEGIN
   IF condition IS NULL THEN where_clause := NULL; END IF;
   EXECUTE IMMEDIATE 'DELETE FROM ' || table_name || where_clause;
END;
/

BULK COLLECT INTO

PL/SQL - Bulk Collect - Fetch collection of (records|Collection)

DECLARE
  TYPE t_bulk_collect_test_tab IS TABLE OF bulk_collect_test%ROWTYPE;

  l_tab     t_bulk_collect_test_tab;
  l_cursor  SYS_REFCURSOR;
BEGIN
  OPEN l_cursor FOR 'SELECT * FROM bulk_collect_test';
  FETCH l_cursor
  BULK COLLECT INTO l_tab;
  CLOSE l_cursor; 

  DBMS_OUTPUT.put_line('Dynamic FETCH  : ' || l_tab.count);

  -- With Execute Immediate Statement
  EXECUTE IMMEDIATE 'SELECT * FROM bulk_collect_test'
  BULK COLLECT INTO l_tab;
  DBMS_OUTPUT.put_line('Dynamic EXECUTE: ' || l_tab.COUNT);


END;
/

RETURNING BULK COLLECT INTO

You can use the RETURNING BULK COLLECT INTO clause with the EXECUTE IMMEDIATE statement to store the results of an INSERT, UPDATE, or DELETE statement in a set of collections. Only INSERT, UPDATE, and DELETE statements can have output bind variables. You bulk-bind them with the RETURNING BULK COLLECT INTO clause of EXECUTE IMMEDIATE.

DECLARE
   TYPE NameList IS TABLE OF VARCHAR2(15);
   enames    NameList;
   bonus_amt NUMBER := 50;
   sql_stmt  VARCHAR(200);
BEGIN
   sql_stmt := 'UPDATE employees SET salary = salary + :1 
                RETURNING last_name INTO :2';
   EXECUTE IMMEDIATE sql_stmt
      USING bonus_amt RETURNING BULK COLLECT INTO enames;
END;
/

You can use the BULK COLLECT INTO clause with the FETCH statement to store values from each column of a cursor in a separate collection.

FORALL

You can put an EXECUTE IMMEDIATE statement with the RETURNING BULK COLLECT INTO inside a FORALL statement. You can store the results of all the INSERT, UPDATE, or DELETE statements in a set of collections.

To bind the input variables in a SQL statement, you can use the FORALL statement and USING clause. The SQL statement cannot be a query.

DECLARE
   TYPE NumList IS TABLE OF NUMBER;
   TYPE NameList IS TABLE OF VARCHAR2(15);
   empids NumList;
   enames NameList;
BEGIN
   empids := NumList(101,102,103,104,105);
   FORALL i IN 1..5
      EXECUTE IMMEDIATE
        'UPDATE employees SET salary = salary * 1.04 WHERE employee_id = :1
         RETURNING last_name INTO :2'
         USING empids(i) RETURNING BULK COLLECT INTO enames;
END;
/

You can pass subscripted collection elements to the EXECUTE IMMEDIATE statement through the USING clause. You cannot concatenate the subscripted elements directly into the string argument to EXECUTE IMMEDIATE; for example, you cannot build a collection of table names and write a FORALL statement where each iteration applies to a different table.

IN and OUT for Bind Variables

With the USING clause, the mode defaults to IN, so you do not need to specify a parameter mode for input bind arguments.

With the RETURNING INTO clause, the mode is OUT, so you cannot specify a parameter mode for output bind arguments.

You must specify the parameter mode in more complicated cases, such as this one where you call a procedure from a dynamic PL/SQL block:

CREATE PROCEDURE create_dept (
   deptno IN OUT NUMBER,
   dname  IN VARCHAR2,
   loc    IN VARCHAR2) AS
BEGIN
   SELECT deptno_seq.NEXTVAL INTO deptno FROM dual;
   INSERT INTO dept VALUES (deptno, dname, loc);
END;
/

To call the procedure from a dynamic PL/SQL block, you must specify the IN OUT mode for the bind argument associated with formal parameter deptno, as follows:

DECLARE
   plsql_block VARCHAR2(500);
   new_deptno NUMBER(2);
   new_dname  VARCHAR2(14) := 'ADVERTISING';
   new_loc    VARCHAR2(13) := 'NEW YORK';
BEGIN
   plsql_block := 'BEGIN create_dept(:a, :b, :c); END;';
   EXECUTE IMMEDIATE plsql_block
      USING IN OUT new_deptno, new_dname, new_loc;
   IF new_deptno > 90 THEN ...
END;
/