Oracle Built-in Packages

Oracle Built-in PackagesSearch this book
Previous: 9.2 UTL_RAW: Manipulating Raw DataChapter 10Next: 10.2 DBMS_DESCRIBE: Describing PL/SQL Program Headers
 

10. Miscellaneous Packages

Contents:
DBMS_UTILITY: Performing Miscellaneous Operations
DBMS_DESCRIBE: Describing PL/SQL Program Headers
DBMS_DDL: Compiling and Analyzing Objects
DBMS_RANDOM: Generating Random Numbers (Oracle8 Only)

You can't find a neat category for everything, can you? This chapter brings together a variety of useful packages you are sure to dip into on a regular basis:

DBMS_UTILITY

The actual "miscellaneous" package. It offers programs to free unused user memory, parse comma-delimited lists, calculate the elapsed time of PL/SQL programs, and much more. You never know what you'll find popping up next in DBMS_UTILITY!

DBMS_DESCRIBE

Contains a single procedure, DESCRIBE_PROCEDURE, which you can use to get information about the parameters of a stored program.

DBMS_DDL

Contains programs to recompile stored code, analyze objects in your schema, and modify the referenceability of object identifiers in Oracle8.

DBMS_RANDOM

New to Oracle8, supplies PL/SQL developers with a random number generator.

10.1 DBMS_UTILITY: Performing Miscellaneous Operations

The DBMS_UTILITY package is the "miscellaneous package" for PL/SQL. It contains programs that perform a wide variety of operations (listed in Table 10.1).

TIP: I recommend that whenever you install a new version of the Oracle database, you scan the contents of the dbmsutil.sql file. Check to see if Oracle has added any new programs or changed the functionality of existing programs.

10.1.1 Getting Started with DBMS_UTILITY

The DBMS_UTILITY package is created when the Oracle database is installed. The dbmsutil.sql script (found in the built-in packages source code directory, as described in Chapter 1, Introduction) contains the source code for this package's specification. This script is called by catproc.sql, which is normally run immediately after database creation. The script creates the public synonym DBMS_UTILITY for the package and grants EXECUTE privilege on the package to public. All Oracle users can reference and make use of this package.

Table 10.1 summarizes the programs available with DBMS_UTILITY.


Table 10.1: DBMS_UTILITY Programs

Name

Description

Use in SQL

ANALYZE_DATABASE

Analyzes all the tables, clusters, and indexes in a database

No

ANALYZE_PART_OBJECT

Runs the equivalent of the SQL ANALYZE TABLE or ANALYZE INDEX command for each partition of the object, using parallel job queues (PL/SQL8 only)

No

ANALYZE_SCHEMA

Analyzes all the tables, clusters, and indexes in the specified schema

No

COMMA_TO_TABLE

Parses a comma-delimited list into a PL/SQL table (PL/SQL Release 2.1 and later)

No

COMPILE_SCHEMA

Compiles all procedures, functions, and packages in the specified schema

No

DATA_BLOCK_ADDRESS_BLOCK

Gets the block number part of a data block address

Yes

DATA_BLOCK_ADDRESS_FILE

Gets the file number part of a data block address

Yes

DB_VERSION

Returns the database version and compatibility information for the current instance (PL/SQL8 only)

No

EXEC_DDL_STATEMENT

Executes the provided DDL statement (PL/SQL8 only)

No

FORMAT_CALL_STACK

Returns the current module call stack in a formatted display

No

FORMAT_ERROR_STACK

Returns the current error stack in a formatted display

No

GET_HASH_VALUE

Returns a hash value for a string; used to obtain unique (it is hoped) integer values for strings

No

GET_PARAMETER_VALUE

Retrieves information about a parameter in the database parameter file, otherwise known as the INIT.ORA file (PL/SQL8 only)

Yes

GET_TIME

Returns the elapsed time since an arbitrary time in 100ths of seconds

No

IS_PARALLEL_SERVER

Returns TRUE if the database instance was started in parallel server mode

No

MAKE_DATA_BLOCK_ADDRESS

Creates a data block address given a file number and a block number

Yes

NAME_RESOLVE

Resolves the name of an object into its component parts

No

NAME_TOKENIZE

Returns the individual components or tokens in a string

No

PORT_STRING

Returns a string describing the platform and version of the current database

Yes

TABLE_TO_COMMA

Moves the names in a PL/SQL table into a comma-delimited list

No

10.1.1.1 DBMS_UTILITY nonprogram elements

In addition to the functions and procedures defined in the package, DBMS_UTILITY also declares five PL/SQL tables that are used either as input into or output from the package's built-in modules. By the way, these tables are also used by other built-in packages, such as DBMS_DEFER. See Chapter 10 of Oracle PL/SQL Programming for more information about PL/SQL tables (also called index-by tables as of Oracle8).

DBMS_UTILITY.UNCL_ARRAY

This PL/SQL table type is used to store lists of strings in the format:

"USER"."NAME."COLUMN"@LINK

You can use the array to store any strings you want up to the length determined in the following TABLE type statement:

TYPE DBMS_UTILITY.UNCL_ARRAY IS 
   TABLE OF VARCHAR2(227) INDEX BY BINARY_INTEGER;
DBMS_UTILITY.NAME_ARRAY

This PL/SQL table type is used to store names of identifiers and is defined as follows:

TYPE DBMS_UTILITY.NAME_ARRAY IS 
TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
DBMS_UTILITY.DBLINK_ARRAY

This PL/SQL table type is used to store database links and is defined as follows:

TYPE DBMS_UTILITY.DBLINK_ARRAY IS 
TABLE OF VARCHAR2(128) INDEX BY BINARY_INTEGER;
DBMS_UTILITY.INDEX_TABLE_TYPE

This PL/SQL table type is declared within the package, but is not otherwise used. It is made available for use by other packages and programs.

TYPE DBMS_UTILITY.INDEX_TABLE_TYPE IS 
TABLE OF BINARY_INTEGER INDEX BY BINARY_INTEGER;
DBMS_UTILITY.NUMBER_ARRAY

This PL/SQL table type is declared within the package, but is not otherwise used. It is made available for use by other packages and programs.

TYPE DBMS_UTILITY.NUMBER_ARRAY IS 
TABLE OF NUMBER INDEX BY BINARY_INTEGER;

You can declare PL/SQL tables based on these TABLE type statements as shown below:

DECLARE
   short_name_list DBMS_UTILITY.NAME_ARRAY;
   long_name_list DBMS_UTILITY.INDEX_TABLE_TYPE;
BEGIN
...

Of course, if you do declare PL/SQL tables based on DBMS_UTILITY data structures, then those declarations will change with any changes in the package.

10.1.2 The DBMS_UTILITY Interface

This section describes each of the programs in the DBMS_UTILITY package; because of the miscellaneous nature of these programs, they are simply listed in alphabetical order.

10.1.2.1 The DBMS_UTILITY.ANALYZE_DATABASE procedure

This procedure analyzes all the tables, clusters, and indexes in the entire database. The header for the procedure follows:

PROCEDURE DBMS_UTILITY.ANALYZE_DATABASE
   (method IN VARCHAR2
   ,estimate_rows IN NUMBER DEFAULT NULL
   ,estimate_percent IN NUMBER DEFAULT NULL
   ,method_opt IN VARCHAR2 DEFAULT NULL);

Parameters are summarized in this table.

Parameter

Description

method

Action to be taken by the program. ESTIMATE, DELETE, and COMPUTE are accepted values and are explained later.

estimate_rows

The number of rows to be used to perform the statistics estimate. Cannot be less than 1. Used only if method is ESTIMATE.

estimate_percent

The percentage of rows to be used to perform the statistics estimate. Ignored if estimate_rows is non-NULL. Must be between 1 and 99. Used only if method is ESTIMATE.

method_opt

The method option, indicating which elements of the object will be analyzed.

Here are the valid entries for the method argument, and the resulting activity (when you pass one of these values, they must be enclosed in single quotes):

COMPUTE

Exact statistics are computed based on the entire contents of the objects. These values are then placed in the data dictionary.

ESTIMATE

Statistics are estimated. With this option, either estimate_rows or estimate_percent must be non-NULL. These values are then placed in the data dictionary.

DELETE

The statistics for this object are deleted from the data dictionary.

Here are the valid method_opt entries and the resulting impact (when you pass one of these values, they must be enclosed in single quotes):

FOR TABLE

Collects statistics for the table.

FOR ALL COLUMNS [SIZE N]

Collects column statistics for all columns and scalar attributes. The size is the maximum number of partitions in the histogram, with a default of 75 and a maximum of 254.

FOR ALL INDEXED COLUMNS [SIZE N]

Collects column statistics for all indexed columns in the table. The size is the maximum number of partitions in the histogram, with a default of 75 and a maximum of 254.

FOR ALL INDEXES

Collects statistics for all indexes associated with the table.

10.1.2.1.1 Example

Here is an example of a request to this program to analyze all columns in my database:

BEGIN
   DBMS_UTILITY.ANALYZE_DATABASE (
      'ESTIMATE',
      100,
      50,
      'FOR ALL COLUMNS SIZE 200');
END;

10.1.2.2 The DBMS_UTILITY.ANALYZE_SCHEMA procedure

This procedure analyzes all of the tables, clusters, and indexes in the specified schema. The header for the procedure follows:

PROCEDURE DBMS_UTILITY.ANALYZE_SCHEMA
   (schema IN VARCHAR2
   ,method IN VARCHAR2
   ,estimate_rows IN NUMBER DEFAULT NULL
   ,estimate_percent IN NUMBER DEFAULT NULL
   ,method_opt IN VARCHAR2 DEFAULT NULL);

Parameters are summarized in this table.

Parameters

Description

schema

The name of the schema containing the object for which you wish to compute statistics. If NULL, then the current schema is used. This argument is case-sensitive.

method

Action to be taken by the program. ESTIMATE, DELETE, and COMPUTE are accepted values (explained later).

estimate_rows

The number of rows to be used to perform the statistics estimate. Cannot be less than 1. Used only if method is ESTIMATE.

estimate_percent

The percentage of rows to be used to perform the statistics estimate. Ignored if estimate_rows is non-NULL. Must be between 1 and 99. Used only if method is ESTIMATE.

method_opt

The method option, indicating which elements of the object will be analyzed.

Here are the valid entries for the method argument, and the resulting activity (when you pass one of these values, they must be enclosed in single quotes):

COMPUTE

Exact statistics are computed based on the entire contents of the objects. These values are then placed in the data dictionary.

ESTIMATE

Statistics are estimated. With this option, either estimate_rows or estimate_percent must be non-NULL. These values are then placed in the data dictionary.

DELETE

The statistics for this object are deleted from the data dictionary.

Here are the valid method_opt entries and the resulting impact (when you pass one of these values, they must be enclosed in single quotes):

FOR TABLE

Collects statistics for the table.

FOR ALL COLUMNS [SIZE N]

Collects column statistics for all columns and scalar attributes. The size is the maximum number of partitions in the histogram, with a default of 75 and a maximum of 254.

FOR ALL INDEXED COLUMNS [SIZE N]

Collects column statistics for all indexed columns in the table. The size is the maximum number of partitions in the histogram, with a default of 75 and a maximum of 254.

FOR ALL INDEXES

Collects statistics for all indexes associated with the table.

10.1.2.2.1 Example

Here is an example of a request to this program to analyze all indexes in my current schema:

BEGIN
   DBMS_UTILITY.ANALYZE_SCHEMA (
      USER, 
      'ESTIMATE',
      100,
      50,
      'FOR ALL INDEXES');
END;
/

10.1.2.3 The DBMS_UTILITY.ANALYZE_PART_OBJECT procedure (Oracle8 Only)

This procedure analyzes the specified, partitioned object. Here's the header for the procedure:

PROCEDURE DBMS_UTILITY.ANALYZE_PART_OBJECT
   (schema IN VARCHAR2 DEFAULT NULL
   ,object_name IN VARCHAR2 DEFAULT NULL
   ,object_type IN CHAR DEFAULT 'T'
   ,command_type IN CHAR DEFAULT 'E'
   ,command_opt IN VARCHAR2 DEFAULT NULL
   ,sample_clause IN VARCHAR2 DEFAULT 'SAMPLE 5 PERCENT');

Parameters are summarized in the following table.

Parameter

Description

schema

The schema containing the specified object.

object_name

The name of the object to be analyzed. It must be partitioned.

object_type

The type of the object. Must be either T for TABLE or I for INDEX.

command_type

A code indicating the type of analysis to perform. Valid values: C for COMPUTE STATISTICS, E for ESTIMATE STATISTICS, D for DELETE STATISTICS, and V for VALIDATE STRUCTURE.

command_opt

Options for the different command types. If command type is C or E, then command_opt can be any of the following:

FOR TABLE, FOR ALL LOCAL INDEXES, FOR ALL COLUMNS, or a combination of some of the FOR options of the ANALYZE STATISTICS command. If command_type is V, then command_opt can be CASCADE if the object_type is T for TABLE.

sample_clause

Specifies the sample clause to use when command_type is E for ESTIMATE.

Running this program is equivalent to executing this SQL command,

ANALYZE TABLE|INDEX [<schema>.]<object_name>
   PARTITION <pname> [<command_type>] [<command_opt>] [<sample_clause>]

for each partition of the specified object. DBMS_UTILITY will submit a job for each partition, so that the analysis can run in parallel using job queues. It is up to the user to control the number of concurrent jobs that will be started by setting correctly the initialization parameter JOB_QUEUE_PROCESSES.

Any syntax errors encountered for the object specification will be reported in SNP trace files.

10.1.2.3.1 Example

Here is an example of a request to this program to delete the statistics associated with the columns of the emp table:

BEGIN
   DBMS_UTILITY.ANALYZE_PART_OBJECT (
      USER,
      'EMP', 
      'T',
      'DELETE STATISTICS',
      'FOR ALL COLUMNS');
END;
/

10.1.2.4 The DBMS_UTILITY.COMMA_TO_TABLE procedure

The COMMA_TO_TABLE procedure parses a comma-delimited list and places each name into a PL/SQL table. Here's the header for the procedure:

  PROCEDURE DBMS_UTILITY.COMMA_TO_TABLE
     (list IN VARCHAR2
     ,tablen OUT BINARY_INTEGER
     ,tab OUT UNCL_ARRAY);

Parameters are summarized in the following table.

Parameter

Description

list

Comma-delimited string

tablen

Number of names found in the list and placed in the PL/SQL table

tab

The PL/SQL table declared using one of the package's predeclared TABLE types

This procedure uses the NAME_TOKENIZE procedure to determine which of the string's characters are names and which are commas.

10.1.2.4.1 Example

COMMA_TO_TABLE is a handy utility if you happen to have a comma-delimited string; otherwise, it does you no good. Just think: with a tiny bit more effort, Oracle could have provided us with a much more general-purpose and useful string parsing engine.

In any case, here is a sample use of DBMS_UTILITY.COMMA_TO_TABLE. It takes two different lists of correlated information, parses them into rows in two different tables, and then uses that data in a series of UPDATE statements.

/* Filename on companion disk: upddelist.sp */*
CREATE OR REPLACE PROCEDURE upd_from_list (
   empno_list IN VARCHAR2, 
   sal_list IN VARCHAR2)
IS
   empnos DBMS_UTILITY.UNCL_ARRAY;
   sals DBMS_UTILITY.UNCL_ARRAY;
   numemps INTEGER;
BEGIN
   DBMS_UTILITY.COMMA_TO_TABLE (empno_list, numemps, empnos);
   DBMS_UTILITY.COMMA_TO_TABLE (sal_list, numemps, sals);
   FOR rownum IN 1 .. numemps
   LOOP
      UPDATE emp SET sal = TO_NUMBER (sals(rownum))
       WHERE empno = TO_NUMBER (empnos(rownum));
   END LOOP;
END;
/

NOTE: If you are running Oracle8, you could even rewrite this program to use array processing in DBMS_SQL and replace this loop with a single, dynamic UPDATE statement. See Chapter 2, Executing Dynamic SQL and PL/SQL, for more information.

10.1.2.5 The DBMS_UTILITY.COMPILE_SCHEMA procedure

This procedure compiles all procedures, functions, and packages in the specified schema. The header for the procedure is,

PROCEDURE DBMS_UTILITY.COMPILE_SCHEMA (schema VARCHAR2);

where schema is the name of the schema.

I have heard reports from developers that it sometimes seems as though they run this program and it does not do anything at all. As I write this, though, I have requested that DBMS_UTILITY recompile my PL/Vision schema, and the buzzing of the hard drive light, as well as the delay in the resurfacing of my SQL*Plus prompt, attests to the fact that it is indeed recompiling the scores of packages in this schema.

10.1.2.5.1 Example

I execute the following command in SQL*Plus to recompile all programs in my current schema. Notice that before the recompilation, I had a single invalid package. Afterwards, all objects are valid.

SQL> select object_name from user_objects where status='INVALID';
OBJECT_NAME
---------------------------------------------------------------
PLGTE

SQL> exec DBMS_UTILITY.COMPILE_SCHEMA(user)
PL/SQL procedure successfully completed.

SQL> select object_name from user_objects where status='INVALID';
no rows selected

10.1.2.6 The DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK function

This function extracts and returns the block number of a data block address. The header for this function is,

FUNCTION DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK (dba IN NUMBER)
   RETURN NUMBER;

where dba is the data block address.

10.1.2.7 The DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE function

This function extracts and returns the file number of a data block address. The header for this function is,

FUNCTION DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE (dba IN NUMBER)
RETURN NUMBER;

where dba is the data block address.

10.1.2.8 The DBMS_UTILITY.DB_VERSION procedure

This procedure (PL/SQL8 only) returns version information for the current database instance. Here's the header for this procedure:

PROCEDURE DBMS_UTILITY.DB_VERSION
    (version OUT VARCHAR2
    ,compatibility OUT VARCHAR2);

Parameters are summarized in the following table.

Parameter

Description

version

A string that represents the internal software version of the database. The length of this string is variable and is determined by the database version.

compatibility

The compatibility setting of the database determined by the INIT.ORA parameter, COMPATIBLE. If the parameter is not specified in the INIT.ORA file, NULL is returned.

10.1.2.8.1 Example

Before this function was available, you had to build a query against a V$ table in order to obtain this information. Now it is easy to obtain your database version from within PL/SQL. In fact, you can make it even easier to get this information by building a wrapper around DBMS_UTILITY.DB_VERSION, as shown here:

/* Filename on companion disk: dbver.spp */*
CREATE OR REPLACE PACKAGE db
IS
   FUNCTION version RETURN VARCHAR2;
   FUNCTION compatibility RETURN VARCHAR2;
END;
/
CREATE OR REPLACE PACKAGE BODY db
IS
   v VARCHAR2(100);
   c VARCHAR2(100);

   PROCEDURE init_info
   IS
   BEGIN
      IF v IS NULL
      THEN
         DBMS_UTILITY.DB_VERSION (v, c);
      END IF;
   END;

   FUNCTION version RETURN VARCHAR2
   IS
   BEGIN
      init_info;
      RETURN v;      
   END;

   FUNCTION compatibility RETURN VARCHAR2
   IS
   BEGIN
      init_info;
      RETURN c;      
   END;
END;
/

Notice that this very simple package also optimizes lookups against the DBMS_UTILITY package. The first time you call either the DB.VERSION or the DB.COMPATILITY functions, the private init_info procedure will detect that the v variable is NULL, and so it will call the built-in procedure. From that point on, however, whenever you call either of the DB functions, they will simply return the current value. After all, the version of the database is not going to change during your connection to that database.

Here is the output I received from db.version on Oracle8:

SQL> exec DBMS_UTILITY.PUT_LINE (db.version)
8.0.3.0.0

10.1.2.9 The DBMS_UTILITY.EXEC_DDL_STATEMENT procedure

Oracle has added a procedure to the DBMS_UTILITY package that allows you to execute a DDL statement easily. The header for this procedure follows:

PROCEDURE DBMS_UTILITY.EXEC_DDL_STATEMENT
    (parse_string IN VARCHAR2);

Here, for example, is all the code I need to write to create an index from within PL/SQL:

BEGIN
   DBMS_UTILITY.EXEC_DDL_STATEMENT
      ('create index so_easy on emp (hiredate, mgr, sal)');
END;
/

You can also use the DBMS_SQL package to perform the same functionality.

10.1.2.10 The DBMS_UTILITY.FORMAT_CALL_STACK function

This function formats and returns the current call stack. You can use this function to access the call stack in your program. The header for the function follows:

FUNCTION DBMS_UTILITY.FORMAT_CALL_STACK RETURN VARCHAR2;

10.1.2.10.1 Example

I generated the information in this next example with the following statement:

DBMS_OUTPUT.PUT_LINE (DBMS_UTILITY.FORMAT_CALL_STACK);

The code shows sample output from a call to FORMAT_CALL_STACK.

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
817efc90         3  procedure BOOK.CALC_TOTALS
817d99ec         3  function BOOK.NET_PROFIT
817d101c         4  anonymous block

The output from this function can be up to 2000 bytes in length.

10.1.2.11 The DBMS_UTILITY.FORMAT_ERROR_STACK function

The FORMAT_ERROR_STACK function formats and returns the current error stack. You might use this function in an exception handler to examine the sequence of errors raised. The header for the function follows:

FUNCTION DBMS_UTILITY.FORMAT_ERROR_STACK RETURN VARCHAR2;

The output from this function can be up to 2000 bytes in length.

10.1.2.11.1 Example

The script file errstk.sql creates three procedures, each of which raises a different exception, and then kicks off the nested execution of those programs.

/* Filename on companion disk: errstk.sql */*
CREATE OR REPLACE PROCEDURE proc1
IS
BEGIN
   RAISE NO_DATA_FOUND;
END;
/
CREATE OR REPLACE PROCEDURE proc2 S
BEGIN
   proc1;
EXCEPTION
   WHEN OTHERS THEN RAISE VALUE_ERROR;
END;
/
CREATE OR REPLACE PROCEDURE proc2 IS
BEGIN
   proc1;
EXCEPTION
   WHEN OTHERS THEN RAISE VALUE_ERROR;
END;
/
CREATE OR REPLACE PROCEDURE proc3 IS
BEGIN
   proc2;
EXCEPTION
   WHEN OTHERS THEN RAISE DUP_VAL_ON_INDEX;
END;
/
BEGIN /* Now execute the top-level procedure. */
   proc3;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE (DBMS_UTILITY.FORMAT_ERROR_STACK);
END;
/

Here is the output from this script:

ORA-00001: unique constraint (.) violated
ORA-06502: PL/SQL: numeric or value error
ORA-01403: no data found

Here is my conclusion from this test: the DBMS_UTILITY.FORMAT_ERROR_STACK is of limited use in PL/SQL programs. You don't see the name of the program in which the error was raised, and you don't see the line number on which the error occurred.

10.1.2.12 The DBMS_UTILITY.GET_HASH_VALUE function

This function gives PL/SQL developers access to a hashing algorithm. You will generally use hashing to generate a unique (or at least likely to be unique) integer value for a string. Here's the header for this function:

FUNCTION DBMS_UTILITY.GET_HASH_VALUE
    (name IN VARCHAR2
    ,base IN NUMBER
    ,hash_size IN NUMBER)
RETURN NUMBER;

Parameters are summarized in the following table.

Parameter

Description

name

The string to be converted or hashed into an integer

base

The base or starting value of integer values for the hashing algorithm

hash_size

The size of the "hash table," meaning the total number of values that are available to the hashing algorithm as conversions from the string inputs

The values of base and hash_size determine the range of integers that can be used as converted values. Clearly, the larger the hash size, the more likely it is that you will be able to obtain a unique integer for every string you pass to the program.

Here are some points to keep in mind when working with the GET_HASH_VALUE function:

  • Use a small prime number for the base parameter. This establishes the low point of the range of values for the hash table.

  • Use a very large number, and, at Oracle's suggestion, a power of 2, for the hash size to obtain best results. I usually employ a number like 230.

  • You can never be sure that the function will actually return an integer value that is unique across the different strings you are converting or have already converted. You must always check to ensure that the value is unique.

  • If you hit a conflict, you must rebuild your entire hash table (the list of integer values you have generated so far).

  • Since you want to make sure that your base and hash_size values are applied consistently to all hashings for a particular application, you should never call the GET_HASH_VALUE function directly. Instead, you should build a "wrapper" around it that presets all of the arguments except the string that you are hashing. This technique is shown in the following example.

10.1.2.12.1 Example

The following example demonstrates how to use the hash function. It also shows how to use the function to build an alternative index on a PL/SQL table. Finally, it compares the performance of hash-based lookups versus a "full table scan" of a PL/SQL table. Comments in the program should make it easy to follow the algorithm.

NOTE: The demohash procedure makes use of the p.l procedure, the PL/Vision replacement for the much more awkward and limited DBMS_OUTPUT.PUT_LINE, as well as the PLVtmr package.[1]

/* Filename on companion disk: hashdemo.sp */
CREATE OR REPLACE PROCEDURE demohash (counter IN INTEGER)
IS
   v_row PLS_INTEGER;
   v_name VARCHAR2(30);
   hashing_failure EXCEPTION;

   /* Define the PL/SQL table */
   TYPE string_tabtype IS TABLE OF VARCHAR2(60) 
      INDEX BY BINARY_INTEGER;
   names string_tabtype;

   /* A function which returns the hashed value. */
   FUNCTION hashval (value IN VARCHAR2) RETURN NUMBER
   IS
   BEGIN
       RETURN DBMS_UTILITY.GET_HASH_VALUE 
          (value, 37, 1073741824);  /* POWER (2, 30) */
   END hashval;

   /* Add a name to the table, using the hash function to 
      determine the row in which the value is placed. Ah, 
      the beauty of sparse PL/SQL tables! */
   PROCEDURE addname (nm IN VARCHAR2) IS
   BEGIN
       v_row := hashval (nm);
       names (v_row) := nm;
   END;

   /* Obtain the row for a name by scanning the table. */
   FUNCTION rowbyscan (nm IN VARCHAR2) RETURN PLS_INTEGER
   IS
      v_row PLS_INTEGER := names.FIRST;
      retval PLS_INTEGER;
   BEGIN
      LOOP
         EXIT WHEN v_row IS NULL;
         IF names(v_row) = nm
         THEN
            retval := v_row;
            EXIT;
         ELSE
            v_row := names.NEXT (v_row);
         END IF;
      END LOOP;
      RETURN retval;
   END;

   /* Obtain the row for a name by hashing the string. */
  FUNCTION rowbyhash (nm IN VARCHAR2) RETURN PLS_INTEGER
   IS
   BEGIN
      RETURN hashval (nm);
   END;
BEGIN 
   /* Load up the table with a set of strings based on the number
      of iterations requested. This allows us to easily test the
      scalability of the two algorithms. */
   FOR i IN 1 .. counter
   LOOP
       addname ('Steven' || i);
       addname ('Veva' || i);
       addname ('Eli' || i);
       addname ('Chris' || i);
   END LOOP;

   /* Verify that there were no hashing conflicts (the COUNT should
      be 4 x counter. */
   p.l ('Count in names', names.COUNT);

   IF names.COUNT != 4 * counter
   THEN
      p.l ('Hashing conflict! Test suspended...');
      RAISE hashing_failure;
   END IF;

   /* Verify that the two scans return matching values. */
   v_name := 'Eli' || TRUNC (counter/2);
   p.l ('scan',rowbyscan (v_name));
   p.l ('hash',rowbyhash (v_name));
   IF rowbyscan (v_name) != rowbyhash (v_name)
   THEN
      p.l ('Scanned row differs from hashed row. Test suspended...');
      RAISE hashing_failure;
   END IF;

   /* Time performance of retrieval via scan. */
   plvtmr.capture;
   FOR i IN 1 .. counter
   LOOP
      v_row := rowbyscan (v_name);
   END LOOP;
   plvtmr.show_elapsed ('scan');

   /* Time performance of retrieval via hashed value. */
   plvtmr.capture;
   FOR i IN 1 .. counter
   LOOP
      v_row := rowbyhash (v_name);
   END LOOP;
   plvtmr.show_elapsed ('hash');
EXCEPTION
   WHEN hashing_failure
   THEN
      NULL;
END;
/

10.1.2.13 The DBMS_UTILITY.GET_PARAMETER_VALUE function

Available first in PL/SQL8, this function allows you to retrieve the value of a database initialization parameter (set in the INIT.ORA initialization file). Here's the header:

FUNCTION DBMS_UTILITY.GET_PARAMETER_VALUE
    (parnam IN VARCHAR2
    ,intval IN OUT BINARY_INTEGER
    ,strval IN OUT VARCHAR2)
RETURN BINARY_INTEGER;

The value returned by the function is either of the following:

0

Indicating a numeric or Boolean parameter value

1

Indicating a string parameter value

Parameters are summarized in the following table.

Parameter

Description

parnam

The name of the initialization parameter (case-insensitive).

intval

The parameter value if that value is numeric. If the value is a Boolean (i.e., the value in the initialization file is TRUE or FALSE), then intval is set to 0 for FALSE and 1 for TRUE. If the value is a string, then this argument contains the length of that string value.

strval

The parameter value if that value is a string. Otherwise it is NULL.

Long desired by Oracle developers, the GET_PARAMETER_VALUE function now allows you to get critical information about the current database instance, including the default date format and lots of information about the way shared memory is configured. And you don't have to use UTL_FILE to read the initialization file. (Chances are your DBA would not enable the database directory holding this file for UTL_FILE access anyway!). Note that if you have more than one entry for the same parameter (certainly a possibility with a parameter such as UTL_FILE_DIR), then this built-in will retrieve only the value associated with the first occurrence of the parameter.

You will probably want to build a wrapper around GET_PARAMETER_VALUE to make it easier to retrieve and interpret the results. Why? Whenever you call this built-in function, you must declare two variables to retrieve the OUT arguments. You must then interpret the results. Rather than write all this code and have to remember all these rules, you can build it into a package once and then simply call the appropriate program as needed. A prototype of such a package is shown later in this section.

My package specification contains these three sections:

  • Generic interfaces to the built-in, by datatype: return a string value, integer value, or Boolean value. You have to know which type of value should be returned for the name you provide.

  • Functions returning the values of specific named (by the name of the function) entries in the initialization file. You should expand this section to make it easy to retrieve values for parameters you work with.

  • A display procedure to show the different values returned by the built-in for a particular parameter.

    /* Filename on companion disk: dbparm.spp */
CREATE OR REPLACE PACKAGE dbparm
IS
   /* Generic (by datatype) interfaces to built-in. */
   FUNCTION strval (nm IN VARCHAR2) RETURN VARCHAR2;
   FUNCTION intval (nm IN VARCHAR2) RETURN INTEGER;
   FUNCTION boolval (nm IN VARCHAR2) RETURN BOOLEAN;

   /* Encapsulation for specific parameter retrieval */
   FUNCTION nls_date_format RETURN VARCHAR2;
   FUNCTION utl_file_dir RETURN VARCHAR2;
   FUNCTION db_block_buffers RETURN INTEGER;

   PROCEDURE showval (nm IN VARCHAR2);
END;
/

Rather than show the entire package body (also found in dbparm.spp), I will show you the two levels of encapsulation around DBMS_UTILITY.GET_PARAMETER_VALUE found in the package. You can then apply that technique to other parameters of interest.

Here is the dbparm.intval function. It calls the built-in procedure and then returns the integer value. You might want to enhance this procedure to check the datatype of the parameter and only return a value if it is in fact a numeric (or Boolean) type.

FUNCTION intval (nm IN VARCHAR2) RETURN INTEGER
IS
   valtype PLS_INTEGER;
   ival PLS_INTEGER;
   sval VARCHAR2(2000);
BEGIN
   valtype := DBMS_UTILITY.GET_PARAMETER_VALUE (nm, ival, sval);
   RETURN ival;
END;

Now I build my dbparm.db_block_buffers package on top of that one as follows:

FUNCTION db_block_buffers RETURN INTEGER
IS
BEGIN
   RETURN intval ('db_block_buffers');
END;

10.1.2.14 The DBMS_UTILITY.GET_TIME function

This function returns the number of 100ths of seconds that have elapsed from an arbitrary time. The header for the function follows:

FUNCTION DBMS_UTILITY.GET_TIME RETURN NUMBER;

You are probably wondering what this "arbitrary time" is and why I don't tell you about what that starting point is. Two reasons: I don't know and it doesn't matter. You should not use GET_TIME to establish the current time, but only to calculate the elapsed time between two events.

The following example calculates the number of 100ths of elapsed seconds since the calc_totals procedure was executed:

DECLARE
   time_before BINARY_INTEGER;
   time_after BINARY_INTEGER;
BEGIN
   time_before := DBMS_UTILITY.GET_TIME;
   calc_totals;
   time_after := DBMS_UTILITY.GET_TIME;
   DBMS_OUTPUT.PUT_LINE (time_after - time_before);
END;

Without GET_TIME, Oracle functions can only record and provide elapsed time in second intervals, which is a very coarse granularity in today's world of computing. With GET_TIME, you can get a much finer understanding of the processing times of lines in your program.

Notice that in my anonymous block I had to declare two local variables, make my calls to GET_TIME, and then compute the difference. I will probably need to perform those actions over and over again in my programs. I might even want to perform timings that cross product lines (e.g., start my timing in a form and then check elapsed time from inside a report module). To make it easier to use GET_TIME in these various ways, I built a package called sptimer ("stored package timer" mechanism), which you can find in the sptimer.sps and sptimer.spb files on the companion disk.[2]

[2] PL/Vision also offers the PLVtmr package, a much more fully-realized timing utility. See the Preface ("About PL/Vision") for more information.

10.1.2.15 The DBMS_UTILITY.IS_PARALLEL_SERVER function

This function helps determine whether the database is running in parallel server mode. The specification follows:

FUNCTION DBMS_UTILITY.IS_PARALLEL_SERVER  RETURN BOOLEAN;

The function returns TRUE if the database is running in parallel server mode; otherwise, it returns FALSE.

10.1.2.16 The DBMS_UTILITY.MAKE_DATA_BLOCK_ADDRESS function

Use this function to obtain a valid data block address from a file number and block number. The header follows:

FUNCTION DBMS_UTILITY.MAKE_DATA_BLOCK_ADDRESS
   (file IN NUMBER
   ,block IN NUMBER)
RETURN NUMBER;

10.1.2.16.1 Example

Here is an example of calling this function and displaying the resulting value:

SQL> BEGIN
   2    DBMS_OUTPUT.PUT_LINE
   3      (DBMS_UTILITY.MAKE_DATA_BLOCK_ADDRESS (10000, 20000));
   4 END;
   5 /

268455456

10.1.2.17 The DBMS_UTILITY.NAME_RESOLVE procedure

This procedure resolves the name of an object into its component parts, performing synonym translations as necessary. Here's the header for the procedure:

PROCEDURE DBMS_UTILITY.NAME_RESOLVE
   (name IN VARCHAR2, 
    context IN NUMBER,
    schema OUT VARCHAR2,
    part1 OUT VARCHAR2,
    part2 OUT VARCHAR2,
    dblink OUT VARCHAR2,
    part1_type OUT NUMBER, 
    object_number OUT NUMBER);

Parameters are summarized in the following table.

Parameter

Description

name

The name of the object to be resolved.

context

Present for future compatibility; must be set to the value 1.

schema

Name of the object's schema.

part1

The first part of the object's name.

part2

The second part of the object's name (NULL unless the object is a package module, and then part1 is the package name).

dblink

Name of the database link for the object, if any.

part1_type

Indicates the type of object returned in part1.

object_number

The object number for the named object. When object_number is returned NOT NULL, the name was successfully resolved.

An object type may have one of the following values:

5

Synonym

7

Standalone procedure

8

Standalone function

9

Package

The NAME_RESOLVE procedure has six OUT parameters, which means that in order to use this module you will have to declare six variables -- an annoying task that creates an obstacle to casual use of the procedure.

I built a procedure called show_name_components precisely to make it easier to take advantage of NAME_RESOLVE. The show_name_components accepts an object name, and then calls DBMS_OUTPUT.PUT_LINE to display the different components of the name. It shows information only if it is relevant; in other words, if there is no part2, then part2 is not displayed. The name of the database link is displayed only if there is a database link associated with that object.

Here are some examples of calls to show_name_components:

SQL>  execute show_name_components('do.pl');
Schema: BOOK
Package: DO
Name: PL

SQL>  execute show_name_components('do');
Schema: BOOK
Package: DO

SQL> execute show_name_components('show_name_components');
Schema: BOOK
Procedure: SHOW_NAME_COMPONENTS

Here is the show_name_components procedure in its entirety:

/* Filename on companion disk: showcomp.sp */*
CREATE OR REPLACE PROCEDURE show_name_components (name_in IN VARCHAR2)
IS
   /* variables to hold components of the name */
   schema VARCHAR2(100); 
   part1 VARCHAR2(100); 
   part2 VARCHAR2(100);
   dblink VARCHAR2(100); 
   part1_type NUMBER; 
   object_number NUMBER;

   /*--------------------- Local Module -----------------------*/
   FUNCTION object_type (type_in IN INTEGER) 
      RETURN VARCHAR2
   /* Return name for integer type */
   IS
      synonym_type CONSTANT INTEGER := 5;
      procedure_type CONSTANT INTEGER := 7;
      function_type CONSTANT INTEGER := 8;
      package_type CONSTANT INTEGER := 9;
   BEGIN
      IF type_in = synonym_type
      THEN
         RETURN 'Synonym';
      ELSIF type_in = procedure_type
      THEN
         RETURN 'Procedure';
      ELSIF type_in = function_type
      THEN
         RETURN 'Function';
      ELSIF type_in = package_type
      THEN
         RETURN 'Package';
      END IF;
   END;
BEGIN
   /* Break down the name into its components */
   DBMS_UTILITY.NAME_RESOLVE
      (name_in, 1,
       schema, part1, part2,
       dblink , part1_type, object_number);

   /* If the object number is NULL, name resolution failed. */
   IF object_number IS NULL
   THEN
      DBMS_OUTPUT.PUT_LINE 
         ('Name "' || name_in || 
          '" does not identify a valid object.');
   ELSE
      /* Display the schema, which is always available. */
      DBMS_OUTPUT.PUT_LINE ('Schema: ' || schema);

      /* If there is a first part to name, have a package module */
      IF part1 IS NOT NULL
      THEN
         /* Display the first part of the name */
         DBMS_OUTPUT.PUT_LINE
            (object_type (part1_type) || ': ' || part1);

         /* If there is a second part, display that. */
         IF part2 IS NOT NULL
         THEN
            DBMS_OUTPUT.PUT_LINE ('Name: ' || part2);
         END IF;
      ELSE
         /* No first part of name. Just display second part. */
         DBMS_OUTPUT.PUT_LINE
            (object_type (part1_type) || ': ' || part2);
      END IF;

      /* Display the database link if it is present. */
      IF dblink IS NOT NULL
      THEN
         DBMS_OUTPUT.PUT_LINE ('Database Link:' || dblink);
      END IF;
   END IF;
END;
/

10.1.2.18 The DBMS_UTILITY.NAME_TOKENIZE procedure

This procedure calls the PL/SQL parser to parse the given name that is in the following format,

a [ . b [. c]] [@dblink ]

where dblink is the name of a database link. Here's the header for the procedure:

PROCEDURE DBMS_UTILITY.NAME_TOKENIZE
   (name  IN VARCHAR2,
    a OUT VARCHAR2,
    b OUT VARCHAR2,
    c OUT VARCHAR2,
    dblink OUT VARCHAR2,
    nextpos OUT BINARY_INTEGER);

Parameters are summarized in the following table.

Parameter

Description

name

Name being parsed

a, b, c

Components of name, if present

dblink

Name of database link

nextpos

Position where next token starts

NAME_TOKENIZE follows these rules:

  • Strips off all double quotes

  • Converts to uppercase if there are no quotes

  • Ignores any inline comments

  • Does no semantic analysis

  • Leaves any missing values as NULL

10.1.2.19 The DBMS_UTILITY.PORT_STRING function

The PORT_STRING function returns a string that uniquely identifies the version of Oracle Server and the platform or operating system of the current database instance. The specification for this function follows:

FUNCTION DBMS_UTILITY.PORT_STRING RETURN VARCHAR2;

Running the PORT_STRING function in Oracle8 on Windows NT, for example, returns the following string:

IBMPC/WINNT-8.0.0

The maximum length of the string returned by this function is operating system-specific.

10.1.2.20 The DBMS_UTILITY.TABLE_TO_COMMA procedure

The TABLE_TO_COMMA procedure converts a PL/SQL table into a comma-delimited list. Here's the header for this procedure:

PROCEDURE DBMS_UTILITY.TABLE_TO_COMMA
  (tab IN UNCL_ARRAY,
   tablen OUT BINARY_INTEGER,
   list OUT VARCHAR2);

Parameters are summarized in the following table.

Parameter

Description

tab

A PL/SQL table declared using the package's TABLE type

tablen

The number of rows defined in the PL/SQL table (assumed to be densely packed, all rows contiguously defined)

list

The string that will contain a comma-delimited list of the names for the PL/SQL table


Previous: 9.2 UTL_RAW: Manipulating Raw DataOracle Built-in PackagesNext: 10.2 DBMS_DESCRIBE: Describing PL/SQL Program Headers
9.2 UTL_RAW: Manipulating Raw DataBook Index10.2 DBMS_DESCRIBE: Describing PL/SQL Program Headers

The Oracle Library Navigation

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference