10
Native Dynamic SQL

A happy and gracious flexibility ... --Matthew Arnold

This chapter shows you how to use native dynamic SQL (dynamic SQL for short), a PL/SQL interface that makes your applications more flexible and versatile. You learn simple ways to write programs that can build and process SQL statements "on the fly" at run time.

Within PL/SQL, you can execute any kind of SQL statement (even data definition and data control statements) without resorting to cumbersome programmatic approaches. Dynamic SQL blends seamlessly into your programs, making them more efficient, readable, and concise.

Major Topics

What Is Dynamic SQL?
The Need for Dynamic SQL
Using the EXECUTE IMMEDIATE Statement
Using the OPEN-FOR, FETCH, and CLOSE Statements
Specifying Parameter Modes
Tips and Traps

What Is Dynamic SQL?

Most PL/SQL programs do a specific, predictable job. For example, a stored procedure might accept an employee number and salary increase, then update the sal column in the emp table. In this case, the full text of the UPDATE statement is known at compile time. Such statements do not change from execution to execution. So, they are called static SQL statements.

However, some programs must build and process a variety of SQL statements at run time. For example, a general-purpose report writer must build different SELECT statements for the various reports it generates. In this case, the full text of the statement is unknown until run time. Such statements can, and probably will, change from execution to execution. So, they are called dynamic SQL statements.

Dynamic SQL statements are stored in character strings built by your program at run time. Such strings must contain the text of a valid SQL statement or PL/SQL block. They can also contain placeholders for bind arguments. A placeholder is an undeclared identifier, so its name, to which you must prefix a colon, does not matter. For example, PL/SQL makes no distinction between the following strings:

'DELETE FROM emp WHERE sal > :my_sal AND comm < :my_comm'
'DELETE FROM emp WHERE sal > :s AND comm < :c'

To process most dynamic SQL statements, you use the EXECUTE IMMEDIATE statement. However, to process a multi-row query (SELECT statement), you must use the OPEN-FOR, FETCH, and CLOSE statements.

The Need for Dynamic SQL

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. (For a comparison with DBMS_SQL, see Oracle8i Application Developer's Guide - Fundamentals.)

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]...];

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, record is a user-defined or %ROWTYPE record that stores a SELECTed row, and bind_argument is an expression whose value is passed to the dynamic SQL statement or PL/SQL block. (The NOCOPY compiler hint is not allowed in an EXECUTE IMMEDIATE statement.)

Except for multi-row queries, the 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. However, you cannot use bind arguments to pass the names of schema objects to a dynamic SQL statement. For the right way, see "Passing the Names of Schema Objects".

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

You must put every bind argument in the USING clause. If you do not specify a parameter mode, it defaults to IN. At run time, any bind arguments in the USING clause replace corresponding placeholders in the SQL statement or PL/SQL block. So, every placeholder must be associated with a bind argument in the USING clause. Numeric, character, and string literals are allowed in the USING clause, but Boolean literals (TRUE, FALSE, NULL) are not. To pass nulls to the dynamic string, you must use a workaround (see "Passing Nulls").

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.

Caution: 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

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

DECLARE
   sql_stmt    VARCHAR2(100);
   plsql_block VARCHAR2(200);
   my_deptno   NUMBER(2) := 50;
   my_dname    VARCHAR2(15) := 'PERSONNEL';
   my_loc      VARCHAR2(15) := 'DALLAS';
   emp_rec     emp%ROWTYPE;
BEGIN
   sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)';
   EXECUTE IMMEDIATE sql_stmt USING my_deptno, my_dname, my_loc;

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

   EXECUTE IMMEDIATE 'DELETE FROM dept 
      WHERE deptno = :n' USING my_deptno;

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

   EXECUTE IMMEDIATE 'CREATE TABLE bonus (id NUMBER, amt NUMBER)';

   sql_stmt := 'ALTER SESSION SET SQL_TRACE TRUE';
   EXECUTE IMMEDIATE sql_stmt;
END;

In the example below, a stand-alone 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;

Using the OPEN-FOR, FETCH, and CLOSE Statements

You use three statements to process a dynamic multi-row query: OPEN-FOR, FETCH, and CLOSE. First, you OPEN a cursor variable FOR a multi-row query. Then, you FETCH rows from the result set one at a time. When all the rows are processed, you CLOSE the cursor variable. (For more information about cursor variables, see "Using Cursor Variables".)

Opening the Cursor Variable

The OPEN-FOR statement associates a cursor variable with a multi-row query, executes the query, identifies the result set, positions the cursor on the first row in the result set, then zeroes the rows-processed count kept by %ROWCOUNT.

Unlike the static form of OPEN-FOR, the dynamic form has an optional USING clause. At run time, bind arguments in the USING clause replace corresponding placeholders in the dynamic SELECT statement. The syntax is

OPEN {cursor_variable | :host_cursor_variable} FOR dynamic_string
   [USING bind_argument[, bind_argument]...];

where cursor_variable is a weakly typed cursor variable (one without a return type), host_cursor_variable is a cursor variable declared in a PL/SQL host environment such as an OCI program, and dynamic_string is a string expression that represents a multi-row query.

In the following example, you declare a cursor variable, then associate it with a dynamic SELECT statement that returns rows from the emp table:

DECLARE
   TYPE EmpCurTyp IS REF CURSOR;  -- define weak REF CURSOR type
   emp_cv   EmpCurTyp;  -- declare cursor variable
   my_ename VARCHAR2(15);
   my_sal   NUMBER := 1000;
BEGIN
   OPEN emp_cv FOR  -- open cursor variable
      'SELECT ename, sal FROM emp WHERE sal > :s' USING my_sal;
   ...
END;

Any bind arguments in the query are evaluated only when the cursor variable is opened. So, to fetch from the cursor using different bind values, you must reopen the cursor variable with the bind arguments set to their new values.

Fetching from the Cursor Variable

The FETCH statement returns a row from the result set of a multi-row query, assigns the values of select-list items to corresponding variables or fields in the INTO clause, increments the count kept by %ROWCOUNT, and advances the cursor to the next row. The syntax follows:

FETCH {cursor_variable | :host_cursor_variable}
   INTO {define_variable[, define_variable]... | record};

Continuing the example, you fetch rows from cursor variable emp_cv into define variables my_ename and my_sal:

LOOP
   FETCH emp_cv INTO my_ename, my_sal;  -- fetch next row
   EXIT WHEN emp_cv%NOTFOUND;  -- exit loop when last row is fetched
   -- process row
END LOOP;

For each column value returned by the query associated with the cursor variable, there must be a corresponding, type-compatible variable or field in the INTO clause. You can use a different INTO clause on separate fetches with the same cursor variable. Each fetch retrieves another row from the same result set.

If you try to fetch from a closed or never-opened cursor variable, PL/SQL raises the predefined exception INVALID_CURSOR.

Closing the Cursor Variable

The CLOSE statement disables a cursor variable. After that, the associated result set is undefined. The syntax follows:

CLOSE {cursor_variable | :host_cursor_variable};

In this example, when the last row is processed, you close cursor variable emp_cv:

LOOP
   FETCH emp_cv INTO my_ename, my_sal;
   EXIT WHEN emp_cv%NOTFOUND;
   -- process row
END LOOP;
CLOSE emp_cv;  -- close cursor variable

If you try to close an already-closed or never-opened cursor variable, PL/SQL raises INVALID_CURSOR.

Some Examples

As the following example shows, you can fetch rows from the result set of a dynamic multi-row query into a record:

DECLARE
   TYPE EmpCurTyp IS REF CURSOR;
   emp_cv   EmpCurTyp;
   emp_rec  emp%ROWTYPE;
   sql_stmt VARCHAR2(100);
   my_job   VARCHAR2(15) := 'CLERK';
BEGIN
   sql_stmt := 'SELECT * FROM emp WHERE job = :j';
   OPEN emp_cv FOR sql_stmt USING my_job;
   LOOP
      FETCH emp_cv INTO emp_rec;
      EXIT WHEN emp_cv%NOTFOUND;
      -- process record
   END LOOP;
   CLOSE emp_cv;
END;

The next example illustrates the use of objects and collections. Suppose you define object type Person and VARRAY type Hobbies, as follows:

CREATE TYPE Person AS OBJECT (name VARCHAR2(25), age NUMBER);
CREATE TYPE Hobbies IS VARRAY(10) OF VARCHAR2(25);

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 || 
         ' (p Person, h 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 p, h 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;

From an anonymous PL/SQL block, you might call the procedures in package teams, as follows:

DECLARE
   team_name VARCHAR2(15);
   ...
BEGIN
   ...
   team_name := 'Notables';
   teams.create_table(team_name);
   teams.insert_row(team_name, Person('John', 31), 
      Hobbies('skiing', 'coin collecting', 'tennis'));
   teams.insert_row(team_name, Person('Mary', 28), 
      Hobbies('golf', 'quilting', 'rock climbing'));
   teams.print_table(team_name);
END;

Specifying Parameter Modes

You need not specify a parameter mode for input bind arguments (those used, for example, in the WHERE clause) because the mode defaults to IN. However, you must specify the OUT mode for output bind arguments used in the RETURNING clause of an INSERT, UPDATE, or DELETE statement. An example follows:

DECLARE
   sql_stmt VARCHAR2(100);
   old_loc  VARCHAR2(15);
BEGIN
   sql_stmt := 
      'DELETE FROM dept WHERE deptno = 20 RETURNING loc INTO :x';
   EXECUTE IMMEDIATE sql_stmt USING OUT old_loc;
   ...
END;

Likewise, 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 stand-alone procedure:

CREATE PROCEDURE create_dept (
   deptno IN OUT NUMBER,
   dname  IN VARCHAR2,
   loc    IN VARCHAR2) AS
BEGIN
   deptno := deptno_seq.NEXTVAL;
   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(200);
   new_deptno  NUMBER(2);
   new_dname   VARCHAR2(15) := 'ADVERTISING';
   new_loc     VARCHAR2(15) := '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;

Tips and Traps

This section shows you how to take full advantage of dynamic SQL and how to avoid some common pitfalls.

Passing the Names of Schema Objects

Suppose you need a procedure that accepts the name of any database table, then drops that table from your schema. Using dynamic SQL, you might write the following stand-alone procedure:

CREATE PROCEDURE drop_table (table_name IN VARCHAR2) AS
BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE :tab' USING table_name;
END;

However, at run time, this procedure fails with an invalid table name error. That is because you cannot use bind arguments to pass the names of schema objects to a dynamic SQL statement. Instead, you must embed parameters in the dynamic string, then pass the names of schema objects to those parameters.

To debug the last example, you must revise the EXECUTE IMMEDIATE statement. Instead of using a placeholder and bind argument, you use the concatenation operator to embed parameter table_name in the dynamic string, as follows:

CREATE PROCEDURE drop_table (table_name IN VARCHAR2) AS
BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE ' || table_name;
END;

Now, you can pass the name of any database table to the dynamic SQL statement.

Using Duplicate Placeholders

Placeholders in a dynamic SQL statement are associated with bind arguments in the USING clause by position, not by name. So, if the same placeholder appears two or more times in the SQL statement, each appearance must correspond to a bind argument in the USING clause. For example, given the dynamic string

sql_stmt := 'INSERT INTO payroll VALUES (:x, :x, :y, :x)';

you might code the corresponding USING clause as follows:

EXECUTE IMMEDIATE sql_stmt USING a, a, b, a;

However, only the unique placeholders in a dynamic PL/SQL block are associated with bind arguments in the USING clause by position. So, if the same placeholder appears two or more times in a PL/SQL block, all appearances correspond to one bind argument in the USING clause. In the example below, the first unique placeholder (x) is associated with the first bind argument (a). Likewise, the second unique placeholder (y) is associated with the second bind argument (b).

DECLARE
   a NUMBER := 4;
   b NUMBER := 7;
BEGIN
   plsql_block := 'BEGIN calc_stats(:x, :x, :y, :x); END;'
   EXECUTE IMMEDIATE plsql_block USING a, b;
   ...
END;

Using Cursor Attributes

Every cursor has four attributes: %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT. When appended to the cursor name, they return useful information about the execution of static and dynamic SQL statements.

To process SQL data manipulation statements, Oracle opens an implicit cursor named SQL. Its attributes return information about the most recently executed INSERT, UPDATE, DELETE, or single-row SELECT statement. For example, the following stand-alone function uses %ROWCOUNT to return the number of rows deleted from a database table:

CREATE FUNCTION rows_deleted (
   table_name IN VARCHAR2, 
   condition IN VARCHAR2) RETURN INTEGER AS
BEGIN
   EXECUTE IMMEDIATE 
      'DELETE FROM ' || table_name || ' WHERE ' || condition;
   RETURN SQL%ROWCOUNT;  -- return number of rows deleted
END;

Likewise, when appended to a cursor variable name, the cursor attributes return information about the execution of a multi-row query. For more information about cursor attributes, see "Using Cursor Attributes".

Passing Nulls

Suppose you want to pass nulls to a dynamic SQL statement. For example, you might write the following EXECUTE IMMEDIATE statement:

EXECUTE IMMEDIATE 'UPDATE emp SET comm = :x' USING NULL;

However, this statement fails with a bad expression error because the literal NULL is not allowed in the USING clause. To work around this restriction, simply replace the keyword NULL with an uninitialized variable, as follows:

DECLARE
   a_null CHAR(1); -- set to NULL automatically at run time
BEGIN
   EXECUTE IMMEDIATE 'UPDATE emp SET comm = :x' USING a_null;
END;

Doing Remote Operations

As the following example shows, PL/SQL subprograms can execute dynamic SQL statements that refer to objects on a remote database:

PROCEDURE delete_dept (db_link VARCHAR2, dept_num INTEGER) IS
BEGIN
   EXECUTE IMMEDIATE 'DELETE FROM dept@' || db_link ||
      ' WHERE deptno = :n' USING dept_num;
END;

Also, the targets of remote procedure calls (RPCs) can contain dynamic SQL statements. For example, suppose the following stand-alone function, which returns the number of rows in a table, resides on the Chicago database:

CREATE FUNCTION row_count (tab_name CHAR) RETURN INT AS
   rows INT;
BEGIN
   EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || tab_name INTO rows;
   RETURN rows;
END;

From an anonymous block, you might call the function remotely, as follows:

DECLARE
   rows INTEGER;
BEGIN
   rows := row_count@chicago('emp');

Using Invoker Rights

By default, a stored procedure executes with the privileges of its definer, not its invoker. Such procedures are bound to the schema in which they reside. For example, assume that the following stand-alone procedure, which can drop any kind of database object, resides in schema scott:

CREATE PROCEDURE drop_it (kind IN VARCHAR2, name IN VARCHAR2) AS
BEGIN
   EXECUTE IMMEDIATE 'DROP ' || kind || ' ' || name;
END;

Also assume that user jones has been granted the EXECUTE privilege on this procedure. When user jones calls drop_it, as follows, the dynamic DROP statement executes with the privileges of user scott:

SQL> CALL drop_it('TABLE', 'dept');

Furthermore, the unqualified reference to table dept is resolved in schema scott. So, the procedure drops the table from schema scott, not from schema jones.

However, the AUTHID clause enables a stored procedure to execute with the privileges of its invoker (current user). Such procedures are not bound to a particular schema. For example, the following version of drop_it executes with the privileges of its invoker:

CREATE PROCEDURE drop_it (kind IN VARCHAR2, name IN VARCHAR2)
   AUTHID CURRENT_USER AS
BEGIN
   EXECUTE IMMEDIATE 'DROP ' || kind || ' ' || name;
END;

Also, the unqualified reference to the database object is resolved in the schema of the invoker. For details, see "Invoker Rights versus Definer Rights".

Using Pragma RESTRICT_REFERENCES

A function called from SQL statements must obey certain rules meant to control side effects. (See "Controlling Sides Effects".) To check for violations of the rules, you can use the pragma RESTRICT_REFERENCES, which instructs the compiler to report reads and/or writes to database tables and/or package variables. (See Oracle8i Application Developer's Guide - Fundamentals.) However, if the function body contains a dynamic INSERT, UPDATE, or DELETE statement, the function always violates the rules "write no database state" and "read no database state."

Avoiding Deadlocks

In a few situations, executing a SQL data definition statement results in a deadlock. For example, the procedure below causes a deadlock because it attempts to drop itself. To avoid deadlocks, never try to ALTER or DROP a subprogram or package while you are still using it.

CREATE PROCEDURE calc_bonus (emp_id NUMBER) AS
BEGIN
   ...
   EXECUTE IMMEDIATE 'DROP PROCEDURE calc_bonus';
   -- causes "timeout occurred while waiting to lock object" error
END;

10 Native Dynamic SQL

Major Topics

What Is Dynamic SQL?

The Need for Dynamic SQL

Using the EXECUTE IMMEDIATE Statement

Some Examples

Using the OPEN-FOR, FETCH, and CLOSE Statements

Opening the Cursor Variable

Fetching from the Cursor Variable

Closing the Cursor Variable

Some Examples

Specifying Parameter Modes

Tips and Traps

Passing the Names of Schema Objects

Using Duplicate Placeholders

Using Cursor Attributes

Passing Nulls

Doing Remote Operations

Using Invoker Rights

Using Pragma RESTRICT_REFERENCES

Avoiding Deadlocks

10
Native Dynamic SQL