|Oracle9i Application Developer's Guide - Fundamentals
Release 2 (9.2)
Part Number A96590-01
Dynamic SQL is a programming technique that enables you to build SQL statements dynamically at runtime. You can create more general purpose, flexible applications by using dynamic SQL because the full text of a SQL statement may be unknown at compilation. For example, dynamic SQL lets you create a procedure that operates on a table whose name is not known until runtime.
Oracle includes two ways to implement dynamic SQL in a PL/SQL application:
This chapter covers the following topics:
You can find details about the
DBMS_SQL package in the Oracle9i Supplied PL/SQL Packages and Types Reference.
Dynamic SQL enables you to write programs that reference SQL statements whose full text is not known until runtime. Before discussing dynamic SQL in detail, a clear definition of static SQL may provide a good starting point for understanding dynamic SQL. Static SQL statements do not change from execution to execution. The full text of static SQL statements are known at compilation, which provides the following benefits:
Because of these advantages, you should use dynamic SQL only if you cannot use static SQL to accomplish your goals, or if using static SQL is cumbersome compared to dynamic SQL. However, static SQL has limitations that can be overcome with dynamic SQL. You may not always know the full text of the SQL statements that must be executed in a PL/SQL procedure. Your program may accept user input that defines the SQL statements to execute, or your program may need to complete some processing work to determine the correct course of action. In such cases, you should use dynamic SQL.
For example, a reporting application in a data warehouse environment might not know the exact table name until runtime. These tables might be named according to the starting month and year of the quarter, for example
INV_01_1998, and so on. You can use dynamic SQL in your reporting application to specify the table name at runtime.
You might also want to run a complex query with a user-selectable sort order. Instead of coding the query twice, with different
ORDER BY clauses, you can construct the query dynamically to include a specified
ORDER BY clause.
Dynamic SQL programs can handle changes in data definitions, without the need to recompile. This makes dynamic SQL much more flexible than static SQL. Dynamic SQL lets you write reusable code because the SQL can be easily adapted for different environments..
Dynamic SQL also lets you execute data definition language (DDL) statements and other SQL statements that are not supported in purely static SQL programs.
You should use dynamic SQL in cases where static SQL does not support the operation you want to perform, or in cases where you do not know the exact SQL statements that must be executed by a PL/SQL procedure. These SQL statements may depend on user input, or they may depend on processing work done by the program. The following sections describe typical situations where you should use dynamic SQL and typical problems that can be solved by using dynamic SQL
In PL/SQL, you can only execute the following types of statements using dynamic SQL, rather than static SQL:
Oracle9i SQL Reference for information about DDL and SCL statements.
Also, you can only use the
TABLE clause in the
SELECT statement through dynamic SQL. For example, the following PL/SQL block contains a
SELECT statement that uses the
TABLE clause and native dynamic SQL:
CREATE TYPE t_emp AS OBJECT (id NUMBER, name VARCHAR2(20)) / CREATE TYPE t_emplist AS TABLE OF t_emp / CREATE TABLE dept_new (id NUMBER, emps t_emplist) NESTED TABLE emps STORE AS emp_table; INSERT INTO dept_new VALUES ( 10, t_emplist( t_emp(1, 'SCOTT'), t_emp(2, 'BRUCE'))); DECLARE deptid NUMBER; ename VARCHAR2(20); BEGIN EXECUTE IMMEDIATE 'SELECT d.id, e.name FROM dept_new d, TABLE(d.emps) e -- not allowed in static SQL -- in PL/SQL WHERE e.id = 1' INTO deptid, ename; END; /
You can use dynamic SQL to create applications that execute dynamic queries, whose full text is not known until runtime. Many types of applications need to use dynamic queries, including:
Many types of applications must interact with data that is generated periodically. For example, you might know the tables definitions at compile time, but not the names of the tables.
Dynamic SQL can solve this problem, because it lets you wait until runtime to specify the table names. For example, in the sample data warehouse application discussed in "What Is Dynamic SQL?", new tables are generated every quarter, and these tables always have the same definition. You might let a user specify the name of the table at runtime with a dynamic SQL query similar to the following:
CREATE OR REPLACE PROCEDURE query_invoice( month VARCHAR2, year VARCHAR2) IS TYPE cur_typ IS REF CURSOR; c cur_typ; query_str VARCHAR2(200); inv_num NUMBER; inv_cust VARCHAR2(20); inv_amt NUMBER; BEGIN query_str := 'SELECT num, cust, amt FROM inv_' || month ||'_'|| year || ' WHERE invnum = :id'; OPEN c FOR query_str USING inv_num; LOOP FETCH c INTO inv_num, inv_cust, inv_amt; EXIT WHEN c%NOTFOUND; -- process row here END LOOP; CLOSE c; END; /
You can use dynamic SQL to build a SQL statement in a way that optimizes the execution by concatenating the hints into a SQL statement dynamically. This lets you change the hints based on your current database statistics, without requiring recompilation.
For example, the following procedure uses a variable called
a_hint to allow users to pass a hint option to the
CREATE OR REPLACE PROCEDURE query_emp (a_hint VARCHAR2) AS TYPE cur_typ IS REF CURSOR; c cur_typ; BEGIN OPEN c FOR 'SELECT ' || a_hint || ' empno, ename, sal, job FROM emp WHERE empno = 7566'; -- process END; /
In this example, the user can pass any of the following values for
a_hint = '/*+ ALL_ROWS */'
a_hint = '/*+ FIRST_ROWS */'
a_hint = '/*+ CHOOSE */'
Oracle9i Database Performance Guide and Reference for more information about using hints.
You can use the
IMMEDIATE statement to execute anonymous PL/SQL blocks. You can add flexibility by constructing the block contents at runtime.
For example, suppose ythroughthroughou want to write an application that takes an event number and dispatches to a handler for the event. The name of the handler is in the form
EVENT_HANDLER_event_num, where event_num is the number of the event. One approach is to implement the dispatcher as a switch statement, where the code handles each event by making a static call to its appropriate handler. This code is not very extensible because the dispatcher code must be updated whenever a handler for a new event is added.
CREATE OR REPLACE PROCEDURE event_handler_1(param number) AS BEGIN -- process event RETURN; END; / CREATE OR REPLACE PROCEDURE event_handler_2(param number) AS BEGIN -- process event RETURN; END; / CREATE OR REPLACE PROCEDURE event_handler_3(param number) AS BEGIN -- process event RETURN; END; / CREATE OR REPLACE PROCEDURE event_dispatcher (event number, param number) IS BEGIN IF (event = 1) THEN EVENT_HANDLER_1(param); ELSIF (event = 2) THEN EVENT_HANDLER_2(param); ELSIF (event = 3) THEN EVENT_HANDLER_3(param); END IF; END; /
Using native dynamic SQL, you can write a smaller, more flexible event dispatcher similar to the following:
CREATE OR REPLACE PROCEDURE event_dispatcher (event NUMBER, param NUMBER) IS BEGIN EXECUTE IMMEDIATE 'BEGIN EVENT_HANDLER_' || to_char(event) || '(:1); END;' USING param; END; /
By using the invoker-rights feature with dynamic SQL, you can build applications that issue dynamic SQL statements under the privileges and schema of the invoker. These two features, invoker-rights and dynamic SQL, enable you to build reusable application subcomponents that can operate on and access the invoker's data and modules.
PL/SQL User's Guide and Reference for information about using invokers-rights and native dynamic SQL.
This scenario shows you how to perform the following operations using native dynamic SQL:
The database in this scenario is a company's human resources database (named
hr) with the following data model:
A master table named
offices contains the list of all company locations. The
offices table has the following definition:
emp_location tables contain the employee information, where location is the name of city where the office is located. For example, a table named
emp_houston contains employee information for the company's Houston office, while a table named
emp_boston contains employee information for the company's Boston office.
emp_location table has the following definition:
Column Name Null? Type EMPNO NOT_NULL NUMBER(4) ENAME NOT_NULL VARCHAR2(10) JOB NOT_NULL VARCHAR2(9) SAL NOT_NULL NUMBER(7,2) DEPTNO NOT_NULL NUMBER(2)
The following sections describe various native dynamic SQL operations that can be performed on the data in the
The following native dynamic SQL procedure gives a raise to all employees with a particular job title:
CREATE OR REPLACE PROCEDURE salary_raise (raise_percent NUMBER, job VARCHAR2) IS TYPE loc_array_type IS TABLE OF VARCHAR2(40) INDEX BY binary_integer; dml_str VARCHAR2 (200); loc_array loc_array_type; BEGIN -- bulk fetch the list of office locations SELECT location BULK COLLECT INTO loc_array FROM offices; -- for each location, give a raise to employees with the given 'job' FOR i IN loc_array.first..loc_array.last LOOP dml_str := 'UPDATE emp_' || loc_array(i) || ' SET sal = sal * (1+(:raise_percent/100))' || ' WHERE job = :job_title'; EXECUTE IMMEDIATE dml_str USING raise_percent, job; END LOOP; END; / SHOW ERRORS;
EXECUTE IMMEDIATE statement can perform DDL operations. For example, the following procedure adds an office location:
CREATE OR REPLACE PROCEDURE add_location (loc VARCHAR2) IS BEGIN -- insert new location in master table INSERT INTO offices VALUES (loc); -- create an employee information table EXECUTE IMMEDIATE 'CREATE TABLE ' || 'emp_' || loc || '( empno NUMBER(4) NOT NULL, ename VARCHAR2(10), job VARCHAR2(9), sal NUMBER(7,2), deptno NUMBER(2) )'; END; / SHOW ERRORS;
The following procedure deletes an office location:
CREATE OR REPLACE PROCEDURE drop_location (loc VARCHAR2) IS BEGIN -- delete the employee table for location 'loc' EXECUTE IMMEDIATE 'DROP TABLE ' || 'emp_' || loc; -- remove location from master table DELETE FROM offices WHERE location = loc; END; / SHOW ERRORS;
IMMEDIATE statement can perform dynamic single-row queries. You can specify bind variables in the
USING clause and fetch the resulting row into the target specified in the
INTO clause of the statement.
The following function retrieves the number of employees at a particular location performing a specified job:
CREATE OR REPLACE FUNCTION get_num_of_employees (loc VARCHAR2, job VARCHAR2) RETURN NUMBER IS query_str VARCHAR2(1000); num_of_employees NUMBER; BEGIN query_str := 'SELECT COUNT(*) FROM ' || ' emp_' || loc || ' WHERE job = :job_title'; EXECUTE IMMEDIATE query_str INTO num_of_employees USING job; RETURN num_of_employees; END; / SHOW ERRORS;
CLOSE statements can perform dynamic multiple-row queries. For example, the following procedure lists all of the employees with a particular job at a specified location:
CREATE OR REPLACE PROCEDURE list_employees(loc VARCHAR2, job VARCHAR2) IS TYPE cur_typ IS REF CURSOR; c cur_typ; query_str VARCHAR2(1000); emp_name VARCHAR2(20); emp_num NUMBER; BEGIN query_str := 'SELECT ename, empno FROM emp_' || loc || ' WHERE job = :job_title'; -- find employees who perform the specified job OPEN c FOR query_str USING job; LOOP FETCH c INTO emp_name, emp_num; EXIT WHEN c%NOTFOUND; -- process row here END LOOP; CLOSE c; END; / SHOW ERRORS;
Oracle provides two methods for using dynamic SQL within PL/SQL: native dynamic SQL and the
DBMS_SQL package. Native dynamic SQL lets you place dynamic SQL statements directly into PL/SQL code. These dynamic statements include DML statements (including queries), PL/SQL anonymous blocks, DDL statements, transaction control statements, and session control statements.
To process most native dynamic SQL statements, you use the
IMMEDIATE statement. To process a multi-row query (
SELECT statement), you use
To use native dynamic SQL, the
DBMS_SQL package is a PL/SQL library that offers an API to execute SQL statements dynamically. The
DBMS_SQL package has procedures to open a cursor, parse a cursor, supply binds, and so on. Programs that use the
DBMS_SQL package make calls to this package to perform dynamic SQL operations.
The following sections provide detailed information about the advantages of both methods.
The PL/SQL User's Guide and Reference for detailed information about using native dynamic SQL and the Oracle9i Supplied PL/SQL Packages and Types Reference for detailed information about using the
Native dynamic SQL provides the following advantages over the
Because native dynamic SQL is integrated with SQL, you can use it in the same way that you use static SQL within PL/SQL code. Native dynamic SQL code is typically more compact and readable than equivalent code that uses the
DBMS_SQL package you must call many procedures and functions in a strict sequence, making even simple operations require a lot of code. You can avoid this complexity by using native dynamic SQL instead.
Table 8-1 illustrates the difference in the amount of code required to perform the same operation using the
DBMS_SQL package and native dynamic SQL.
|DBMS_SQL Package||Native Dynamic SQL|
CREATE PROCEDURE insert_into_table ( table_name VARCHAR2, deptnumber NUMBER, deptname VARCHAR2, location VARCHAR2) IS cur_hdl INTEGER; stmt_str VARCHAR2(200); rows_processed BINARY_INTEGER; BEGIN stmt_str := 'INSERT INTO ' || table_name || ' VALUES (:deptno, :dname, :loc)'; -- open cursor cur_hdl := dbms_sql.open_cursor; -- parse cursor dbms_sql.parse(cur_hdl, stmt_str, dbms_sql.native); -- supply binds dbms_sql.bind_variable (cur_hdl, ':deptno', deptnumber); dbms_sql.bind_variable (cur_hdl, ':dname', deptname); dbms_sql.bind_variable (cur_hdl, ':loc', location); -- execute cursor rows_processed := dbms_sql.execute(cur_hdl); -- close cursor dbms_sql.close_cursor(cur_hdl); END; / SHOW ERRORS;
CREATE PROCEDURE insert_into_table ( table_name VARCHAR2, deptnumber NUMBER, deptname VARCHAR2, location VARCHAR2) IS stmt_str VARCHAR2(200); BEGIN stmt_str := 'INSERT INTO ' || table_name || ' values (:deptno, :dname, :loc)'; EXECUTE IMMEDIATE stmt_str USING deptnumber, deptname, location; END; / SHOW ERRORS;
Native dynamic SQL in PL/SQL performs comparably to the performance of static SQL, because the PL/SQL interpreter has built-in support for it. Programs that use native dynamic SQL are much faster than programs that use the
DBMS_SQL package. Typically, native dynamic SQL statements perform 1.5 to 3 times better than equivalent
DBMS_SQL calls. (Your performance gains may vary depending on your application.)
Native dynamic SQL bundles the statement preparation, binding, and execution steps into a single operation, which minimizes the data copying and procedure call overhead and improves performance.
DBMS_SQL package is based on a procedural API and incurs high procedure call and data copy overhead. Each time you bind a variable, the
DBMS_SQL package copies the PL/SQL bind variable into its space for use during execution. Each time you execute a fetch, the data is copied into the space managed by the
DBMS_SQL package and then the fetched data is copied, one column at a time, into the appropriate PL/SQL variables, resulting in substantial overhead.
When using either native dynamic SQL or the
DBMS_SQL package, you can improve performance by using bind variables, because bind variables allow Oracle to share a single cursor for multiple SQL statements.
For example, the following native dynamic SQL code does not use bind variables:
CREATE OR REPLACE PROCEDURE del_dept ( my_deptno dept.deptno%TYPE) IS BEGIN EXECUTE IMMEDIATE 'DELETE FROM dept WHERE deptno = ' || to_char (my_deptno); END; / SHOW ERRORS;
For each distinct
my_deptno variable, a new cursor is created, causing resource contention and poor performance. Instead, bind
my_deptno as a bind variable:
CREATE OR REPLACE PROCEDURE del_dept ( my_deptno dept.deptno%TYPE) IS BEGIN EXECUTE IMMEDIATE 'DELETE FROM dept WHERE deptno = :1' USING my_deptno; END; / SHOW ERRORS;
Here, the same cursor is reused for different values of the bind
my_deptno, improving performance and scalabilty.
Native dynamic SQL supports all of the types supported by static SQL in PL/SQL, including user-defined types such as user-defined objects, collections, and
DBMS_SQL package does not support these user-defined types.
Native dynamic SQL and static SQL both support fetching into records, but the
DBMS_SQL package does not. With native dynamic SQL, the rows resulting from a query can be directly fetched into PL/SQL records.
In the following example, the rows from a query are fetched into the
DECLARE TYPE EmpCurTyp IS REF CURSOR; c EmpCurTyp; emp_rec emp%ROWTYPE; stmt_str VARCHAR2(200); e_job emp.job%TYPE; BEGIN stmt_str := 'SELECT * FROM emp WHERE job = :1'; -- in a multi-row query OPEN c FOR stmt_str USING 'MANAGER'; LOOP FETCH c INTO emp_rec; EXIT WHEN c%NOTFOUND; END LOOP; CLOSE c; -- in a single-row query EXECUTE IMMEDIATE stmt_str INTO emp_rec USING 'PRESIDENT'; END; /
DBMS_SQL package provides the following advantages over native dynamic SQL:
DBMS_SQL package is supported in client-side programs, but native dynamic SQL is not. Every call to the
DBMS_SQL package from the client-side program translates to a PL/SQL remote procedure call (RPC); these calls occur when you need to bind a variable, define a variable, or execute a statement.
DESCRIBE_COLUMNS procedure in the
DBMS_SQL package can be used to describe the columns for a cursor opened and parsed through
DBMS_SQL. This feature is similar to the
DESCRIBE command in SQL*Plus. Native dynamic SQL does not have a
DBMS_SQL package supports statements with a
RETURNING clause that update or delete multiple rows. Native dynamic SQL only supports a
RETURNING clause if a single row is returned.
"Performing DML with RETURNING Clause Using Dynamic SQL: Example" for examples of
DBMS_SQL package supports SQL statements larger than 32KB; native dynamic SQL does not.
PARSE procedure in the
DBMS_SQL package parses a SQL statement once. After the initial parsing, you can use the statement multiple times with different sets of bind arguments.
Native dynamic SQL prepares a SQL statement each time the statement is used, which typically involves parsing, optimization, and plan generation. Although the extra prepare operations incur a small performance penalty, the slowdown is typically outweighed by the performance benefits of native dynamic SQL.
The following examples illustrate the differences in the code necessary to complete operations with the
DBMS_SQL package and native dynamic SQL. Specifically, the following types of examples are presented:
In general, the native dynamic SQL code is more readable and compact, which can improve developer productivity.
The following example includes a dynamic query statement with one bind variable (
:jobname) and two select columns (
This example queries for employees with the job description
SALESMAN in the
job column of the
emp table. Table 8-2 shows sample code that accomplishes this query using the
DBMS_SQL package and native dynamic SQL.
|DBMS_SQL Query Operation||Native Dynamic SQL Query Operation|
DECLARE stmt_str varchar2(200); cur_hdl int; rows_processed int; name varchar2(10); salary int; BEGIN cur_hdl := dbms_sql.open_cursor; -- open cursor stmt_str := 'SELECT ename, sal FROM emp WHERE job = :jobname'; dbms_sql.parse(cur_hdl, stmt_str, dbms_sql.native); -- supply binds (bind by name) dbms_sql.bind_variable( cur_hdl, 'jobname', 'SALESMAN'); -- describe defines dbms_sql.define_column(cur_hdl, 1, name, 200); dbms_sql.define_column(cur_hdl, 2, salary); rows_processed := dbms_sql.execute(cur_hdl); -- execute LOOP -- fetch a row IF dbms_sql.fetch_rows(cur_hdl) > 0 then -- fetch columns from the row dbms_sql.column_value(cur_hdl, 1, name); dbms_sql.column_value(cur_hdl, 2, salary); -- <process data> ELSE EXIT; END IF; END LOOP; dbms_sql.close_cursor(cur_hdl); -- close cursor END; /
DECLARE TYPE EmpCurTyp IS REF CURSOR; cur EmpCurTyp; stmt_str VARCHAR2(200); name VARCHAR2(20); salary NUMBER; BEGIN stmt_str := 'SELECT ename, sal FROM emp WHERE job = :1'; OPEN cur FOR stmt_str USING 'SALESMAN'; LOOP FETCH cur INTO name, salary; EXIT WHEN cur%NOTFOUND; -- <process data> END LOOP; CLOSE cur; END; /
The following example includes a dynamic
INSERT statement for a table with three columns:
This example inserts a new row for which the column values are in the PL/SQL variables
location. Table 8-3 shows sample code that accomplishes this DML operation using the
DBMS_SQL package and native dynamic SQL.
|DBMS_SQL DML Operation||Native Dynamic SQL DML Operation|
DECLARE stmt_str VARCHAR2(200); cur_hdl NUMBER; deptnumber NUMBER := 99; deptname VARCHAR2(20); location VARCHAR2(10); rows_processed NUMBER; BEGIN stmt_str := 'INSERT INTO dept_new VALUES (:deptno, :dname, :loc)'; cur_hdl := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE( cur_hdl, stmt_str, DBMS_SQL.NATIVE); -- supply binds DBMS_SQL.BIND_VARIABLE (cur_hdl, ':deptno', deptnumber); DBMS_SQL.BIND_VARIABLE (cur_hdl, ':dname', deptname); DBMS_SQL.BIND_VARIABLE (cur_hdl, ':loc', location); rows_processed := dbms_sql.execute(cur_hdl); -- execute DBMS_SQL.CLOSE_CURSOR(cur_hdl); -- close END; /
The following example uses a dynamic
UPDATE statement to update the location of a department, then returns the name of the department:
Table 8-4 shows sample code that accomplishes this operation using both the
DBMS_SQL package and native dynamic SQL.
|DBMS_SQL DML Returning Operation||Native Dynamic SQL DML Returning Operation|
DECLARE deptname_array dbms_sql.Varchar2_Table; cur_hdl INT; stmt_str VARCHAR2(200); location VARCHAR2(20); deptnumber NUMBER := 10; rows_processed NUMBER; BEGIN stmt_str := 'UPDATE dept_new SET loc = :newloc WHERE deptno = :deptno RETURNING dname INTO :dname'; cur_hdl := dbms_sql.open_cursor; dbms_sql.parse (cur_hdl, stmt_str, dbms_sql.native); -- supply binds dbms_sql.bind_variable (cur_hdl, ':newloc', location); dbms_sql.bind_variable (cur_hdl, ':deptno', deptnumber); dbms_sql.bind_array (cur_hdl, ':dname', deptname_array); -- execute cursor rows_processed := dbms_sql.execute(cur_hdl); -- get RETURNING column into OUT bind array dbms_sql.variable_value (cur_hdl, ':dname', deptname_array); dbms_sql.close_cursor(cur_hdl); END; /
DECLARE deptname_array dbms_sql.Varchar2_Table; stmt_str VARCHAR2(200); location VARCHAR2(20); deptnumber NUMBER := 10; deptname VARCHAR2(20); BEGIN stmt_str := 'UPDATE dept_new SET loc = :newloc WHERE deptno = :deptno RETURNING dname INTO :dname'; EXECUTE IMMEDIATE stmt_str USING location, deptnumber, OUT deptname; END; /
Although this chapter discusses PL/SQL support for dynamic SQL, you can call dynamic SQL from other languages:
If you have an application that uses OCI, Pro*C/C++, or Pro*COBOL to execute dynamic SQL, you should consider switching to native dynamic SQL inside PL/SQL stored procedures and functions. The network round-trips required to perform dynamic SQL operations from client-side applications might hurt performance. Stored procedures can reside on the server, eliminating the network overhead. You can call the PL/SQL stored procedures and stored functions from the OCI, Pro*C/C++, or Pro*COBOL application.
For information about calling Oracle stored procedures and stored functions from various languages, refer to:
Although you can enumerate each field of a PL/SQL record when inserting or updating rows in a table, the resulting code is not especially readable or maintainable. Instead, you can use PL/SQL records directly in these statements. The most convenient technique is to declare the record using a %ROWTYPE attribute, so that it has exactly the same fields as the SQL table.
DECLARE emp_rec emp%ROWTYPE; BEGIN emp_rec.eno := 1500; emp_rec.ename := 'Steven Hill'; emp_rec.sal := '40000'; -- A %ROWTYPE value can fill in all the row fields. INSERT INTO emp VALUES emp_rec; -- The fields of a %ROWTYPE can completely replace the table columns. UPDATE emp SET ROW = emp_rec WHERE eno = 100; END; /
Although this technique helps to integrate PL/SQL variables and types more closely with SQL DML statements, you cannot use PL/SQL records as bind variables in dynamic SQL statements.
PL/SQL User's Guide and Reference, for more information about PL/SQL records.