|PL/SQL User's Guide and Reference
10g Release 1 (10.1)
Part Number B10807-01
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 programs more flexible, by building and processing SQL statements at run time.
With dynamic SQL, you can directly execute any kind of SQL statement (even data definition and data control statements). You can build statements where you do not know table names,
WHERE clauses, and other information in advance.
This chapter contains these topics:
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
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
IMMEDIATE statement. To process a multi-row query (
SELECT statement), you use the
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
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
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
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. (For a comparison with DBMS_SQL, see Oracle Database Application Developer's Guide - Fundamentals.)
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
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. For the right way, see "Passing Schema Object Names As Parameters".
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
Used only for DML statements that have a
RETURNING clause (without a
COLLECT clause), the
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
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
INTO clause without specifying the parameter mode. If you use both the
USING clause and the
INTO clause, the
USING clause can contain only
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
INTO clause. You can use numeric, character, and string literals as bind arguments, but you cannot use Boolean literals (
NULL). To pass nulls to the dynamic string, you must use a workaround. See "Passing Nulls to Dynamic SQL".
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
You can execute a dynamic SQL statement repeatedly using new values for the bind arguments. However, you incur some overhead because
IMMEDIATE re-prepares the dynamic string before every execution.
Example 7-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 7-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; /
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
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; /
You use three statements to process a dynamic multi-row query:
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)".)
Example 7-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 7-4 Dynamic SQL for Object Types and Collections
The next example illustrates the use of objects and collections. Suppose you define object 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
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; /
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:
The static versions of these statements, clauses, and cursor attribute are discussed in "Reducing Loop Overhead for DML Statements and Queries (FORALL, BULK COLLECT)". Refer to that section for background information.
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
NUMBER. Three statements support dynamic bulk binds:
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
DELETE statement in a set of collections.
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.
You can put an
EXECUTE IMMEDIATE statement with the
COLLECT INTO inside a
FORALL statement. You can store the results of all the
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.
Example 7-5 Dynamic SQL with BULK COLLECT INTO Clause
You can bind define variables in a dynamic query using the
INTO clause. As the following example shows, you can use that clause in a bulk
FETCH or bulk
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 7-6 Dynamic SQL with RETURNING BULK COLLECT INTO Clause
DELETE statements can have output bind variables. You bulk-bind them with the
BULK COLLECT INTO clause of
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-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; /
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.
BEGIN EXECUTE IMMEDIATE 'dbms_output.put_line(''No semicolon'')'; EXECUTE IMMEDIATE 'BEGIN dbms_output.put_line(''semicolons''); END;'; END;
When you code
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 example below, 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 emp WHERE empno = ' || 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
CREATE PROCEDURE fire_employee (emp_id NUMBER) AS BEGIN EXECUTE IMMEDIATE 'DELETE FROM emp WHERE empno = :num' USING emp_id; END; /
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 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
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 the following example, 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.
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; /
The SQL cursor attributes
%ROWCOUNT work when you issue an
DELETE, or single-row
SELECT statement in dynamic SQL:
EXECUTE IMMEDIATE 'DELETE FROM employees WHERE employee_id > 1000'; rows_deleted := SQL%ROWCOUNT;
Likewise, when appended to a cursor variable name, the cursor attributes return information about the execution of a multi-row query:
OPEN c1 FOR 'SELECT * FROM employees'; FETCH c1 BULK COLLECT INTO rec_tab; rows_fetched := c1%ROWCOUNT;
For more information about cursor attributes, see "Using Cursor Expressions".
DECLARE a_null CHAR(1); -- set to NULL automatically at run time BEGIN EXECUTE IMMEDIATE 'UPDATE emp SET comm = :x' USING a_null; END; /
PL/SQL subprograms can execute dynamic SQL statements that use database links to refer to objects on remote databases:
PROCEDURE delete_dept (db_link VARCHAR2, dept_id INTEGER) IS BEGIN EXECUTE IMMEDIATE 'DELETE FROM departments@' || db_link || ' WHERE deptno = :num' USING dept_id; END; /
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('employees'); END; /
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.
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)".
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
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
IMMEDIATE statement, only the
INTO clause can be checked at compile time for violations of
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
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; /
When a dynamic
DELETE statement has a
RETURNING clause, output bind arguments can go in the
INTO clause or the
USING clause. In new applications, use the
INTO clause. In old applications, you can continue to use the