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.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.

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 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.

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.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.

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.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.

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

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.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.

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.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.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.

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 2³⁰.

• 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.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.

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.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.

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.

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.