Dynamic SQL Concepts - Part II

Some programs must build and process SQL statements where some information is not known in advance. A reporting application might build different SELECT statements for the various reports it generates, substituting new table and column names and ordering or grouping by different columns. Database management applications might issue statements such as CREATE, DROP, and GRANT that cannot be coded directly in a PL/SQL program. These statements are called dynamic SQL statements.

Dynamic SQL statements built as character strings built at run time. The strings contain the text of a SQL statement or PL/SQL block. They can also contain placeholders for bind arguments. Placeholder names are prefixed by a colon, and the names themselves do 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. To process a multi-row query (SELECT statement), you use the OPEN-FOR, FETCH, and CLOSE statements.

Why Use 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). Unlike INSERT, UPDATE, and DELETE statements, these statements cannot be included directly in a PL/SQL program.

■ You want more flexibility. For example, you might want to pass the name of a schema object as a parameter to a procedure. You might want to build different search conditions for the WHERE clause of a SELECT statement.

■ You want to issue a query where you do not know the number, names, or datatypes of the columns in advance. In this case, you use the DBMS_SQL package rather than the OPEN-FOR statement.

If you have older code that uses the DBMS_SQL package, the techniques described in this chapter using EXECUTE IMMEDIATE and OPEN-FOR generally provide better performance, more readable code, and extra features such as support for objects and

collections.

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 main argument to EXECUTE IMMEDIATE is the string containing the SQL statement to execute. You can build up the string using concatenation, or use a predefined string.

Except for multi-row queries, the dynamic string can contain any SQL statement (without the final semicolon) or any PL/SQL block (with the final semicolon). The string can also contain placeholders, arbitrary names preceded by a colon, for bind arguments. In this case, you specify which PL/SQL variables correspond to the placeholders with the INTO, USING, and RETURNING INTO clauses.

You can only use placeholders in places where you can substitute variables in the SQL statement, such as conditional tests in WHERE clauses. You cannot use placeholders for

the names of 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. 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. 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. 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. For example, define variables and bind arguments cannot be Booleans or associative arrays. 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.

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

Example 2 Dynamic SQL Procedure that Accepts Table Name and WHERE Clause
In the example below, a standalone procedure accepts the name of a database table and an optional WHERE-clause condition. 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 OR REPLACE 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;
END;

/

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

/

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.

Examples of Dynamic SQL for Records, Objects, and Collections

Example 3 Dynamic SQL Fetching into a Record
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;

/

Example 4 Dynamic SQL for Object Types and Collections
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);

Using dynamic SQL, you can write a package that uses these types:
CREATE OR REPLACE 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 OR REPLACE 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 block, you might call the procedures in package TEAMS:
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
Bulk SQL passes entire collections back and forth, not just individual elements. This technique improves performance by minimizing the number of context switches between the PL/SQL and SQL engines. You can use a single statement instead of a
loop that issues a SQL statement in every iteration.

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

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

Example 5 Dynamic SQL with BULK COLLECT INTO Clause
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;

/

Example 6 Dynamic SQL with RETURNING BULK COLLECT INTO Clause

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:

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

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