Oracle Built−in Packages- P29: 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 Notice that there is no exposure here of the fact that arrays are being used. FOR i IN 1 . LOOP v_ename i v_empno i END LOOP END LOOP dynamic TO_CHAR - timing END And here are the astounding results from execution of this test script SQL @ 10 static 114 dynamic 119 SQL @ 100 static 1141 dynamic 1167 In other words there was virtually no difference in performance between these two approaches. This closeness exceeded my expectations and is more than one might expect simply from the array processing. My hat is off to the PL SQL development team They really have reduced the overhead of executing dynamic SQL and anonymous blocks. You will find the full implementation of the empfetch body in the file later I will show the different elements of the package and offer some observations. First the following data structures are declared at the package level 1. A cursor for the dynamic SQL. I open the cursor when the package is initialized and keep that cursor allocated for the duration of the session minimizing memory requirements and maximizing performance. c PLS_INTEGER 2. Two arrays to hold the employee IDs and the names. Notice that I must use the special table types provided by DBMS_SQL. empno_array ename_array 3. A global variable keeping track of the number of rows fetched. I could retrieve this value with the operator but this would be misleading if you were appending rows and it would also entail more overhead than simply storing the value in this variable. g_num_fetched PLS_INTEGER 0 Now we will look at the programs that make use of these data structures. First the rows procedure which populates the arrays PROCEDURE rows numrows_in IN INTEGER where_clause_in IN VARCHAR2 NULL append_rows_in IN BOOLEAN FALSE IS .
