tailieunhanh - Oracle Built−in Packages- P13

Oracle Built−in Packages- P13: Ah, for the good old days of Version of PL /SQL! Life was so simple then. No stored procedures or functions and certainly no packages. You had your set of built−in functions, like SUBSTR and TO_DATE. You had the IF statement and various kinds of loops. With these tools at hand, you built your batch−processing scripts for execution in SQL*Plus, and you coded your triggers in SQL*Forms , and you went home at night content with a good day's work done. | Appendix A What s on the Companion Disk . . Oracle PL SQL Oracle PL SQL Oracle Advonced PL SQL Oracle Wet Applkctiom Oracle PL SQL Oracle PL SQL Programming I Programming I Buir-in I Programming I Pt SQL Developer s I Language I Budt-ins 0 ne Second Edition Guide to Orocle8r features Packages with Packages Inlrodoction Pocket Reference Pocket Reference Copyright c 2000 O Reilly Associates. All rights reserved. DBMS_SQL Exceptions 51 Oracle Built-in Packages SEARCH Chapter 2 Executing Dynamic SQL and PL SQL NEXT 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 for a graphical summary Figure DBMS_SQL execution flow 52 Appendix A What s on the Companion Disk 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 .