Oracle dynamic SQL.

Preamble

Dynamic SQL makes your programs more flexible by creating and processing SQL sentences at runtime.

With Dynamic SQL you can directly execute most types of SQL statements, including data definition and data management statements.

You can build queries where you don’t know table names, WHERE sentences and other information in advance.

EXECUTE IMMEDIATE statement

Oracle/PLSQL operator EXECUTE IMMEDIATE prepares (analyzes) and immediately executes a dynamic SQL query or an anonymous PL/SQL block.

The main argument of EXECUTE IMMEDIATE is the string containing the SQL query to be executed. You can create a string using concatenation or use a predefined string.

A dynamic string can contain any SQL statement (without the last semicolon) except multiline queries or any PL/SQL block (with the last semicolon).

A dynamic_string may also contain placeholders, random names preceded by a colon, for bind_argument binding arguments. In this case, you specify which PL/SQL variables correspond to the placeholders using the INTO, USING and RETURNING INTO operators.

At runtime, the binding arguments replace the corresponding placeholders in the dynamic string. Each placeholder must be associated with a binding argument in the USING and/or RETURNING INTO sentences.

Syntax of EXECUTE IMMEDIATE operator

Oracle/PLSQL syntax of EXECUTE IMMEDIATE operator for passing a value to a variable or string:

EXECUTE IMMEDIATE dynamic_string
[ INTO {[define_variable_id[, define_variable_id] ... | record_name_id}].
[USING [IN | OUT | IN OUT] bind_argument_id ]
returning_clause_id;

or the Oracle/PLSQL syntax of the EXECUTE IMMEDIATE operator to pass a value to the collection

EXECUTE IMMEDIATE dynamic_string
[[ BULK COLLECT] INTO {host_array_name_id | collection_name_id}].
[USING [IN | OUT | IN OUT] bind_argument_id]
returning_clause_id;

Parameters or arguments

• dynamic_string – A string literal, variable or expression representing a single SQL statement or PL/SQL block. It must be of type CHAR or VARCHAR2, not NCHAR or NVARCHAR2.
• BULK COLLECT – Saves results values in one or more collections for faster queries than loops with FETCH operators.
• INTO – Used for single-line queries only, this section specifies the variables or records to which the column values are extracted. For each value retrieved by the query, the INTO offer must contain the corresponding type-compatible variable or field.
• define_variable_id – Variable in which the value of the selected column is stored.
• record_name_id – Custom record or %ROWTYPE record where the selected string is saved.
• bind_argument_id – An expression whose value is passed to a dynamic SQL statement, or a variable in which a value returned by a dynamic SQL statement is saved.
• collection_name_id – Declared collection, in which values of select_item are extracted from dynamic_string. For each select_item there must be an appropriate, type-compatible collection in the list.
• host_array_name_id – An array (declared in the PL/SQL host environment and passed to PL/SQL as a binding variable) in which the select_item values are retrieved. For each select_item, there must be a matching, type-compatible array in the list. Host arrays must start with a colon.
• USING – Default is IN. Defines the list of input and/or output binding arguments.
• return_clause_id – Returns values from inserted rows, eliminating the need for SELECT rows after. You can extract the column values into variables or in a collection. You cannot use the RETURNING sentence for remote or parallel inserting. If the instruction does not affect any rows, the values of the variables in the RETURNING sentence are not defined.

Some examples of dynamic SQL

Let’s look at some examples of using Oracle/PLSQL EXECUTE IMMEDIATE operator to understand how to use EXECUTE IMMEDIATE in Oracle/PLSQL.
Description of commands in comments (-).

DECLARE 
sql_stmt VARCHAR2(200);
plsql_block VARCHAR2(500);
emp_id NUMBER(4) := 7566;
emp_id NUMBER(7,2);
dept_id NUMBER(2) := 50;
dept_name VARCHAR2(14) := 'PERSONNEL';
location VARCHAR2(13) := 'DALLAS';
emp_rec emp%ROWTYPE;
BEGIN 
--EXECUTE IMMEDIATE with SQL proposal
EXECUTE IMMEDIATE 'CREATE TABLE bonus (id NUMBER, amt NUMBER)';
--by assigning sql_stmt a string SQL sentence with fills :1, :2, :3
sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)';
--run EXECUTE IMMEDIATE with sql_stmt using binding arguments dept_id, dept_name, location
EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location;
--by assigning sql_stmt SQL sentence with a filler :id
sql_stmt := 'SELECT * FROM emp WHERE empno = :id';
--run EXECUTE IMMEDIATE with sql_stmt using the emp_id binding argument and save the result in emp_rec.
EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;
--using plsql_block to run an anonymous block with the raise_salary subroutine of the emp_pkg package with placeholders :id, :amt
plsql_block := 'BEGIN emp_pkg.raise_salary(:id, :amt); END;';
--run EXECUTE IMMEDIATE with plsql_block using binding arguments :id, :amt
EXECUTE IMMEDIATE plsql_block USING 7788, 500;
--including a sql_stmt SQL sentence with a filler :1, :2
sql_stmt := 'UPDATE emp SET sal = 2000 WHERE empno = :1 RETURNING sal INTO :2';
--run EXECUTE IMMEDIATE with sql_stmt using emp_id, salary binding arguments
EXECUTE IMMEDIATE sql_stmt USING emp_id RETURNING INTO salary;
--EXECUTE IMMEDIATE with SQL sentence with filler :num and binding argument dept_id.
EXECUTE IMMEDIATE 'DELETE FROM dept WHERE deptno = :num' USING dept_id;
--EXECUTE IMMEDIATE with SQL sentence
EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE TRUE';
END;

An example of a dynamic SQL procedure that accepts a table name and the WHERE clause

In this example, the offline procedure accepts the database table name and the optional condition of the WHERE offer. If you miss the condition, the offline procedure will remove all rows from the table. Otherwise, the procedure deletes only those rows that match the condition.

CREATE OR REPLACE PROCEDURE delete_rows (
table_name IN VARCHAR2,
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;

Specification of parameter modes for binding variables in dynamic SQL strings

With the USING sentence, the default mode is IN, so you do not need to specify a parameter mode for input binding arguments.

With the sentence RETURNING INTO the mode is OUT, so you cannot specify a parameter mode for the output binding arguments.

You must specify a parameter mode in more complex cases such as this where you call the procedure from the PL/SQL dynamic block:

Example:

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 a procedure from the PL/SQL dynamic block, you must specify the IN OUT mode for the binding argument associated with the formal deptno parameter 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 Using Dynamic SQL

To process a dynamic multiline query you use three operators: OPEN-FOR, FETCH and CLOSE.

First you open a cursor variable for a multiline query. Then you select rows from the result set one at a time.

When all rows are processed, you close (CLOSE) the cursor variable.

The following example shows how you can extract rows from the resulting set of dynamic multiline queries 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;

Examples of dynamic SQL for object types and collections

The following example illustrates the use of objects and collections. Suppose you defined the object type Person and VARRAY as 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 create a package that uses these types:

--create a package specification
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 the package body
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 ((pers Person, hobbs Hobbies); END;
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 can call procedures from the TEAMS 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;

Using Bulk (multiple) SQL in dynamic SQL

SQL Bulk binds entire collections, not just individual elements. This method improves performance by minimizing the number of context switching between PL/SQL and SQL mechanisms. You can use one statement instead of the cycle that produces the SQL statement at each iteration.

Using the following commands, sentences and cursor attribute, your applications can create lengthy SQL statements and then execute them dynamically at runtime:

• BULK FETCH sentence
• BULK EXECUTE IMMEDIATE proposal
• FORALL offer
• COLLECT INTO expression
• RETURNING INTO expression
• %BULK_ROWCOUNT cursor attribute

Using Dynamic SQL with Bulk SQL

Mass (multiple) binding allows Oracle to bind a variable in an SQL statement to a collection of values.

The collection type can be any type of Oracle/PLSQL collection (index-by table, nested table or varray).

Items in the collection must be of SQL data type such as CHAR, DATE or NUMBER.

Three operators support dynamic mass linking: EXECUTE IMMEDIATE, FETCH and FORALL.

EXECUTE IMMEDIATE

• You can use the BULK COLLECT INTO offer with the EXECUTE IMMEDIATE operator to store values from each column of the query result set in a separate collection.
• You can use the RETURNING BULK COLLECT INTO sentence with EXECUTE IMMEDIATE to store the results of INSERT, UPDATE or DELETE in a collection set.

FETCH

• You can use BULK COLLECT INTO with FETCH to store values from each cursor column in a separate collection.

FORALL

• You can combine EXECUTE IMMEDIATE instruction with RETURNING BULK COLLECT INTO instruction inside FORALL instruction. You can store the results of all INSERT, UPDATE or DELETE operators in a collection set.
• You can transfer indexed collection items to the EXECUTE IMMEDIATE instruction via the USING sentence.
• You cannot combine indexed items directly into a string argument for EXECUTE IMMEDIATE; for example, you cannot create a collection of table names and write FORALL where each iteration is applied to another table.

Examples of dynamic SQL with the sentence BULK COLLECT INTO

You can bind certain variables in a dynamic query using the BULK COLLECT INTO offer. As shown in the following example, you can use this sentence in the FETCH mass expression or the EXECUTE IMMEDIATE mass expression:

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 of dynamic SQL with the sentence RETURNING BULK COLLECT INTO

Only INSERT, UPDATE and DELETE operators can have binding output variables. You associate them in EXECUTE IMMEDIATE with the RETURNING BULK COLLECT INTO offer.
For example:

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 of dynamic SQL inside the FORALL operator

To bind input variables in an SQL statement, you can use the FORALL statement and the USING sentence as shown below. An SQL statement cannot be a query. For example:

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;

Dynamic SQL recommendations

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

When to use or skip a semicolon with Dynamic SQL

When constructing one SQL statement in a line, do not put a semicolon at the end.

When creating an anonymous PL/SQL block, add a semicolon at the end of each PL/SQL statement and at the end of an anonymous block. For example:

BEGIN
EXECUTE IMMEDIATE 'dbms_output.put_line(''No semicolon')';
EXECUTE IMMEDIATE 'BEGIN dbms_output.put_line(''No Semicolon'); END;';
END;

Performance improvement of dynamic SQL using binding variables

When the code of your INSERT, UPDATE, DELETE, and SELECT operators is directly in PL/SQL, PL/SQL turns variables into binding variables automatically so that operators work effectively with SQL. When you create such operators in dynamic SQL, you need to specify binding variables yourself to get the same performance.

In the example below, Oracle opens a separate cursor for each individual emp_id value. This can lead to resource conflict and performance degradation, as each operator is analyzed 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 binding variable that allows Oracle to reuse the same cursor for different emp_id values:

CREATE PROCEDURE fire_employee (emp_id NUMBER) AS
BEGIN
EXECUTE IMMEDIATE
'DELETE FROM emp WHERE empno = :num' USING emp_id;
END;

Passing names of scheme objects as parameters

Suppose you want a procedure that takes the name of any database table, and then removes that table from your schema. You must create a row with an operator that includes object names and then use EXECUTE IMMEDIATE to perform the operator:

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

Use concatenation to build a string, not try to pass the table name as a binding variable through the USING sentence.

Using duplicate placeholders with dynamic SQL

Fillers in a dynamic SQL statement are associated with binding arguments in a USING sentence by position, not by name. If you specify a sequence of placeholders, for example :a, :a, :b, :b, you must include four elements in the USING sentence. For example, given a dynamic string:

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

the fact that the name x repeats does not matter. You can encode the corresponding USING sentence with four different binding variables:

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

If a dynamic operator represents a PL/SQL block, rules for duplicate aggregates are different. Each unique placeholder is displayed for one element in the USING sentence. If the same filler appears two or more times, all references to this name correspond to the same binding argument in the USING sentence.

In the following example, all references to placeholder x are linked to the first binding argument a, and the second unique placeholder y is linked to the second binding argument b.
For example:

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 with Dynamic SQL

SQL attributes of the cursor %FOUND, %ISOPEN, %NOTFOUND and %ROWCOUNT work in dynamic SQL when issuing INSERT, UPDATE, DELETE or a single row SELECT sentence:

EXECUTE IMMEDIATE 'DELETE FROM employees WHERE employee_id > 1000';
rows_deleted := SQL%ROWCOUNT;

Similarly, when adding a cursor variable name, cursor attributes return information about executing a multiline query:

OPEN c1 FOR 'SELECT * FROM employees';
FETCH c1 BULK COLLECT INTO rec_tab;
rows_fetched := c1%ROWCOUNT;

Passing NULL to dynamic SQL

Literal NULL is not allowed in the USING sentence. To get around this restriction, replace the NULL keyword with an uninitialized variable:

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

Using database links with dynamic SQL

The PL/SQL subroutines can execute dynamic SQL statements that use database references to reference objects in 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;

Remote Procedure Call Objectives (RPCs) may contain dynamic SQL statements. For example, suppose that the next standalone function that returns the number of rows in a table is in a 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 can call the function remotely as shown below:

DECLARE
emp_count INTEGER;
BEGIN
emp_count := row_count@chicago('employees');
END;

Using Invoker rights with dynamic SQL

Dynamic SQL allows you to write procedures for managing schemes that can be centralized in one scheme, as well as call from other schemes and work with objects in these schemes.

For example, this procedure can remove any 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 this procedure is part of the HR scheme. Without this condition, the AUTHID procedure would always delete objects in the HR schema, regardless of who calls it. Even if you pass on the full name of the object, this procedure will not have permission to make changes to other schemes.

The AUTHID operator raises both of these limitations. It allows the procedure to run with the privileges of the user who calls it, and makes unqualified references to objects in that user’s schema.

How to avoid deadlocks using dynamic SQL

In some situations, executing an SQL data definition operator leads to a deadlock. For example, the procedure below causes a mutual lockout because it tries to remove itself. In order to avoid deadlocks, never attempt to execute ALTER or DROP commands on a subroutine or packet while you are still using it.

CREATE OR REPLACE PROCEDURE calc_bonus (emp_id NUMBER) AS
BEGIN
EXECUTE IMMEDIATE 'DROP PROCEDURE calc_bonus'; -- dead end!
END;

Oracle PL/SQL Tutorial: What is Dynamic SQL in PL/SQL

About Enteros

​Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.​