Database, SQL and PL/SQL

On Confusion and Recompilation

 

Apply best practices to program naming, recompiling invalid programs, and collecting.

By Steven Feuerstein Oracle ACE Director

May/June 2006

 

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:

  • Change the name of one of the two programs. From a best-practices perspective, this would be my first recommendation. If, for example, you have naming conventions for packages and schema-level program units, you can likely avoid this kind of name collision. I will assume, however, that you are not able to change the name, so you can choose one of the remaining solutions.

  • Prefix the call to the schema-level function with the name of the schema. This will distinguish it from the call to the packaged program. One downside to this approach is that you will have to hard-code the schema name in your code. This is generally something developers want to avoid, because schema names can change, depending on how and where the application is installed.

  • Use dynamic SQL to call the schema-level function. The dynamic block executes outside the scope of the package, so an unqualified reference to the function will be directed to the schema-level function. With this approach, you avoid hard-coding the schema name. You will not, however, be able to bind variables of arguments with non-SQL datatypes, such as Boolean or a record type, using EXECUTE IMMEDIATE.

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:

  • If you need a sparsely indexed list (for "data-smart" storage, for example), your only practical option is an associative array. True, you could allocate and then delete elements of a nested table variable, but it is inefficient to do so for anything but the smallest collections.

  • If your PL/SQL application requires negative subscripts, you have to use associative arrays.

  • If you are running Oracle Database 10g and would find it useful to perform high-level set operations on your collections, choose nested tables over associative arrays.

  • If you want to enforce a limit on the number of rows stored in a collection, use VARRAYs.

  • If you intend to store large amounts of persistent data in a column collection, your only option is a nested table. Oracle Database will then use a separate table behind the scenes to hold the collection data, so you can allow for almost limitless growth.

  • If you want to preserve the order of elements stored in the collection column and if your data set will be small, use a VARRAY. What is "small"? I tend to think in terms of how much data you can fit into a single database block; if you span blocks, you get row chaining, which decreases performance. The database block size is established at database creation time and is typically 2K, 4K, or 8K.

  • Here are some other indications that a VARRAY would be appropriate: You don't want to worry about deletions occurring in the middle of the data set; your data has an intrinsic upper bound; or you expect, in general, to retrieve the entire collection simultaneously.

The information in Table 1 will also help you make your choice.

Table 1: Associative array, nested table, or VARRAY

Characteristic Associative Array Nested Table VARRAY
Dimensionality Single Single Single
Use inside the FROM clause of query with TABLE operator No Yes Yes
Usable as column datatype in a table No Yes; data stored "out of line" (in separate table) Yes; data stored "in line" (in same table)
Uninitialized state Empty (cannot be null); elements undefined Atomically null; illegal to reference elements Atomically null; illegal to reference elements
Initialization Automatic, when declared Via constructor, fetch, assignment Via constructor, fetch, assignment
In PL/SQL, elements referenced via BINARY_INTEGER (-2,147,483,647 .. 2,147, 483,647) VARCHAR2 (Oracle9i Database Release 2 and above) Positive integer between 1 and 2,147,483,647 Positive integer between 1 and 2,147,483,647
Sparse Yes Initially, no; after deletions, yes No
Bounded No Can be extended Yes
Can assign value to any EXTEND element at any time Yes No; may need to use EXTEND first No; may need to use past upper bound first and cannot use EXTEND
Means of extending Assign value to element with a new subscript Use built-in EXTEND procedure (or TRIM to condense), with no predefined maximum Use EXTEND (or TRIM), but only up to declared maximum size
Can be compared for equality No Yes, in Oracle Database 10g No
Can be manipulated with set operators No Yes, in Oracle Database 10g No
Retains ordering and subscripts when stored in and retrieved from database N/A No Yes

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.
1 - At least one of the objects to be recompiled is not of a supported object type.
2 - At least one of the objects to be recompiled depends on an invalid object outside the scope of the current recompile request.
4 - At least one of the objects to be recompiled was compiled with errors and is INVALID.

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).
2. With versions of Oracle Database prior to Oracle Database 10g, I will use Yakobson's utility. I will avoid DBMS_UTILITY.COMPILE_SCHEMA, because with versions of Oracle Database prior to Oracle Database 10g, it will always recompile all the program units in the schema and so will be much slower.

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;
/
Next Steps

READ more about
UTL_RECOMP
 DBMS_UTILITY.COMPILE_SCHEMA

READ more
Feuerstein
 Best Practice PL/SQL

 DOWNLOAD Oracle Database 10g



 

DISCLAIMER: We've captured these popular historical magazine articles for your reference. Links etc contained within these article possibly won't work. These articles are provided as-is with no guarantee that the information within them is the best advice for current versions of the Oracle Database.