Native Dynamic SQL

What Is Dynamic SQL?
Dynamic SQL refers to DDL, DML, and query statements that are constructed, parsed, and executed at runtime. It is called dynamic because the SQL statement you want to run is not fully known (or static) when you write and compile your code. Most of the time, you need input from the user, such as the columns that person wants to see or some element of the WHERE clause, to complete the SQL statement.
Here is an example of a dynamic DDL statement:
'DROP TABLE ' || l_my_table

Dynamic PL/SQL refers to anonymous PL/SQL blocks that are constructed, compiled, and executed at runtime. Here is an example of a dynamic PL/SQL block:
'BEGIN report_pkg.run_report (' || l_report_id || ');'

Dynamic PL/SQL statements must end in a semi- colon; dynamic SQL statements may not end in a semicolon.

You need dynamic SQL in the following situations:

• You want to execute a SQL data definition statement (such as CREATE), a data control statement (such as GRANT), or a session control statement (such as ALTER SESSION). In PL/SQL, such statements cannot be executed statically.

• You want more flexibility. For example, you might want to defer your choice of schema objects until run time. Or, you might want your program to build different search conditions for the WHERE clause of a SELECT statement. A more complex program might choose from various SQL operations, clauses, etc.

• You use package DBMS_SQL to execute SQL statements dynamically, but you want better performance, something easier to use, or functionality that DBMS_SQL lacks such as support for objects and collections

Using 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;
BEGIN
 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 don't need to 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;

Improve Performance Using Bind Variables

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;

Error Handling with NDS

• Don't construct your dynamic string in the EXECUTE IMMEDIATE statement. Instead, put the string together and assign it to a variable. Then pass that variable to the EXECUTE IMMEDIATE statement. If the dynamic SQL statement fails, you can then display or log that string as part of your error handling.
• Include an exception section in the block of code that contains the EXECUTE IMMEDIATE statement. In other words, handle the error as close as possible to where it was raised. You will then have access to as much information as possible about why the failure occurred.
• Consolidate all error handling logic in a local procedure to ensure consistent error handling and minimize code redundancy. The code in Listing 2 displays the error stack using DBMS_UTILITY.FORMAT_ERROR_STACK; the error backtrace using DBMS_UTILITY.FORMAT_ERROR_BACKTRACE (available only in Oracle 10g; it tells you the precise line on which the error was raised); and most important, the dynamic SQL string.
• Include specific handlers (within the exception section) for errors that commonly occur, but you should certainly also include a WHEN OTHERS clause. When writing Listing 2, I realized that a common error will be when a user passes an invalid table name. So I declared a local, named exception for the ORA-00942 error and then provided a simple, clear message to help resolve the problem, as shown when I run the tabcount function with an incorrect table name:

• You need to execute a dynamic SQL string that is longer than 32K characters. EXECUTE IMMEDIATE parses and runs a single string, which means that it is limited to 32K. There may well be cases, such as with very large generated SQL statements and compilation of DDL (such as CREATE OR REPLACE PACKAGE>), where you will exceed that limit. In such cases, use the overloading of DBMS_SQL.PARSE that accepts a collection of strings and then parses the statement formed from the concatenation of all those strings.
• You need to use Method 4 Dynamic SQL. Method 4 is the most complex and generic of dynamic SQL forms. It is appropriate when you do not know, when you write your code, either of the following: how many columns you are retrieving in a query or how many variable values you are binding into your string. Although it is possible to implement Method 4 dynamic SQL in NDS, the task is actually more straightforward with DBMS_SQL.
• You want to minimize the parsing of your SQL statement. One downside of NDS is that your SQL statement will be parsed each time it is executed, even if the same statement has been parsed just a moment ago. If your statement is large and complex, that overhead can be significant. With DBMS_SQL you can bypass the parse phase and simply reexecute that same statement with different bind variables.

CREATE OR REPLACE PROCEDURE compile_from_file ( dir_in    IN   VARCHAR2, 
                                                file_in   IN   VARCHAR2)
IS
   l_file    UTL_FILE.file_type;
   l_lines   DBMS_SQL.varchar2s;
   l_cur     PLS_INTEGER        := DBMS_SQL.open_cursor;
BEGIN
   l_file := UTL_FILE.fopen (dir_in, file_in, 'R');
   BEGIN
      LOOP
         UTL_FILE.get_line (l_file, l_lines (l_lines.COUNT + 1));
      END LOOP;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         NULL;
   END;

   UTL_FILE.fclose (l_file);
   DBMS_SQL.parse (c => l_cur, statement => l_lines, lb => l_lines.FIRST, ub => l_lines.LAST, lfflg => TRUE, language_flag =>DBMS_SQL.native);
   DBMS_SQL.close_cursor (l_cur);
EXCEPTION
   WHEN OTHERS THEN
      UTL_FILE.fclose (l_file);
      DBMS_SQL.close_cursor (l_cur);
      DBMS_OUTPUT.put_line ('Compile from file failure: ');
      DBMS_OUTPUT.put_line (SUBSTR (SQLERRM, 1, 255));
END compile_from_file;
/

   DBMS_SQL.parse (c => l_cur,statement => l_lines,lb => l_lines.FIRST,ub => l_lines.LAST,lfflg => TRUE,language_flag =>DBMS_SQL.native);