A DBMS_SQL Recap

The builtin DBMS_SQL package allows you to dynamically construct and execute SQL and PL/SQL statements. You get full programmatic control -- and with it comes full responsibility. With DBMS_SQL, nothing is taken for granted. You must specify each and every operation on 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 data types of columns in SELECT statements.

To execute dynamic SQL (and PL/SQL) with the DBMS_SQL you must follow this general flow:

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 modules for this dynamic SQL statement. Note that this cursor is completely distinct from static PL/SQL cursors (whether implicit or explicit).

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.

Bind all host variables. If the SQL statement contains references to host PL/SQL variables, you 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.

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 local PL/SQL variables that receive the values when a row is fetched (see COLUMN_VALUE). This step is necessary only for SELECT statements and is roughly equivalent to the INTO clause of an implicit SELECT statement in PL/SQL.

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.

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 PL/SQL cursor. When you fetch, however, you do not fetch directly into local PL/SQL variables.

Retrieve values from the execution of the dynamic SQL. If the SQL statement is a query, you will retrieve values from the SELECT expression list using COLUMN_VALUE. If you have executed a PL/SQL block, you will use VARIABLE_VALUE to retrieve any bind variables included in the PL/SQL code.

Close the cursor. Just as with normal PL/SQL cursors, you should always clean up by closing the cursor when you are done. This releases the memory associated with the cursor.