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|
|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.
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; /
DOWNLOAD Oracle Database 10g
Photography by Ricardo Gomez, Unsplash