DEVELOPER: PL/SQL Practices
On Confusion and Recompilation
By Steven Feuerstein
Apply best practices to program naming, recompiling invalid programs, and collecting.
I have a package named pck_events_1 that has a function named fn_1. I have another schema-level ("standalone") function, also named fn_1, defined in the same schema. How can I call the schema-level function from a subprogram inside the package?
That's an awkward situation, isn't it?
You have three possible solutions:
These last two options are demonstrated in the name_confusion.sql script shown in Listing 1.
Code Listing 1: name_confusion.sql script
-- Saved in name_confusion.sql -- Must run in SCOTT schema SET SERVEROUTPUT ON CREATE OR REPLACE PROCEDURE this_one (n in number) IS BEGIN DBMS_OUTPUT.put_line ( 'schema-level' ); END this_one; / CREATE OR REPLACE PACKAGE same_name IS PROCEDURE this_one (n in number); PROCEDURE that_one; END same_name; / CREATE OR REPLACE PACKAGE BODY same_name IS PROCEDURE this_one (n in number) IS BEGIN DBMS_OUTPUT.put_line ( 'package-level' ); END this_one; PROCEDURE that_one IS BEGIN this_one (10); scott.this_one (10); EXECUTE IMMEDIATE 'BEGIN same_name.this_one (:val); END;' USING 10; EXECUTE IMMEDIATE 'BEGIN this_one (:val); END;' USING 10; END that_one; END same_name; / BEGIN same_name.that_one; END; /
And now I run the script in Listing 1 in SQL*Plus and see the following results:
SQL> @name_confusion Procedure created. Package created. Package body created. package-level schema-level package-level schema-level
In this example, both of the this_one programs (schema-level and packaged) have the same parameter list, so the code compiles regardless of which program is being invoked. Suppose the parameter lists are different, however, as shown in Listing 2. Here my schema-level procedure takes a single Boolean argument. In this case, as you can see in the output in Listing 2, my package will not even compile unless I qualify the program name with its schema.
Code Listing 2: name_confusion2.sql
-- Saved in name_confusion2.sql SET SERVEROUTPUT ON CREATE OR REPLACE PROCEDURE this_one (b IN BOOLEAN) IS BEGIN DBMS_OUTPUT.put_line ( 'schema-level' ); END this_one; / CREATE OR REPLACE PACKAGE same_name IS PROCEDURE this_one (n in number); PROCEDURE that_one; END same_name; / CREATE OR REPLACE PACKAGE BODY same_name IS PROCEDURE this_one (n in number) IS BEGIN DBMS_OUTPUT.put_line ( 'package-level' ); END this_one; PROCEDURE that_one IS BEGIN this_one ('155'); this_one (TRUE); END that_one; END same_name; / SQL> @name_confusion2 Procedure created. Package created. Warning: Package Body created with compilation errors. SQL> show errors Errors for PACKAGE BODY SAME_NAME: LINE/COL ERROR ---------- ------------------------------------------------------------------ 13/7 PL/SQL: Statement ignored 13/7 PLS-00306: wrong number or types of arguments in call to 'THIS_ONE'
I end with this example to drive home the point that the two this_one procedures are not in any way overloaded. They are defined in different scopes, so simply having different parameter lists will not be enough to tell the PL/SQL compiler which program to use.
Which Collection Type Should I Use?
I need to pass a collection from one PL/SQL program to another. Which collection type should I use, or does it make any difference?
Oracle PL/SQL offers three types of collections (arraylike structures): associative array, nested table, and VARRAY. Which collection type makes sense for your application? In some cases, the choice is obvious. In others, there may be several acceptable choices. I offer some guidance and a table that describes many of the differences between associative arrays, nested tables, and VARRAYs.
As a PL/SQL developer, I find myself leaning toward using associative arrays as a first instinct. There are several reasons for this preference: At one time (Oracle Database 7.3), associative arrays—then called PL/SQL tables—were the only type of collection in PL/SQL. So I got used to using them. They also involve the least amount of coding: You don't have to initialize or extend them. In Oracle9i Database Release 2 and later, you can index associative arrays by strings as well as by integers. However, if you want to store your collection within a database table, you cannot use an associative array. The question then becomes: nested table or VARRAY?
The following guidelines will help you choose an associative array, nested table, or VARRAY:
The information in Table 1 will also help you make your choice.
Table 1: Associative array, nested table, or VARRAY
For more-detailed explanations of these topics and collections in general, check out chapter 13 of Oracle PL/SQL Programming , 4th Edition (www.oreilly.com/catalog/oraclep4) and the Oracle documentation ( Oracle Database PL/SQL User's Guide and Reference ) at download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/collections.htm.
Recompiling Invalid Program Units
How can I recompile all invalid program units in my schema?
Oracle offers two built-in packaged utilities to recompile invalid program units. In addition, Solomon Yakobson, an outstanding Oracle technologist, wrote his own recompile utility, which he has made generally available for our use. Finally, many PL/SQL editors offer their own features to accomplish this.
I review briefly the two Oracle options and the Yakobson utility below and then show usage of each in a performance comparison script.
UTL_RECOMP. New to Oracle Database 10g Release 2 and a very powerful utility designed specifically for recompilation, UTL_RECOMP offers "a packaged interface to recompile invalid PL/SQL modules, Java classes, index types, and operators in a database sequentially or in parallel" (from the description found in the utlrcmp.sql script that creates the UTL_RECOMP package and several associated tables and views). Only a SYSDBA account has the authority to run the UTL_RECOMP subprograms (RECOMP_SERIAL and RECOMP_PARALLEL), unless a DBA grants EXECUTE on this package to your schema or to PUBLIC.
For more information on this package, visit the PL/SQL Packages and Types Reference , at download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/u_recomp.htm#ARPLS380.
DBMS_UTILITY.COMPILE_SCHEMA. This utility has been around since Oracle Database Version 7. You can run it from any schema, and it will recompile either all program units (the only option available before Oracle Database 10g) or simply all invalid program units (available via a new argument in Oracle Database 10g's version of COMPILE_SCHEMA).
For more information on this subprogram, visit the PL/SQL Packages and Types Reference , at download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_util.htm#sthref9332
Recompile script from Solomon Yakobson. You can run this handy utility (available via the Downloads link on the Best Practice PL/SQL page) from any schema, and it will recompile all invalid programs according to the dependency order, so that when it is done, all programs without compile errors will have been recompiled and the status will have been set to VALID. This function returns the following values:
0 - Success. All requested objects are recompiled and VALID.
So, as is typical in the world of Oracle and PL/SQL, you have alternatives. Which should you use? To answer this question I put together a script—recompile_comparison.sql, shown in Listing 3, —to check the performance of these three approaches. As you review these numbers, keep in mind that the absolute values are not important; rather, the difference between the values is key.
Code Listing 3: recompile_comparison.sql
-- Saved in recompile_comparison.sql SET SERVEROUTPUT ON FORMAT WRAPPED SPOOL recompile_comparison.log DECLARE -- What program do you want to recompile, -- to force invalidation of other objects? g_program VARCHAR2 ( 100 ) := 'package qu_all_objects'; -- g_start_time PLS_INTEGER; -- l_dummy PLS_INTEGER; PROCEDURE show_invalid ( context_in IN VARCHAR2 ) IS l_invalid PLS_INTEGER; BEGIN SELECT COUNT ( * ) INTO l_invalid FROM user_objects WHERE status = 'INVALID'; DBMS_OUTPUT.put_line ( 'Invalid object count ' || context_in || ': ' || l_invalid ); END show_invalid; PROCEDURE before_recompile IS BEGIN EXECUTE IMMEDIATE 'alter ' || g_program || ' compile reuse settings'; show_invalid ( 'before' ); -- Change get_cpu_time to get_time for versions earlier than 10g g_start_time := DBMS_UTILITY.get_cpu_time; END before_recompile; PROCEDURE after_recompile ( approach_in IN VARCHAR2 ) IS BEGIN -- Change get_cpu_time to get_time for versions earlier than 10g DBMS_OUTPUT.put_line ( 'Time for "' || approach_in || '" = ' || TO_CHAR ( DBMS_UTILITY.get_cpu_time - g_start_time ) ); show_invalid ( 'after' ); END after_recompile; BEGIN before_recompile; l_dummy := recompile ( o_owner => USER, display => FALSE ); after_recompile ( 'Yakobson utility' ); -- before_recompile; DBMS_UTILITY.compile_schema ( USER -- Comment out following line for versions earlier than 10g , compile_all => FALSE, reuse_settings => TRUE ); after_recompile ( 'dbms_utility.compile_schema' ); -- before_recompile; SYS.UTL_RECOMP.recomp_serial ( USER ); after_recompile ( 'utl_recomp.serial' ); END; / SPOOL OFF
I ran the recompile_comparison script on Oracle Database 10g Release 2 and got these results:
SQL> @recompile_comparison Time for "Yakobson utility" = 6003 Time for "dbms_utility.compile_schema" = 5900 Time for "utl_recomp.recomp_serial" = 5936
I ran a modified version of recompile_comparison on Oracle9i Database Release 2 (adjusted to use DBMS_UTILITY.GET_TIME instead of GET_CPU_TIME, and no calls to UTL_RECOMP, because it does not exist in Oracle9i Database) and got these results:
Time for "Yakobson utility" = 7244 Time for "dbms_utility.compile_schema" = 22309
The call to DBMS_UTILITY.COMPILE_SCHEMA on Oracle9i Database took much longer, because that utility recompiles all program units in the schema, not just those that are invalid. The Oracle Database 10g version of this program allows you to specify that you want to compile only invalid objects.
From these results, I conclude the following:
1. In Oracle Database 10g, I will use DBMS_UTILITY.COMPILE_SCHEMA, which will run as efficiently as or better than the other options (when compiling only invalid objects) and can be run from any schema (no special privileges required).
Obtaining the Names of Columns in a Dynamic Select
I store SELECT statements in a relational table and then retrieve and execute them dynamically in PL/SQL, as specified by the user. I need to obtain the names of the columns in my queries, to use in the display of the headers. How can I get this information?
Oracle provides a wonderfully useful utility in the DBMS_SQL package—the DESCRIBE_COLUMNS procedure—to solve this problem.
You pass DBMS_SQL.DESCRIBE_COLUMNS an already parsed cursor, and it returns a collection of records, each element of which gives you information about a column or an expression in the SELECT list of the query.
DBMS_SQL.DESCRIBE_COLUMNS allows you to describe the columns of your dynamic cursor, returning information about each column in an associative array of records. This capability offers you the possibility of writing very generic cursor-processing code; this procedure will come in particularly handy when you are writing Method 4 dynamic SQL and you are not certain how many columns are being selected. It also allows you to easily obtain the names of each of those columns.
To use this procedure, you need to have declared a PL/SQL collection based on the DBMS_SQL.DESC_TAB collection type (or DESC_TAB2, if your query might return column names that are longer than 30 characters). You can then traverse the table and extract the needed information about the cursor. The anonymous block in Listing 4, available with the online version of this column, shows the basic steps you will perform when working with the DBMS_SQL.DESCRIBE_COLUMNS built-in. (To simplify the code, I assume that the datatypes of all columns are VARCHAR2 or implicitly convertible to VARCHAR2.)
Code Listing 4: Anonymous block using DBMS_SQL.DESCRIBE_COLUMNS
DECLARE -- This query might be read from a table... l_query VARCHAR2 ( 4000 ) := 'SELECT last_name, salary FROM employees'; l_cursor PLS_INTEGER := DBMS_SQL.open_cursor; l_columns DBMS_SQL.desc_tab2; l_numcols PLS_INTEGER; l_value VARCHAR2 ( 4000 ); l_feedback PLS_INTEGER; BEGIN -- Parse the query. DBMS_SQL.parse ( l_cursor, l_query, DBMS_SQL.native ); -- Retrieve column information DBMS_SQL.describe_columns2 ( l_cursor, l_numcols, l_columns ); -- Define each of the column names (and display column names) FOR colind IN 1 .. l_numcols LOOP -- Specify maximum size of the string being retrieved. DBMS_SQL.define_column ( l_cursor, colind, l_value, 4000 ); DBMS_OUTPUT.put_line ( l_columns ( colind ).col_name ); END LOOP; -- Now execute the query.... l_feedback := DBMS_SQL.EXECUTE ( l_cursor ); LOOP EXIT WHEN DBMS_SQL.fetch_rows ( l_cursor ) = 0; FOR colind IN 1 .. l_numcols LOOP -- Retrieve each value and display it. DBMS_SQL.COLUMN_VALUE ( l_cursor, colind, l_value ); DBMS_OUTPUT.put_line ( l_columns ( colind ).col_name || ' = ' || l_value ); END LOOP; END LOOP; -- Cleanup DBMS_SQL.close_cursor ( l_cursor ); END; /
Steven Feuerstein (firstname.lastname@example.org) is considered one of the world's leading experts on the Oracle PL/SQL language, having written 10 books on the subject, including Oracle PL/SQL Programming and Oracle PL/SQL Best Practices (O'Reilly Media). Feuerstein serves as a senior technology advisor for Quest Software, and his projects include Qnxo, the world's first active mentoring product (www.qnxo.com).