12.1 How do I… Execute dynamic SQL with DBMS_SQL?

Problem

I want to run a DDL statement in my PL/SQL block but I get a compilation error. Sometimes the SQL statement to be executed needs to be constructed at runtime by using the results of another query. I also want to invoke a PL/SQL procedure dynamically as the name of the procedure is returned by a query at runtime. How do I execute dynamic SQL statements in PL/SQL? Technique

Dynamic SQL is supported by the DBMS_SQL package, which you can use to write stored procedures or functions and anonymous PL/SQL blocks that generate and execute dynamic SQL statements. Dynamic SQL statements are not embedded in your source program but are stored in strings that are built at runtime. Both data manipulation language (DML) and data definition language (DDL) statements can be executed dynamically using the DBMS_SQL package.

Essentially, SQL and PL/SQL can be generated on-the-fly and then executed. With this powerful technique, redundant code can be generated by PL/SQL code itself. Using dynamic SQL, for instance, enables you to create a procedure that manipulates a table whose name is not known until runtime. The DBMS_SQL package also allows a SQL statement to be created, parsed, and executed dynamically at runtime. Table 12.1 shows the functions and procedures in the DBMS_SQL package.

PARSE Procedure Parses the statement for syntax.
Name Type Description
BIND_VARIABLE Procedure Binds a value to an input variable.
DEFINE_COLUMN Procedure Binds a variable to a cursor column.
EXECUTE Function Executes the cursor.
EXECUTE_AND_FETCH Function Executes the cursor and fetch rows.
FETCH_ROWS Function Fetches the rows from the cursor.
VARIABLE_VALUE Procedure Retrieves the value of a output bind variable.
COLUMN_VALUE Procedure Retrieves a column value from the cursor into an output variable
CLOSE_CURSOR Procedure Closes the cursor after processing.

When executing query (SELECT) statements with the DBMS_SQL package, the first step is to create a cursor with the OPEN_CURSOR function. The PARSE procedure is then used to parse the SQL statement and prepares it for execution. Use the BIND_VARIABLE procedure to bind values to any input variables. Column values from the cursor can be returned to the local PL/SQL module by binding local variables to columns, using the DEFINE_COLUMN procedure. The EXECUTE function executes the cursor once it has been parsed. Use the FETCH_ROWS function to fetch records, and the COLUMN_VALUE procedure to retrieve the column values to local variables. After the processing is done, the cursor is closed using the CLOSE_CURSOR procedure. The EXECUTE and FETCH_ROWS calls can be combined into a single call by using the EXECUTE_AND_FETCH function.

When executing non-query DDL and DML statements or PL/SQL blocks using the DBMS_SQL package, the steps are simpler than the above steps. First of all, create a cursor with the OPEN_CURSOR function and bind any input variables, using the BIND_VARIABLE procedure. The query-related calls to DEFINE_COLUMN, FETCH_ROWS, and COLUMN_VALUE are eliminated. After executing the cursor using the EXECUTE procedure, retrieve the values any output bind variables with the VARIABLE_VALUE procedure, only if the statement being executed is an anonymous PL/SQL block. Finally, close the cursor using the CLOSE_CURSOR procedure.

Steps

1. Run SQL*Plus and connect as the WAITE user account. Run the CHP12_1.SQL file to create the sample table and data used in this How-To.

SQL> START CHP12_1

SQL> SET ECHO ON

SQL> CREATE TABLE DEPT12 (

2 DEPT_NO NUMBER(5),

3 DEPT_NAME VARCHAR2(30));

Table created.

SQL>

SQL> INSERT INTO DEPT12

2 VALUES (1, ‘Marketing’);

1 row created.

2. Run the CHP12_2.SQL file in SQL*Plus, which contains PL/SQL code that uses the DBMS_SQL package to execute an INSERT statement using dynamic SQL. The technique to associate placeholders (input bind variables) with actual PL/SQL variables, to construct general purpose DML statements is established through this exercise. The results of the operation are shown in Figure 12.1 .

Line 2 declares a variable containing a handle for the cursor when it is created. Line 7 executes the OPEN_CURSOR function in the DBMS_SQL package to create a cursor and return its handle into a PL/SQL variable. The INSERT statement to be executed is parsed for syntax errors using the PARSE procedure in lines 8 and 9. The PARSE procedure is passed the cursor handle, the SQL statement, and the DBMS_SQL.V7 constant. These lines could be replaced by any valid SQL statement to be executed dynamically.

The bind variables in the INSERT statement are associated with actual PL/SQL variables in lines 10 and 11. Line 12 uses the EXECUTE function in the DBMS_SQL package to execute the SQL statement, which processes and returns the number of rows if it is an INSERT, UPDATE, or DELETE statement. Line 13 closes the cursor. The number of rows processed by the EXECUTE function is displayed in SQL*Plus using the DBMS_OUTPUT.PUT_LINE procedure.

Lines 15 through 18 handle any exceptions occurring with a WHEN OTHERS exception handler. If an exception occurred in a function or procedure of the DBMS_SQL package, it must be handled by an exception handler in the local PL/SQL block. The cursor is closed in the event of an exception to free system resources taken up by the cursor. Line 18 prints the SQLCODE and SQLERRM to help debug the cause of the error.

3. Load the CHP12_3.SQL file in SQL*Plus, as shown in Figure 12.2. The file contains an anonymous PL/SQL block that performs a dynamic query and returns the column values to local variables.

In line 8, the SELECT statement to query the DEPT12 table is stored in a string variable. The variables used to retrieve the columns are declared in lines 4 and 5. Line 9 opens a cursor for the process, using the OPEN_CURSOR function. Line 10 parses the query statement using the PARSE procedure. Lines 11 and 12 use the DEFINE_COLUMN procedure to identify the variables that will hold column values returned by the query. Line 13 executes the statement and builds the cursor. The LOOP statement from lines 14 through 22 processes the rows of the cursor by executing the FETCH_ROWS function shown in line 15. If the function returns 0, there are no more records to retrieve and the loop is exited. The COLUMN_VALUE procedures in lines 18 and 19 are used to move column values returned by the cursor to local variables.

How It Works

The DBMS_SQL package is used to execute dynamic SQL statements and return the results to the local PL/SQL code. The OPEN_CURSOR function is used to open a cursor and return its handle, which is used in all subsequent calls. The cursor handle and SQL statement (or an anonymous PL/SQL block) are passed to the PARSE procedure to catch any syntactical errors. The EXECUTE function is then used to execute the statement after it has been parsed. The CLOSE_CURSOR procedure is used to close the cursor after processing is completed.

For queries, to return values from the cursor back to the PL/SQL code, the DEFINE_COLUMN procedure identifies which local variable receives which column. The FETCH_ROWS function actually returns the record. The COLUMN_VALUE procedure is used to move column values from the cursor to variables, for each variable that was defined using the DEFINE_COLUMN procedure. The COLUMN_VALUE procedure should be called only after a successful call to FETCH_ROWS or EXECUTE_AND_FETCH. Only if you are executing an anonymous PL/SQL block, use the VARIABLE_VALUE procedure to retrieve the value of each output variable.

Step 2 executes a DML statement that does not return any values to the local PL/SQL code. Instead, it accepts input variables that are defined using the BIND_VARIABLE procedure. Step 2 also presents exception handling routines in a PL/SQL module using dynamic SQL. When dynamic SQL is used, the local PL/SQL module is expected to handle any exceptions that occur. Steps 3 and 4 present a dynamic SELECT statement. The DEFINE_COLUMN procedure binds columns returned by the query to local variables. The FETCH_ROWS procedure retrieves the records and the COLUMN_VALUE procedure moves the column values to local variables. In Step 5, an anonymous PL/SQL block is executed dynamically. Both input and output bind variables are specified using the BIND_VARIABLE procedure. The value of the output variable is retrieved using the VARIABLE_VALUE procedure.

Additional functions supported by the DBMS_SQL package for error reporting and DBMS_SQL cursor management are listed in Table 12.2 along with a description and where they should be called.

Table 12.2 Additional functions in the DBMS_SQL package

Name Type Description
LAST_ERROR_POSITION Function Returns byte offset in the SQL statement where the error occurred. Called after PARSE.
LAST_ROW_COUNT Function Returns number of rows fetched from a cursor. Called after FETCH_ROWS or EXECUTE_AND_FETCH.
LAST_ROW_ID Function Returns ROWID of the last record fetched. Called after FETCH_ROWS or EXECUTE_AND_FETCH.
LAST_SQL_FUNCTION_CODE Function Returns function code for the SQL statement being executed.
IS_OPEN Function Returns TRUE if the cursor is open, FALSE otherwise.

When using variables of the CHAR, RAW, or ROWID datatypes, and to issue a call to the BIND_VARIABLE, DEFINE_COLUMN, COLUMN_VALUE or VARIABLE_VALUE procedure, you would use the name of the procedure suffixed with the datatype. For example, use the BIND_VARIABLE_ROWID procedure to bind a variable of the ROWID datatype. As a special case for variables of the LONG datatype, the DEFINE_COLUMN_LONG and COLUMN_VALUE_LONG procedures must be used. To use PL/SQL arrays as input/output variables, use the BIND_ARRAY and DEFINE_ARRAY procedures.

Comments

Ever since the release of PL/SQL 2.1, DBMS_SQL package implements dynamic SQL and PL/SQL, which can be called from other PL/SQL blocks. It is perhaps one of the most valuable advancements in Oracle for the application developer. Anonymous PL/SQL blocks, queries, DML, and DDL statements can be handled using the DBMS_SQL package. The ability to dynamically create SQL statements and PL/SQL blocks, as well as parse and execute them at runtime, adds a new dimension to application development.

The DBMS_SQL package helps you to create general purpose procedures. Dynamic SQL is a powerful feature but should be used with care and only when such a need occurs in an application. You might want to issue a UPDATE TABLE statement, for example, from within a stored procedure. OCI and Oracle precompilers are other tools that can perform dynamic SQL and PL/SQL, and are even more powerful than DBMS_SQL.

The privileges are that the DBMS_SQL package is owned by SYS, but operations are performed with the privileges of the caller. Thus, a user executing SQL or PL/SQL through DBMS_SQL should have direct privileges to the objects in question. If the caller is an anonymous PL/SQL block, executing a subprogram (procedure or function) dynamically, the subprogram is run using the privileges of the current user account. But if the caller is a stored subprogram, the called subprogram is run using the privileges of the owner of the calling stored subprogram. -