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