Copyright (c) 2000 O'Reilly & Associates. All rights reserved.
[Appendix A] What's on the Companion Disk?
2.2.5 DBMS_SQL Exceptions 51
Chapter 2
Executing Dynamic SQL
and PL/SQL
2.3 The DBMS_SQL Interface
DBMS_SQL is an extremely powerful package, but it is also one of the most complicated built−in packages
to use. Sure, you can construct and execute any SQL statement you desire. The trade−off for that flexibility is
that you have to do lots more work to get your SQL−related job done. You must specify all aspects of the
SQL statement, usually with a wide variety of procedure calls, from the SQL statement itself down to the
values of bind variables and the datatypes of columns in SELECT statements. Before I explore each of the
programs that implement these steps, let's review the general flow of events that must occur in order to use
DBMS_SQL successfully.
2.3.1 Processing Flow of Dynamic SQL
In order to execute dynamic SQL with DBMS_SQL you must follow these steps; see Figure 2.1 for a
graphical summary:
Figure 2.1: DBMS_SQL execution flow
52
1.
Open a cursor. When you open a cursor, you ask the RDBMS to set aside and maintain a valid cursor
structure for your use with future DBMS_SQL calls. The RDBMS returns an INTEGER handle to this
cursor. You will use this handle in all future calls to DBMS_SQL programs for this dynamic SQL
statement. Note that this cursor is completely distinct from normal, native PL/SQL cursors.
2.
Parse the SQL statement. Before you can specify bind variable values and column structures for the
SQL statement, it must be parsed by the RDBMS. This parse phase verifies that the SQL statement is
properly constructed. It then associates the SQL statement with your cursor handle. Note that when
you parse a DDL statement, it is also executed immediately. Upon successful completion of the DDL
parse, the RDBMS also issues an implicit commit. This behavior is consistent with that of SQL*Plus.
3.

Bind all host variables. If the SQL statement contains references to host PL/SQL variables, you will
include placeholders to those variables in the SQL statement by prefacing their names with a colon, as
in :salary. You must then bind the actual value for that variable into the SQL statement.
4.
[Appendix A] What's on the Companion Disk?
53
Define the columns in SELECT statements. Each column in the list of the SELECT must be defined.
This define phase sets up a correspondence between the expressions in the list of the SQL statement
and the local PL/SQL variables receiving the values when a row is fetched (see COLUMN_VALUE).
This step is only necessary for SELECT statements and is roughly equivalent to the INTO clause of
an implicit SELECT statement in PL/SQL.
5.
Execute the SQL statement. Execute the specified cursor −− that is, its associated SQL statement. If
the SQL statement is an INSERT, UPDATE, or DELETE, the EXECUTE command returns the
numbers of rows processed. Otherwise, you should ignore that return value.
6.
Fetch rows from the dynamic SQL query. If you execute a SQL statement, you must then fetch the
rows from the cursor, as you would with a normal PL/SQL cursor. When you fetch, however, you do
not fetch directly into local PL/SQL variables.
7.
Retrieve values from the execution of the dynamic SQL. If the SQL statement is a query, retrieve
values from the SELECT expression list using COLUMN_VALUE. If you have passed a PL/SQL
block containing calls to stored procedures, use VARIABLE_VALUE to retrieve the values returned
by those procedures.
8.
Close the cursor. As with normal PL/SQL cursors, always clean up by closing the cursor when you
are done. This releases the memory associated with the cursor.
2.3.2 Opening the Cursor
Before you perform any kind of dynamic SQL, you must obtain a pointer to memory in which the dynamic
SQL will be managed. You do this by "opening the cursor," at which point Oracle sets aside memory for a

cursor data area and then returns a pointer to that area.
These pointers are different from the cursors defined by other elements of Oracle, such as the Oracle Call
Interface (OCI) and precompiler interfaces and even PL/SQL's static cursors.
2.3.2.1 The DBMS_SQL.OPEN_CURSOR function
Use this function to open a cursor. Here's the specification:
FUNCTION DBMS_SQL.OPEN_CURSOR RETURN INTEGER;
Notice that you do not provide a name for the cursor. You are simply requesting space in shared memory for
the SQL statement and the data affected by that statement.
You can use a cursor to execute the same or different SQL statements more than once. When you reuse a
cursor, the contents of the cursor data area are reset if a new SQL statement is parsed. You do not have to
close and reopen a cursor before you reuse it. You absolutely do not have to open a new cursor for each new
dynamic SQL statement you want to process. When you are done with the cursor, you should remove it from
memory with a call to the CLOSE_CURSOR procedure.
The following example demonstrates the use of a single cursor for two different SQL statements. I declare a
cursor, use it to create an index, and then use it to update rows in the emp table.
CREATE OR REPLACE PROCEDURE do_two_unrelated_actions
(tab_in IN VARCHAR2, col_in IN VARCHAR2, val_in IN NUMBER)
[Appendix A] What's on the Companion Disk?
2.3.2 Opening the Cursor 54
IS
cur BINARY_INTEGER := DBMS_SQL.OPEN_CURSOR;
fdbk BINARY_INTEGER;
BEGIN
/* Piece together a CREATE INDEX statement. */
DBMS_SQL.PARSE (cur,
'CREATE INDEX ind_' || tab_in || '$' || col_in || ' ON ' ||
tab_in || '(' || col_in || ')',
DBMS_SQL.NATIVE);
fdbk := DBMS_SQL.EXECUTE (cur);
/* Use the same cursor to do the update. */

DBMS_SQL.PARSE (cur,
'UPDATE ' || tab_in || ' SET ' || col_in || ' = :newval',
DBMS_SQL.NATIVE);
DBMS_SQL.BIND_VARIABLE (cur, 'newval', val_in);
fdbk := DBMS_SQL.EXECUTE (cur);
/* Free up the memory from the cursor. */
DBMS_SQL.CLOSE_CURSOR (cur);
END;
/
2.3.2.2 The DBMS_SQL.IS_OPEN function
The IS_OPEN function returns TRUE if the specified cursor is already open, and FALSE if the cursor has
been closed or if the value does not point to a dynamic cursor,
FUNCTION DBMS_SQL.IS_OPEN (c IN INTEGER) RETURN BOOLEAN;
where c is the pointer to the cursor. This function corresponds to the %ISOPEN attribute for regular PL/SQL
cursors.
2.3.3 Parsing the SQL Statement
Once you have allocated a pointer to a cursor, you can then associate that pointer with a SQL statement. You
do this by parsing the SQL statement with a call to the PARSE procedure. The parse phase checks the
statement's syntax, so if there is a syntax error, the call to PARSE will fail and an exception will be raised.
2.3.3.1 The DBMS_SQL.PARSE procedure
The PARSE procedure immediately parses the statement specified. It comes in two formats. The first, as
follows, will be used in almost every case. For very large SQL statments, use the PL/SQL table−based version
described in the next section.
PROCEDURE DBMS_SQL.PARSE
(c IN INTEGER,
statement IN VARCHAR2,
language_flag IN INTEGER);
The parameters for this procedure are summarized in the following table.
Parameter Description
c The pointer to the cursor or memory area for this SQL statement.

statement The SQL statement to be parsed and associated with the cursor. This statement should not be
terminated with a semicolon unless it is a PL/SQL block.
language_flag A flag determing how Oracle will handle the statement. Valid options are DBMS_SQL.V6,
DBMS_SQL.V7, and DBMS_SQL.NATIVE. Use DBMS_SQL.NATIVE unless otherwise
[Appendix A] What's on the Companion Disk?
2.3.2 Opening the Cursor 55