7 Performing SQL Operations with Native Dynamic SQL

Specifying Parameter Modes for Bind Variables in Dynamic SQL Strings

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 (
   deptid IN OUT NUMBER,
   dname  IN VARCHAR2,
   mgrid  IN NUMBER,
   locid  IN NUMBER) AS
BEGIN
   SELECT departments_seq.NEXTVAL INTO deptid FROM dual;
   INSERT INTO departments VALUES (deptid, dname, mgrid, locid);
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 deptid, as shown in Example 7-3.

DECLARE
   plsql_block VARCHAR2(500);
   new_deptid  NUMBER(4);
   new_dname   VARCHAR2(30) := 'Advertising';
   new_mgrid   NUMBER(6) := 200;
   new_locid   NUMBER(4) := 1700;
BEGIN
   plsql_block := 'BEGIN create_dept(:a, :b, :c, :d); END;';
   EXECUTE IMMEDIATE plsql_block
      USING IN OUT new_deptid, new_dname, new_mgrid, new_locid;
END;
/

Using Dynamic SQL with Bulk SQL

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

EXECUTE IMMEDIATE

You can use the BULK COLLECT INTO clause with the EXECUTE IMMEDIATE statement to store values from each column of a query's result set in a separate collection.

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.

FETCH

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.

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.

Examples of Dynamic Bulk Binds

This sections contains examples of dynamic bulk binds.You can bind define variables in a dynamic query using the BULK COLLECT INTO clause. As shown in Example 7-4, 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(25);
   emp_cv EmpCurTyp;
   empids NumList;
   enames NameList;
   sals   NumList;
BEGIN
   OPEN emp_cv FOR 'SELECT employee_id, last_name FROM employees';
   FETCH emp_cv BULK COLLECT INTO empids, enames;
   CLOSE emp_cv;
   EXECUTE IMMEDIATE 'SELECT salary FROM employees'
      BULK COLLECT INTO sals;
END;
/

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, as shown in Example 7-5.

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

To bind the input variables in a SQL statement, you can use the FORALL statement and USING clause, as shown in Example 7-6. 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;
/

Building a Dynamic Query with Dynamic SQL

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 (REF CURSORs)".

When to Use or Omit the Semicolon with Dynamic SQL

When building up a single SQL statement in a string, do not include any semicolon at the end.

When building up a PL/SQL anonymous block, include the semicolon at the end of each PL/SQL statement and at the end of the anonymous block. For example:

BEGIN
   EXECUTE IMMEDIATE 'BEGIN DBMS_OUTPUT.PUT_LINE(''semicolons''); END;';
END;
/

Improving Performance of Dynamic SQL with Bind Variables

When you code INSERT, UPDATE, DELETE, and SELECT statements directly in PL/SQL, PL/SQL turns the variables into bind variables automatically, to make the statements work efficiently with SQL. When you build up such statements in dynamic SQL, you need to specify the bind variables yourself to get the same performance.

In the following example, Oracle opens a different cursor for each distinct value of emp_id. This can lead to resource contention and poor performance as each statement is parsed and cached.

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

You can improve performance by using a bind variable, which 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 employees WHERE employee_id = :id' USING emp_id;
END;
/

Passing Schema Object Names As Parameters

Suppose you need a procedure that accepts the name of any database table, then drops that table from your schema. You must build a string with a statement that includes the object names, then use EXECUTE IMMEDIATE to execute the statement:

CREATE TABLE employees_temp AS SELECT last_name FROM employees;
CREATE PROCEDURE drop_table (table_name IN VARCHAR2) AS
BEGIN
  EXECUTE IMMEDIATE 'DROP TABLE ' || table_name;
END;
/

Use concatenation to build the string, rather than trying to pass the table name as a bind variable through the USING clause.

In addition, if you need to call a procedure whose name is unknown until runtime, you can pass a parameter identifying the procedure. For example, the following procedure can call another procedure (drop_table) by specifying the procedure name when executed.

CREATE PROCEDURE run_proc (proc_name IN VARCHAR2, table_name IN VARCHAR2) ASBEGIN
   EXECUTE IMMEDIATE 'CALL "' || proc_name || '" ( :proc_name )' using table_name;
END;
/

If you want to drop a table with the drop_table procedure, you can run the procedure as follows. Note that the procedure name is capitalized.

CREATE TABLE employees_temp AS SELECT last_name FROM employees;
BEGIN 
  run_proc('DROP_TABLE', 'employees_temp'); 
END;
/

Using Duplicate Placeholders with Dynamic SQL

Placeholders in a dynamic SQL statement are associated with bind arguments in the USING clause by position, not by name. If you specify a sequence of placeholders like :a, :a, :b, :b, you must include four items in the USING clause. For example, given the dynamic string

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

the fact that the name X is repeated is not significant. You can code the corresponding USING clause with four different bind variables:

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

If the dynamic statement represents a PL/SQL block, the rules for duplicate placeholders are different. Each unique placeholder maps to a single item in the USING clause. If the same placeholder appears two or more times, all references to that name correspond to one bind argument in the USING clause. In Example 7-7, all references to the placeholder x are associated with the first bind argument a, and the second unique placeholder y is associated with the second bind argument b.

CREATE PROCEDURE calc_stats(w NUMBER, x NUMBER, y NUMBER, z NUMBER) IS
BEGIN
  DBMS_OUTPUT.PUT_LINE(w + x + y + z);
END;
/
DECLARE
   a NUMBER := 4;
   b NUMBER := 7;
   plsql_block VARCHAR2(100);
BEGIN
   plsql_block := 'BEGIN calc_stats(:x, :x, :y, :x); END;';
   EXECUTE IMMEDIATE plsql_block USING a, b;
END;
/

Using Cursor Attributes with Dynamic SQL

The SQL cursor attributes %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT work when you issue an INSERT, UPDATE, DELETE, or single-row SELECT statement in dynamic SQL:

BEGIN
  EXECUTE IMMEDIATE 'DELETE FROM employees WHERE employee_id > 1000';
  DBMS_OUTPUT.PUT_LINE('Number of employees deleted: ' || TO_CHAR(SQL%ROWCOUNT));
END;
/

Likewise, when appended to a cursor variable name, the cursor attributes return information about the execution of a multi-row query:

DECLARE
  TYPE cursor_ref IS REF CURSOR;
  c1 cursor_ref;
  TYPE emp_tab IS TABLE OF employees%ROWTYPE;
  rec_tab emp_tab;
  rows_fetched NUMBER;
BEGIN
  OPEN c1 FOR 'SELECT * FROM employees';
  FETCH c1 BULK COLLECT INTO rec_tab;
  rows_fetched := c1%ROWCOUNT;
  DBMS_OUTPUT.PUT_LINE('Number of employees fetched: ' || TO_CHAR(rows_fetched));
END;
/

For more information about cursor attributes, see "Managing Cursors in PL/SQL".

Passing Nulls to Dynamic SQL

The literal NULL is not allowed in the USING clause. To work around this restriction, replace the keyword NULL with an uninitialized variable:

CREATE TABLE employees_temp AS SELECT * FROM EMPLOYEES;
DECLARE
   a_null CHAR(1); -- set to NULL automatically at run time
BEGIN
   EXECUTE IMMEDIATE 'UPDATE employees_temp SET commission_pct = :x' USING a_null;
END;
/

Using Database Links with Dynamic SQL

PL/SQL subprograms can execute dynamic SQL statements that use database links to refer to objects on remote databases:

CREATE PROCEDURE delete_dept (db_link VARCHAR2, dept_id INTEGER) IS
BEGIN
   EXECUTE IMMEDIATE 'DELETE FROM departments@' || db_link ||
      ' WHERE department_id = :num' USING dept_id;
END;
/
-- delete department id 41 in the departments table on the remote DB hr_db
CALL delete_dept('hr_db', 41); 

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 hr_db database in London:

CREATE FUNCTION row_count (tab_name VARCHAR2) RETURN NUMBER AS
   rows NUMBER;
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@hr_db('employees');
   DBMS_OUTPUT.PUT_LINE(emp_count);
END;
/

Using Invoker Rights with Dynamic SQL

Dynamic SQL lets you write schema-management procedures that can be centralized in one schema, and can be called from other schemas and operate on the objects in those schemas. For example, this procedure can drop any kind of database object:

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

Let's say that this procedure is part of the HR schema. Without the AUTHID clause, the procedure would always drop objects in the HR schema, regardless of who calls it. Even if you pass a fully qualified object name, this procedure would not have the privileges to make changes in other schemas.

The AUTHID clause lifts both of these restrictions. It lets the procedure run with the privileges of the user that invokes it, and makes unqualified references refer to objects in that user's schema.

For details, see "Using Invoker's Rights Versus Definer's Rights (AUTHID Clause)".

Using Pragma RESTRICT_REFERENCES with Dynamic SQL

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 or write database tables or package variables. (For more information, See Oracle Database Application Developer's Guide - Fundamentals.)

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). PL/SQL cannot detect those side-effects automatically, 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 with Dynamic SQL

In a few situations, executing a SQL data definition statement results in a deadlock. For example, the following procedure 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 OR REPLACE PROCEDURE calc_bonus (emp_id NUMBER) AS
BEGIN
   EXECUTE IMMEDIATE 'DROP PROCEDURE calc_bonus'; -- deadlock!
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.

Using Dynamic SQL With PL/SQL Records and Collections

You can use dynamic SQL with records and collections. As shown in Example 7-9, 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  employees%ROWTYPE;
   sql_stmt VARCHAR2(200);
   v_job   VARCHAR2(10) := 'ST_CLERK';
BEGIN
   sql_stmt := 'SELECT * FROM employees WHERE job_id = :j';
   OPEN emp_cv FOR sql_stmt USING v_job;
   LOOP
     FETCH emp_cv INTO emp_rec;
     EXIT WHEN emp_cv%NOTFOUND;
     DBMS_OUTPUT.PUT_LINE('Name: ' || emp_rec.last_name || ' Job Id: ' ||
                           emp_rec.job_id);
   END LOOP;
   CLOSE emp_cv;
END;
/

For an example of using dynamic SQL with object types, see "Using Dynamic SQL With Objects".