11
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.

This chapter discusses the following topics:

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

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 Oracle9i 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]...]
[{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. However, you cannot use bind arguments to pass the names of schema objects to a dynamic SQL statement. For the right way, see "Making Procedures Work on Arbitrarily Named Schema Objects".

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

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

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;

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.

Examples of Dynamic SQL for Records, Objects, and Collections

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(200);
   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 || 
         ' (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;

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;

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

The static versions of these statements, clauses, and cursor attribute are discussed in "Reducing Loop Overhead for Collections with Bulk Binds". Refer to that section for background information.

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;

Tips and Traps for Dynamic SQL

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

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;

Making Procedures Work on Arbitrarily Named 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 standalone 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 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 explicit 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 standalone 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_id INTEGER) IS
BEGIN
   EXECUTE IMMEDIATE 'DELETE FROM dept@' || db_link ||
      ' WHERE deptno = :num' USING dept_id;
END;

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

CREATE FUNCTION row_count (tab_name VARCHAR2) RETURN INTEGER AS
   rows INTEGER;
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
   emp_count INTEGER;
BEGIN
   emp_count := 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 standalone 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');

Also, 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 Side Effects of PL/SQL Subprograms".) To check for violations of the rules, you can use the pragma RESTRICT_REFERENCES. The pragma asserts that a function does not read and/or write database tables and/or package variables. (For more information, See Oracle9i 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" (WNDS) and "read no database state" (RNDS). That is because dynamic SQL statements are checked at run time, not at compile time. In an EXECUTE IMMEDIATE statement, only the INTO clause can be checked at compile time for violations of RNDS.

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';

11 Native Dynamic SQL