Native Dynamic SQL

You need dynamic SQL in the following situations:

Using the EXECUTE IMMEDIATE Statement

The EXECUTE IMMEDIATE statement prepares (parses) and immediately executes a dynamic SQL statement or an anonymous PL/SQL block. The syntax is

EXECUTE IMMEDIATE dynamic_string
[INTO {define_variable[, define_variable]... | record}]
[USING [IN | OUT | IN OUT] bind_argument
[, [IN | OUT | IN OUT] bind_argument]...]
[{RETURNING | RETURN} INTO bind_argument[, bind_argument]...];

where:
dynamic_string is a string expression that represents a SQL statement or PL/SQL block,
define_variable is a variable that stores a selected column value, and record is a user-defined or %ROWTYPE record that stores a selected row.
An input bind_argument is an expression whose value is passed to the dynamic SQL statement or PL/SQL block.
An output bind_argument is a variable that stores a value returned by the dynamic SQL statement or PL/SQL block.

Except for multi-row queries, the dynamic string can contain any SQL statement (without the terminator) or any PL/SQL block (with the terminator). The string can also contain placeholders for bind arguments.

Used only for single-row queries, the INTO clause specifies the variables or record into which column values are retrieved. For each value retrieved by the query, there must be a corresponding, type-compatible variable or field in the INTO clause.

Used only for DML statements that have a RETURNING clause (without a BULK COLLECT clause), the RETURNING INTO clause specifies the variables into which column values are returned. For each value returned by the DML statement, there must be a corresponding, type-compatible variable in the RETURNING INTO clause.

You can place all bind arguments in the USING clause. The default parameter mode is IN. For DML statements that have a RETURNING clause, you can place OUT arguments in the RETURNING INTO clause without specifying the parameter mode, which, by definition, is OUT. If you use both the USING clause and the RETURNING INTO clause, the USING clause can contain only IN arguments.

At run time, bind arguments replace corresponding placeholders in the dynamic string. So, every placeholder must be associated with a bind argument in the USING clause and/or RETURNING INTO clause. You can use numeric, character, and string literals as bind arguments, but you cannot use Boolean literals (TRUE, FALSE, and NULL). To pass nulls to the dynamic string, you must use a workaround

Dynamic SQL supports all the SQL datatypes. So, for example, define variables and bind arguments can be collections, LOBs, instances of an object type, and refs. As a rule, dynamic SQL does not support PL/SQL-specific types. So, for example, define variables and bind arguments cannot be Booleans or index-by tables. The only exception is that a PL/SQL record can appear in the INTO clause.

You can execute a dynamic SQL statement repeatedly using new values for the bind arguments. However, you incur some overhead because EXECUTE IMMEDIATE re-prepares the dynamic string before every execution.

Some Examples of Dynamic SQL

The following PL/SQL block contains several examples of dynamic SQL:

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
EXECUTE IMMEDIATE 'CREATE TABLE bonus (id NUMBER, amt NUMBER)';

sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)';
EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location;

sql_stmt := 'SELECT * FROM emp WHERE empno = :id';
EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;

plsql_block := 'BEGIN emp_pkg.raise_salary(:id, :amt); END;';
EXECUTE IMMEDIATE plsql_block USING 7788, 500;

sql_stmt := 'UPDATE emp SET sal = 2000 WHERE empno = :1
RETURNING sal INTO :2';
EXECUTE IMMEDIATE sql_stmt USING emp_id RETURNING INTO salary;

EXECUTE IMMEDIATE 'DELETE FROM dept WHERE deptno = :num'
USING dept_id;

EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE TRUE';
END;

In the example below, a standalone procedure accepts the name of a database table (such as 'emp') and an optional WHERE-clause condition (such as 'sal > 2000'). If you omit the condition, the procedure deletes all rows from the table. Otherwise, the procedure deletes only those rows that meet the condition.

CREATE PROCEDURE delete_rows (
table_name IN VARCHAR2,
condition IN VARCHAR2 DEFAULT NULL) AS
where_clause VARCHAR2(100) := ' WHERE ' || condition;
IF condition IS NULL THEN where_clause := NULL; END IF;
EXECUTE IMMEDIATE 'DELETE FROM ' || table_name || where_clause;
EXCEPTION
...
END;

Backward Compatibility of the USING Clause

When a dynamic INSERT, UPDATE, or DELETE statement has a RETURNING clause, output bind arguments can go in the RETURNING INTO clause or the USING clause. In new applications, use the RETURNING INTO clause. In old applications, you can continue to use the USING clause. For example, both of the following EXECUTE IMMEDIATE statements are legal:

DECLARE
sql_stmt VARCHAR2(200);
my_empno NUMBER(4) := 7902;
my_ename VARCHAR2(10);
my_job VARCHAR2(9);
my_sal NUMBER(7,2) := 3250.00;
BEGIN
sql_stmt := 'UPDATE emp SET sal = :1 WHERE empno = :2
RETURNING ename, job INTO :3, :4';

/* Bind returned values through USING clause. */
EXECUTE IMMEDIATE sql_stmt
USING my_sal, my_empno, OUT my_ename, OUT my_job;

/* Bind returned values through RETURNING INTO clause. */
EXECUTE IMMEDIATE sql_stmt
USING my_sal, my_empno RETURNING INTO my_ename, my_job;
...
END;

Specifying Parameter Modes

With the USING clause, you need not specify a parameter mode for input bind arguments because the mode defaults to IN. With the RETURNING INTO clause, you cannot specify a parameter mode for output bind arguments because, by definition, the mode is OUT. An example follows:

DECLARE
sql_stmt VARCHAR2(200);
dept_id NUMBER(2) := 30;
old_loc VARCHAR2(13);
BEGIN
sql_stmt :=
'DELETE FROM dept WHERE deptno = :1 RETURNING loc INTO :2';
EXECUTE IMMEDIATE sql_stmt USING dept_id RETURNING INTO old_loc;
...
END;

When appropriate, you must specify the OUT or IN OUT mode for bind arguments passed as parameters. For example, suppose you want to call the following standalone procedure:

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;

 
Now, using dynamic SQL, you can write a package of procedures that uses these types, as follows:

CREATE PACKAGE teams AS
PROCEDURE create_table (tab_name VARCHAR2);
PROCEDURE insert_row (tab_name VARCHAR2, p Person, h Hobbies);
PROCEDURE print_table (tab_name VARCHAR2);
END;

CREATE PACKAGE BODY teams AS
PROCEDURE create_table (tab_name VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE ' || tab_name ||
' (pers Person, hobbs Hobbies)';
END;

PROCEDURE insert_row (
tab_name VARCHAR2,
p Person,
h Hobbies) IS
BEGIN
EXECUTE IMMEDIATE 'INSERT INTO ' || tab_name ||
' VALUES (:1, :2)' USING p, h;
END;

PROCEDURE print_table (tab_name VARCHAR2) IS
TYPE RefCurTyp IS REF CURSOR;
cv RefCurTyp;
p Person;
h Hobbies;
BEGIN
OPEN cv FOR 'SELECT pers, hobbs FROM ' || tab_name;
LOOP
FETCH cv INTO p, h;
EXIT WHEN cv%NOTFOUND;
-- print attributes of 'p' and elements of 'h'
END LOOP;
CLOSE cv;
END;
END;

Using Bulk Dynamic SQL

In this section, you learn how to add the power of bulk binding to dynamic SQL. Bulk binding improves performance by minimizing the number of context switches between the PL/SQL and SQL engines. With bulk binding, entire collections, not just individual elements, are passed back and forth.

Using the following commands, clauses, and cursor attribute, your applications can construct bulk SQL statements, then execute them dynamically at run time:

BULK FETCH statement
BULK EXECUTE IMMEDIATE statement
FORALL statement
COLLECT INTO clause
RETURNING INTO clause
%BULK_ROWCOUNT cursor attribute

Syntax for Dynamic Bulk Binds

Bulk binding lets Oracle bind a variable in a SQL statement to a collection of values. The collection type can be any PL/SQL collection type (index-by table, nested table, or varray). However, the collection elements must have a SQL datatype such as CHAR, DATE, or NUMBER. Three statements support dynamic bulk binds: EXECUTE IMMEDIATE, FETCH, and FORALL.

Bulk EXECUTE IMMEDIATE

This statement lets you bulk-bind define variables or OUT bind arguments passed as parameters to a dynamic SQL statement. The syntax follows:

EXECUTE IMMEDIATE dynamic_string
[[BULK COLLECT] INTO define_variable[, define_variable ...]]
[USING bind_argument[, bind_argument ...]]
[{RETURNING | RETURN}
BULK COLLECT INTO bind_argument[, bind_argument ...]];

With a dynamic multi-row query, you can use the BULK COLLECT INTO clause to bind define variables. The values in each column are stored in a collection.

With a dynamic INSERT, UPDATE, or DELETE statement that returns multiple rows, you can use the RETURNING BULK COLLECT INTO clause to bulk-bind output variables. The returned rows of values are stored in a set of collections.

Bulk FETCH

 This statement lets you fetch from a dynamic cursor the same way you fetch from a static cursor. The syntax follows:

FETCH dynamic_cursor 
BULK COLLECT INTO define_variable[, define_variable ...];

If the number of define variables in the BULK COLLECT INTO list exceeds the number of columns in the query select-list, Oracle generates an error.

Bulk FORALL

This statement lets you bulk-bind input variables in a dynamic SQL statement. In addition, you can use the EXECUTE IMMEDIATE statement inside a FORALL loop. The syntax follows:

FORALL index IN lower bound..upper bound
EXECUTE IMMEDIATE dynamic_string
USING bind_argument | bind_argument(index)
[, bind_argument | bind_argument(index)] ...
[{RETURNING | RETURN} BULK COLLECT
INTO bind_argument[, bind_argument ... ]];

The dynamic string must represent an INSERT, UPDATE, or DELETE statement (not a SELECT statement).

Examples of Dynamic Bulk Binds

You can bind define variables in a dynamic query using the BULK COLLECT INTO clause. As the following example shows, you can use that clause in a bulk FETCH or bulk EXECUTE IMMEDIATE statement:

DECLARE
TYPE EmpCurTyp IS REF CURSOR;
TYPE NumList IS TABLE OF NUMBER;
TYPE NameList IS TABLE OF VARCHAR2(15);
emp_cv EmpCurTyp;
empnos NumList;
enames NameList;
sals NumList;
BEGIN
OPEN emp_cv FOR 'SELECT empno, ename FROM emp';
FETCH emp_cv BULK COLLECT INTO empnos, enames;
CLOSE emp_cv;

EXECUTE IMMEDIATE 'SELECT sal FROM emp'
BULK COLLECT INTO sals;
END;

Only the INSERT, UPDATE, and DELETE statements can have output bind variables. To bulk-bind them, you use the BULK RETURNING INTO clause, which can appear only in an EXECUTE IMMEDIATE. An example follows:

DECLARE
TYPE NameList IS TABLE OF VARCHAR2(15);
enames NameList;
bonus_amt NUMBER := 500;
sql_stmt VARCHAR(200);
BEGIN
sql_stmt := 'UPDATE emp SET bonus = :1 RETURNING ename INTO :2';
EXECUTE IMMEDIATE sql_stmt
USING bonus_amt RETURNING BULK COLLECT INTO enames;
END;

To bind the input variables in a SQL statement, you can use the FORALL statement and USING clause, as shown below. However, the SQL statement cannot be a query.
DECLARE
TYPE NumList IS TABLE OF NUMBER;
TYPE NameList IS TABLE OF VARCHAR2(15);
empnos NumList;
enames NameList;
BEGIN
empnos := NumList(1,2,3,4,5);
FORALL i IN 1..5
EXECUTE IMMEDIATE
'UPDATE emp SET sal = sal * 1.1 WHERE empno = :1
RETURNING ename INTO :2'
USING empnos(i) RETURNING BULK COLLECT INTO enames;
...
END;

Improving Performance

In the example below, Oracle opens a different cursor for each distinct value of emp_id. This can lead to resource contention and poor performance.

CREATE PROCEDURE fire_employee (emp_id NUMBER) AS
BEGIN
EXECUTE IMMEDIATE
'DELETE FROM emp WHERE empno = ' || TO_CHAR(emp_id);
END;

You can improve performance by using a bind variable, as shown below. This allows Oracle to reuse the same cursor for different values of emp_id.

CREATE PROCEDURE fire_employee (emp_id NUMBER) AS
BEGIN
EXECUTE IMMEDIATE
'DELETE FROM emp WHERE empno = :num' USING emp_id;
END;