Oracle Built-in Packages

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.

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

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 ...