DBMS_UTILITY

The DBMS_UTILITY package provides various utility subprograms.

This chapter contains the following topics:

Using DBMS_UTILITY

  • Summary of DBMS_UTILITY Subprograms

  • Security Model

    DBMS_UTILITY runs with the privileges of the calling user for the NAME_RESOLVE Procedure, the COMPILE_SCHEMA Procedure, and the ANALYZE_SCHEMA Procedure. This is necessary so that the SQL works correctly.

    The package does not run as SYS. The privileges are checked using DBMS_DDL.

    Constants

    The DBMS_UTILITY package uses the constants shown in Table 116-1, "DBMS_UTILITY Constants".

    Table 116-1 DBMS_UTILITY Constants

    Name Type Value Description

    INV_ERROR_ON_RESTRICTIONS

    PLS_INTEGER

    1

    This constant is the only legal value for the p_option_flags parameter of the INVALIDATE subprogram

    Types

    dblink_array

    TYPE dblink_array IS TABLE OF VARCHAR2(128) INDEX BY BINARY_INTEGER;

    Lists of database links should be stored here.

    index_table_type

    TYPE index_table_type IS TABLE OF BINARY_INTEGER INDEX BY BINARY_INTEGER;

    The order in which objects should be generated is returned here.

    instance_record

      TYPE instance_record IS RECORD (
           inst_number   NUMBER,
           inst_name     VARCHAR2(60));
      TYPE instance_table IS TABLE OF instance_record INDEX BY BINARY_INTEGER;

    The list of active instance number and instance name.

    The starting index of instance_table is 1; instance_table is dense.

    lname_array

    TYPE lname_array IS TABLE OF VARCHAR2(4000) index by BINARY_INTEGER;

    Lists of Long NAME should be stored here, it includes fully qualified attribute names.

    name_array

    
    TYPE name_array IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;

    Lists of NAME should be stored here.

    number_array

    TYPE number_array IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;

    The order in which objects should be generated is returned here for users.

    uncl_array

    TYPE uncl_array IS TABLE OF VARCHAR2(227) INDEX BY BINARY_INTEGER;

    Lists of "USER"."NAME"."COLUMN"@LINK should be stored here.

    Deprecated Subprograms

    Obsolete with Oracle Database Release 10g:

    • ANALYZE_DATABASE Procedure
    • ANALYZE_SCHEMA Procedure

    Exceptions

    The following table lists the exceptions raised by DBMS_UTILITY.

    Table 116-2 Exceptions Raised by DBMS_UTILITY

    Exception Error Code Description

    INV_NOT_EXIST_OR_NO_PRIV

    -24237

    Raised by the INVALIDATE subprogram when the object_id argument is NULL or invalid, or when the caller does not have CREATE privileges on the object being invalidated

    INV_MALFORMED_SETTINGS

    -24238

    Raised by the INVALIDATE subprogram if a compiler setting is specified more than once in the p_plsql_object_settings parameter

    INV_RESTRICTED_OBJECT

    -24239

    Raised by the INVALIDATE subprogram when different combinations of conditions pertaining to the p_object_id parameter are contravened

    Summary of DBMS_UTILITY Subprograms

    Table 116-3 DBMS_UTILITY Package Subprograms

    Subprogram Description

    ACTIVE_INSTANCES Procedure

    Returns the active instance

    ANALYZE_DATABASE Procedure

    Analyzes all the tables, clusters, and indexes in a database [see also Deprecated Subprograms]

    ANALYZE_PART_OBJECT Procedure

    Analyzes the given tables and indexes

    ANALYZE_SCHEMA Procedure

    Analyzes all the tables, clusters, and indexes in a schema [see also Deprecated Subprograms]

    CANONICALIZE Procedure

    Canonicalizes a given string

    COMMA_TO_TABLE Procedures

    Converts a comma-delimited list of names into a PL/SQL table of names

    COMPILE_SCHEMA Procedure

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

    CREATE_ALTER_TYPE_ERROR_TABLE Procedure

    Creates an error table to be used in the EXCEPTION clause of the ALTER TYPE statement

    CURRENT_INSTANCE Function

    Returns the current connected instance number

    DATA_BLOCK_ADDRESS_BLOCK Function

    Gets the block number part of a data block address

    DATA_BLOCK_ADDRESS_FILE Function

    Gets the file number part of a data block address

    DB_VERSION Procedure

    Returns version information for the database

    EXEC_DDL_STATEMENT Procedure

    Executes the DDL statement in parse_string

    FORMAT_CALL_STACK Function

    Formats the current call stack

    FORMAT_ERROR_BACKTRACE Function

    Formats the backtrace from the point of the current error to the exception handler where the error has been caught

    FORMAT_ERROR_STACK Function

    Formats the current error stack

    GET_CPU_TIME Function

    Returns the current CPU time in 100th's of a second

    GET_DEPENDENCY Procedure

    Shows the dependencies on the object passed in.

    GET_HASH_VALUE Function

    Computes a hash value for the given string

    GET_PARAMETER_VALUE Function

    Gets the value of specified init.ora parameter

    GET_TIME Function

    Finds out the current time in 100th's of a second

    INVALIDATE Procedure

    Invalidates a database object and (optionally) modifies its PL/SQL compiler parameter settings

    IS_CLUSTER_DATABASE Function

    Finds out if this database is running in cluster database mode

    MAKE_DATA_BLOCK_ADDRESS Function

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

    NAME_RESOLVE Procedure

    Resolves the given name

    NAME_RESOLVE Procedure

    Calls the parser to parse the given name

    PORT_STRING Function

    Returns a string that uniquely identifies the version of Oracle and the operating system

    TABLE_TO_COMMA Procedures

    Converts a PL/SQL table of names into a comma-delimited list of names

    VALIDATE Procedure

    Converts a PL/SQL table of names into a comma-delimited list of names

    ACTIVE_INSTANCES Procedure

    This procedure returns the active instance.

    Syntax

    DBMS_UTILITY.ACTIVE_INSTANCES (
       instance_table   OUT INSTANCE_TABLE,
       instance_count   OUT NUMBER); 

    Parameters

    Table 116-4 ACTIVE_INSTANCES Procedure Parameters

    Procedure Description

    instance_table

    Contains a list of the active instance numbers and names. When no instance is up, the list is empty.

    instance_count

    Number of active instances.

    ANALYZE_DATABASE Procedure

    Note:
    This subprogram is obsolete with release Oracle Database Release 10g. It is retained in documentation for reasons of backward compatibility. For current functionality, see Chapter 103, "DBMS_STATS".

    This procedure runs the ANALYZE command on all the tables, clusters, and indexes in a database. Use this procedure to collect nonoptimizer statistics. For optimizer statistics, use the DBMS_STATS.GATHER_DATABASE_STATS procedure.

    Syntax

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

    Parameters

    Table 116-5 ANALYZE_DATABASE Procedure Parameters

    Parameter Description

    method

    One of ESTIMATE>, COMPUTE or DELETE.

    If ESTIMATE, then either estimate_rows or estimate_percent must be nonzero.

    estimate_rows

    Number of rows to estimate.

    estimate_percent

    Percentage of rows to estimate.

    If estimate_rows is specified, then ignore this parameter.

    method_opt

    Method options of the following format:

    [ FOR TABLE ]
    [ FOR ALL [INDEXED] COLUMNS] [SIZE n]
    [ FOR ALL INDEXES ]
    

    Exceptions

    Table 116-6 ANALYZE_DATABASE Procedure Exceptions

    Exception Description

    ORA-20000

    Insufficient privileges for some object in this database.

    Usage Notes

    Use this procedure to collect nonoptimizer statistics. For optimizer statistics, use the DBMS_STATS.GATHER_TABLE_STATS or DBMS_STATS.GATHER_INDEX_STATS procedure.

    ANALYZE_PART_OBJECT Procedure

    This procedure is equivalent to SQL:

    "ANALYZE TABLE|INDEX [.] PARTITION  [] [] [] 

    Syntax

    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

    Table 116-7 ANALYZE_PART_OBJECT Procedure Parameters

    Parameter Description

    schema

    Schema of the object_name.

    object_name

    Name of object to be analyzed, must be partitioned.

    object_type

    Type of object, must be T (table) or I (index).

    command_type

    Must be V (validate structure)

    command_opt

    Other options for the command type.

    For C, E it can be FOR table, FOR all LOCAL indexes, FOR all columns or combination of some of the 'for' options of analyze statistics (table). For V, it can be CASCADE when object_type is T.

    sample_clause

    The sample clause to use when command_type is 'E'.

    Usage Notes

    For each partition of the object, run in parallel using job queues.

    ANALYZE_SCHEMA Procedure

    Note:
    This subprogram is obsolete with Oracle Database Release 10g. It is retained in documentation for reasons of backward compatibility. For current functionality, see Chapter 103, "DBMS_STATS".

    This procedure runs the ANALYZE command on all the tables, clusters, and indexes in a schema. Use this procedure to collect nonoptimizer statistics. For optimizer statistics, use the DBMS_STATS.GATHER_SCHEMA_STATS procedure.

    Syntax

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

    Parameters

    Table 116-8 ANALYZE_SCHEMA Procedure Parameters

    Parameter Description

    schema

    Name of the schema.

    method

    One of ESTIMATE, COMPUTE or DELETE.

    If ESTIMATE, then either estimate_rows or estimate_percent must be nonzero.

    estimate_rows

    Number of rows to estimate.

    estimate_percent

    Percentage of rows to estimate.

    If estimate_rows is specified, then ignore this parameter.

    method_opt

    Method options of the following format:

    [ FOR TABLE ]
    [ FOR ALL [INDEXED] COLUMNS] [SIZE n]
    [ FOR ALL INDEXES ]
    

    Exceptions

    Table 116-9 ANALYZE_SCHEMA Procedure Exceptions

    Exception Description

    ORA-20000

    Insufficient privileges for some object in this schema.

    CANONICALIZE Procedure

    This procedure canonicalizes the given string. The procedure handles a single reserved or key word (such as 'table'), and strips off white spaces for a single identifier so that ' table ' becomes TABLE.

    Syntax

    DBMS_UTILITY.CANONICALIZE(
       name           IN    VARCHAR2,
       canon_name     OUT   VARCHAR2,
       canon_len      IN    BINARY_INTEGER);

    Parameters

    Table 116-10 CANONICALIZE Procedure Parameters

    Parameter Description

    name>

    The string to be canonicalized

    canon_name>

    The canonicalized string

    canon_len

    The length of the string (in bytes) to canonicalize

    Return Values

    Returns the first canon_len bytes in canon_name.

    Usage Notes

    • If name is NULL, canon_name becomes NULL.
    • If name is not a dotted name, and if name begins and ends with a double quote, remove both quotes. Alternatively, convert to upper case with NLS_UPPER. Note that this case does not include a name with special characters, such as a space, but is not doubly quoted.
    • If name is a dotted name (such as a."b".c), for each component in the dotted name in the case in which the component begins and ends with a double quote, no transformation will be performed on this component. Alternatively, convert to upper case with NLS_UPPER and apply begin and end double quotes to the capitalized form of this component. In such a case, each canonicalized component will be concatenated together in the input position, separated by ".".
    • Any other character after a[.b]* will be ignored.
    • The procedure does not handle cases like 'A B.'

    Examples

    • a becomes A
    • "a" becomes a
    • "a".b becomes "a"."B"
    • "a".b,c.f becomes "a"."B" with",c.f" ignored.

    COMMA_TO_TABLE Procedures

    These procedures converts a comma-delimited list of names into a PL/SQL table of names. The second version supports fully-qualified attribute names.

    Syntax

    DBMS_UTILITY.COMMA_TO_TABLE ( 
       list   IN  VARCHAR2,
       tablen OUT BINARY_INTEGER,
       tab    OUT uncl_array); 
    
    DBMS_UTILITY.COMMA_TO_TABLE ( 
       list   IN  VARCHAR2,
       tablen OUT BINARY_INTEGER,
       tab    OUT lname_array);
    

    Parameters

    Table 116-11 COMMA_TO_TABLE Procedure Parameters

    Parameter Description

    list

    Comma separated list of tables.

    tablen

    Number of tables in the PL/SQL table.

    tab

    PL/SQL table which contains list of table names.

    Return Values

    A PL/SQL table is returned, with values 1..n and n+1 is null.

    Usage Notes

    The list must be a non-empty comma-delimited list: Anything other than a comma-delimited list is rejected. Commas inside double quotes do not count.

    Entries in the comma-delimited list cannot include multibyte characters such as hyphens (-).

    The values in tab are cut from the original list, with no transformations.

    COMPILE_SCHEMA Procedure

    This procedure compiles all procedures, functions, packages, and triggers in the specified schema.

    Syntax

    DBMS_UTILITY.COMPILE_SCHEMA (
       schema          VARCHAR2,
       compile_all     BOOLEAN DEFAULT TRUE,
       reuse_settings  BOOLEAN DEFAULT FALSE);

    Parameters

    Table 116-12 COMPILE_SCHEMA Procedure Parameters

    Parameter Description

    schema

    Name of the schema

    compile_all

    If TRUE, will compile everything within the schema regardless of whether it is VALID

    If FALSE, will compile only INVALID objects

    reuse_settings

    Indicates whether the session settings in the objects should be reused, or whether the current session settings should be adopted instead

    Exceptions

    Table 116-13 COMPILE_SCHEMA Procedure Exceptions

    Exception Description

    ORA-20000

    Insufficient privileges for some object in this schema

    ORA-20001

    Cannot recompile SYS objects

    ORA-20002

    Maximum iterations exceeded. Some objects may not have been recompiled.

    Usage Notes

    After calling this procedure, you should select from view ALL_OBJECTS for items with status of INVALID to see if all objects were successfully compiled.

    To see the errors associated with INVALID objects, you may use the Enterprise Manager command:

    SHOW ERRORS  .

    CREATE_ALTER_TYPE_ERROR_TABLE Procedure

    This procedure creates an error table to be used in the EXCEPTION clause of the ALTER TYPE statement.

    Syntax

    DBMS_UTILITY.CREATE_ALTER_TYPE_ERROR_TABLE(
       schema_name     IN     VARCHAR2,
       table_name      IN     VARCHAR2);

    Parameters

    Table 116-14 CREATE_ALTER_TYPE_ERROR_TABLE Procedure Parameters

    Parameter Description

    schema_name

    The name of the schema.

    table_name

    The name of the table created.

    Exceptions

    An error is returned if the table already exists.

    CURRENT_INSTANCE Function

    This function returns the current connected instance number. It returns NULL when connected instance is down.

    Syntax

    DBMS_UTILITY.CURRENT_INSTANCE
       RETURN NUMBER;

    DATA_BLOCK_ADDRESS_BLOCK Function

    This function gets the block number part of a data block address.

    Syntax

    DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK (
       dba NUMBER) 
      RETURN NUMBER;

    Parameters

    Table 116-15 DATA_BLOCK_ADDRESS_BLOCK Function Parameters

    Parameter Description

    dba

    Data block address.

    Pragmas

    pragma restrict_references(data_block_address_block, WNDS, RNDS, WNPS, RNPS);

    Return Values

    Block offset of the block.

    Usage Notes

    This function should not be used with datablocks which belong to bigfile tablespaces.

    DATA_BLOCK_ADDRESS_FILE Function

    This function gets the file number part of a data block address.

    Syntax

    DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE (
       dba NUMBER) 
      RETURN NUMBER;

    Parameters

    Table 116-16 DATA_BLOCK_ADDRESS_FILE Function Parameters

    Parameter Description

    dba

    Data block address.

    Pragmas

    pragma restrict_references (data_block_address_file, WNDS, RNDS, WNPS, RNPS);

    Return Values

    File that contains the block.

    Usage Notes

    This function should not be used with datablocks which belong to bigfile tablespaces.

    DB_VERSION Procedure

    This procedure returns version information for the database.

    Syntax

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

    Parameters

    Table 116-17 DB_VERSION Procedure Parameters

    Parameter Description

    version

    A string which represents the internal software version of the database (for example, 7.1.0.0.0).

    The length of this string is variable and is determined by the database version.

    compatibility

    The compatibility setting of the database determined by the "compatible" init.ora parameter.

    If the parameter is not specified in the init.ora file, then NULL is returned.

    EXEC_DDL_STATEMENT Procedure

    This procedure executes the DDL statement in parse_string.

    Syntax

    DBMS_UTILITY.EXEC_DDL_STATEMENT (
       parse_string IN VARCHAR2);

    Parameters

    Table 116-18 EXEC_DDL_STATEMENT Procedure Parameters

    Parameter Description

    parse_string

    DDL statement to be executed.

    FORMAT_CALL_STACK Function

    This function formats the current call stack. This can be used on any stored procedure or trigger to access the call stack. This can be useful for debugging.

    Syntax

    DBMS_UTILITY.FORMAT_CALL_STACK 
      RETURN VARCHAR2;

    Pragmas

    pragma restrict_references(format_call_stack,WNDS); 

    Return Values

    This returns the call stack, up to 2000 bytes.

    FORMAT_ERROR_BACKTRACE Function

    This procedure displays the call stack at the point where an exception was raised, even if the procedure is called from an exception handler in an outer scope. The output is similar to the output of the SQLERRM function, but not subject to the same size limitation.

    Syntax

    DBMS_UTILITY.FORMAT_ERROR_BACKTRACE 
      RETURN VARCHAR2;

    Return Values

    The backtrace string. A NULL string is returned if no error is currently being handled.

    Examples

    CREATE OR REPLACE PROCEDURE Log_Errors ( i_buff in varchar2 ) IS
      g_start_pos integer := 1;
      g_end_pos  integer;
    
      FUNCTION Output_One_Line RETURN BOOLEAN IS
      BEGIN
        g_end_pos := Instr ( i_buff, Chr(10), g_start_pos );
    
        CASE g_end_pos > 0
          WHEN true THEN
            DBMS_OUTPUT.PUT_LINE ( Substr ( i_buff, g_start_pos,
    g_end_pos-g_start_pos ) );
            g_start_pos := g_end_pos+1;
            RETURN TRUE;
    
          WHEN FALSE THEN
            DBMS_OUTPUT.PUT_LINE ( Substr ( i_buff, g_start_pos,
    (Length(i_buff)-g_start_pos)+1 ) );
            RETURN FALSE;
        END CASE;
      END Output_One_Line;
    
    BEGIN
      WHILE Output_One_Line() LOOP NULL; 
      END LOOP;
    END Log_Errors;
    /
    
    Set Doc Off
    Set Feedback off
    Set Echo Off
    
    CREATE OR REPLACE PROCEDURE P0 IS
      e_01476 EXCEPTION; pragma exception_init ( e_01476, -1476 );
    BEGIN
      RAISE e_01476;
    END P0;
    /
    Show Errors
    
    CREATE OR REPLACE PROCEDURE P1 IS
    BEGIN
      P0();
    END P1;
    /
    SHOW ERRORS
    
    CREATE OR REPLACE PROCEDURE P2 IS
    BEGIN
      P1();
    END P2;
    /
    SHOW ERRORS
    
    CREATE OR REPLACE PROCEDURE P3 IS
    BEGIN
      P2();
    END P3;
    /
    SHOW ERRORS
    
    CREATE OR REPLACE PROCEDURE P4 IS
      BEGIN P3(); END P4;
    /
    CREATE OR REPLACE PROCEDURE P5 IS
      BEGIN P4(); END P5;
    /
    SHOW ERRORS
    
    CREATE OR REPLACE PROCEDURE Top_Naive IS
    BEGIN
      P5();
    END Top_Naive;
    /
    SHOW ERRORS
    
    CREATE OR REPLACE PROCEDURE Top_With_Logging IS
      -- NOTE: SqlErrm in principle gives the same info as Format_Error_Stack.
      -- But SqlErrm is subject to some length limits,
      -- while Format_Error_Stack is not.
    BEGIN
      P5();
    EXCEPTION
      WHEN OTHERS THEN
        Log_Errors ( 'Error_Stack...' || Chr(10) ||
          DBMS_UTILITY.FORMAT_ERROR_STACK() );
        Log_Errors ( 'Error_Backtrace...' || Chr(10) ||
          DBMS_UTILITY.FORMAT_ERROR_BACKTRACE() );
        DBMS_OUTPUT.PUT_LINE ( '----------' );
    END Top_With_Logging;
    /
    SHOW ERRORS
    
    --------------------------------------------------------------------------------
    
    Set ServerOutput On
    call Top_Naive()
      /*
      ERROR at line 1:
      ORA-01476: divisor is equal to zero
      ORA-06512: at "U.P0", line 4
      ORA-06512: at "U.P1", line 3
      ORA-06512: at "U.P2", line 3
      ORA-06512: at "U.P3", line 3
      ORA-06512: at "U.P4", line 2
      ORA-06512: at "U.P5", line 2
      ORA-06512: at "U.TOP_NAIVE", line 3
      */
      ;
    
    Set ServerOutput On
    call Top_With_Logging()
      /*
      Error_Stack...
      ORA-01476: divisor is equal to zero
      Error_Backtrace...
      ORA-06512: at "U.P0", line 4
      ORA-06512: at "U.P1", line 3
      ORA-06512: at "U.P2", line 3
      ORA-06512: at "U.P3", line 3
      ORA-06512: at "U.P4", line 2
      ORA-06512: at "U.P5", line 2
      ORA-06512: at "U.TOP_WITH_LOGGING", line 6
      ----------
      */
      ;
    
    /*
      ORA-06512:
      Cause:
        Backtrace message as the stack is
        unwound by unhandled exceptions.
      Action:
        Fix the problem causing the exception
        or write an exception handler for this condition.
        Or you may need to contact your application administrator
        or database administrator.
    */

    FORMAT_ERROR_STACK Function

    This function formats the current error stack. This can be used in exception handlers to look at the full error stack.

    Syntax

    DBMS_UTILITY.FORMAT_ERROR_STACK 
      RETURN VARCHAR2;

    Return Values

    This returns the error stack, up to 2000 bytes.

    Return Values

    See

    FORMAT_ERROR_BACKTRACE Function.

    GET_CPU_TIME Function

    This function returns the current CPU time in 100th's of a second. The returned CPU time is the number of 100th's of a second from some arbitrary epoch.

    Syntax

      DBMS_UTILITY.GET_CPU_TIME
       RETURN NUMBER;

    Return Values

    Time is the number of 100th's of a second from some arbitrary epoch.

    GET_DEPENDENCY Procedure

    This procedure shows the dependencies on the object passed in.

    Syntax

      DBMS_UTILITY.GET_DEPENDENCY
       type      IN     VARCHAR2,
       schema    IN     VARCHAR2,
       name      IN     VARCHAR2);

    Parameters

    Table 116-19 GET_DEPENDENCY Procedure Parameters

    Parameter Description

    type

    The type of the object, for example if the object is a table give the type as 'TABLE'.

    schema

    The schema name of the object.

    name

    The name of the object.

    Usage Notes

    This procedure uses the DBMS_OUTPUTpackage to display results, and so you must declare SET SERVEROUTPUT ON if you wish to view dependencies. Alternatively, any application that checks the DBMS_OUTPUT output buffers can invoke this subprogram and then retrieve the output by means of DBMS_OUTPUT subprograms such as GET_LINES.

    GET_HASH_VALUE Function

    This function computes a hash value for the given string.

    Syntax

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

    Parameters

    Table 116-20 GET_HASH_VALUE Function Parameters

    Parameter Description

    name

    String to be hashed.

    base

    Base value for the returned hash value to start at.

    hash_size

    Desired size of the hash table.

    Pragmas

    pragma restrict_references(get_hash_value, WNDS, RNDS, WNPS, RNPS); 

    Return Values

    A hash value based on the input string. For example, to get a hash value on a string where the hash value should be between 1000 and 3047, use 1000 as the base value and 2048 as the hash_size value. Using a power of 2 for the hash_size parameter works best.

    GET_PARAMETER_VALUE Function

    This function gets the value of specified init.ora parameter.

    Syntax

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

    Parameters

    Table 116-21 GET_PARAMETER_VALUE Function Parameters

    Parameter Description

    parnam

    Parameter name.

    intval

    Value of an integer parameter or the value length of a string parameter.

    strval

    Value of a string parameter.

    Return Values

    Parameter type:

    • 0 if parameter is an INTEGER/BOOLEAN parameter
    • 1 if parameter is a string/file parameter

    Usage Notes

    When using DBMS_UTILITY.GET_PARAMETER_VALUE, only the first parameter setting of /dir1 is returned when init.ora is set as follows:

    utl_file_dir = /dir1
    utl_file_dir = /dir2

    However, the full comma-delimited string is returned if you are using:

    utl_file_dir = /dir1, /dir2

    Examples

    DECLARE
      parnam VARCHAR2(256);
      intval BINARY_INTEGER;
      strval VARCHAR2(256);
      partyp BINARY_INTEGER;
    BEGIN
      partyp := dbms_utility.get_parameter_value('max_dump_file_size',
                                                  intval, strval);
      dbms_output.put('parameter value is: ');
      IF partyp = 1 THEN
        dbms_output.put_line(strval);
      ELSE
        dbms_output.put_line(intval);
      END IF;
      IF partyp = 1 THEN
        dbms_output.put('parameter value length is: ');
        dbms_output.put_line(intval);
      END IF;
      dbms_output.put('parameter type is: ');
      IF partyp = 1 THEN
        dbms_output.put_line('string');
      ELSE
        dbms_output.put_line('integer');
      END IF;
    END;

    GET_TIME Function

    This function determines the current time in 100th's of a second. This subprogram is primarily used for determining elapsed time. The subprogram is called twice – at the beginning and end of some process – and then the first (earlier) number is subtracted from the second (later) number to determine the time elapsed.

    Syntax

    DBMS_UTILITY.GET_TIME 
      RETURN NUMBER;

    Return Values

    Time is the number of 100th's of a second from the point in time at which the subprogram is invoked.

    Usage Notes

    Numbers are returned in the range -2147483648 to 2147483647 depending on platform and machine, and your application must take the sign of the number into account in determining the interval. For instance, in the case of two negative numbers, application logic must allow that the first (earlier) number will be larger than the second (later) number which is closer to zero. By the same token, your application should also allow that the first (earlier) number be negative and the second (later) number be positive.

    INVALIDATE Procedure

    This procedure invalidates a database object and (optionally) modifies its PL/SQL compiler parameter settings. It also invalidates any objects that (directly or indirectly) depend on the object being invalidated.

    Syntax

    DBMS_UTILITY.INVALIDATE ( 
       p_object_id              NUMBER,
       p_plsql_object_settings  VARCHAR2 DEFAULT NULL,
       p_option_flags           PLS_INTEGER DEFAULT 0);

    Parameters

    Table 116-22 INVALIDATE Procedure Parameters

    Parameter Description

    p_object_id

    ID number of object to be invalidated. This is the same as the value of the OBJECT_ID column from ALL_OBJECTS. If the object_id argument is NULL or invalid then the exception inv_not_exist_or_no_priv is raised. The caller of this procedure must have create privileges on the object being invalidated else the inv_not_exist_or_no_priv exception is raised.

    p_plsql_object_settings

    This optional parameter is ignored if the object specified by p_object_id is not a PL/SQL object. If no value is specified for this parameter then the PL/SQL compiler settings are left unchanged, that is, equivalent to REUSE SETTINGS. If a value is provided, it must specify the values of the PL/SQL compiler settings separated by one or more spaces. Each setting can be specified only once else inv_malformed_settings exception will be raised. The setting values are changed only for the object specified by p_object_id and do not affect dependent objects that may be invalidated. The setting names and values are case insensitive. If a setting is omitted and REUSE SETTINGS is specified, then if a value was specified for the compiler setting in an earlier compilation of this library unit, Oracle Database uses that earlier value. If a setting is omitted and REUSE ETTINGS was not specified or no value has been specified for the parameter in an earlier compilation, then the database will obtain the value for that setting from the session environment.

    p_option_flags

    This parameter is optional and defaults to zero (no flags). Option flags supported by invalidate.

    • inv_error_on_restrictions (see Constants ): The subprogram imposes various restrictions on the objects that can be invalidated. For example, the object specified by p_object_id cannot be a table. By default, invalidate quietly returns on these conditions (and does not raise an exception). If the caller sets this flag, the exception inv_restricted_object is raised.

    Exceptions

    Table 116-23 INVALIDATE Exceptions

    Exception Description

    INV_NOT_EXIST_OR_NO_PRIV

    Raised when the object_id argument is NULL or invalid, or when the caller does not have CREATE privileges on the object being invalidated

    INV_MALFORMED_SETTINGS

    Raised if a compiler setting is specified more than once in the p_plsql_object_settings parameter

    INV_RESTRICTED_OBJECT

    Raised when different combinations of conditions pertaining to the p_object_id parameter are contravened

    Usage Notes

    The object type (object_type column from ALL_OBJECTS) of the object specified by p_object_id must be a PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY, TRIGGER, TYPE, TYPE BODY, LIBRARY, VIEW, OPERATOR, SYNONYM, or JAVA CLASS. If the object is not one of these types and the flag inv_error_on_restrictions is specified in p_option_flags then the exception inv_restricted_object is raised, else no action is taken.

    If the object specified by p_object_id is the package specification of STANDARD, DBMS_STANDARD, or specification or body of DBMS_UTILITY and the flag inv_error_on_restrictions is specified in p_option_flags then the exception inv_restricted_object is raised, else no action is taken.

    If the object specified by p_object_id is an object type specification and there exist tables which depend on the type and the flag inv_error_on_restrictions is specified in p_option_flags then the exception inv_restricted_object is raised, else no action is taken.

    Examples

    Example 1
    DBMS_UTILITY.INVALIDATE (1232, 'PLSQL_OPTIMIZE_LEVEL = 2 REUSE SETTINGS');

    Assume that the object_id 1232 refers to the procedure remove_emp in the HR schema. Then the above call will mark the remove_emp procedure invalid and change it's PLSQL_OPTIMIZE_LEVEL compiler setting to 2. The values of other compiler settings will remain unchanged since REUSE SETTINGS is specified.

    Objects that depend on hr.remove_emp will also get marked invalid. Their compiler parameters will not be changed.

    Example 2
    DBMS_UTILITY.INVALIDATE (40775, 'plsql_code_type = native');

    Assume that the object_id 40775 refers to the type body leaf_category_typ in the OE schema. Then the above call will mark the type body invalid and change its PLSQL_CODE_TYPE compiler setting to NATIVE. The values of other compiler settings will be picked up from the current session environment since REUSE SETTINGS has not been specified.

    Since no objects can depend on bodies, there are no cascaded invalidations.

    Example 3
    DBMS_UTILITY.INVALIDATE (40796);

    Assume that the object_id 40796 refers to the view oc_orders in the OE schema. Then the above call will mark the oc_orders view invalid.

    Objects that depend on oe.oc_orders will also get marked invalid.

    IS_CLUSTER_DATABASE Function

    This function finds out if this database is running in cluster database mode.

    Syntax

    DBMS_UTILITY.IS_CLUSTER_DATABASE 
      RETURN BOOLEAN;

    Return Values

    This function returns TRUE if this instance was started in cluster database mode; FALSE otherwise.

    MAKE_DATA_BLOCK_ADDRESS Function

    This function creates a data block address given a file number and a block number. A data block address is the internal structure used to identify a block in the database. This function is useful when accessing certain fixed tables that contain data block addresses.

    Syntax

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

    Parameters

    Table 116-24 MAKE_DATA_BLOCK_ADDRESS Function Parameters

    Parameter Description

    file

    File that contains the block.

    block

    Offset of the block within the file in terms of block increments.

    Pragmas

    pragma restrict_references (make_data_block_address, WNDS, RNDS, WNPS, RNPS);

    Return Values

    Data block address.

    NAME_RESOLVE Procedure

    This procedure resolves the given name, including synonym translation and authorization checking as necessary.

    Syntax

    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

    Table 116-25 NAME_RESOLVE Procedure Parameters

    Parameter Description

    name

    Name of the object.

    This can be of the form [[a.]b.]c[@d], where a, b, c are SQL identifier and d is a dblink. No syntax checking is performed on the dblink. If a dblink is specified, or if the name resolves to something with a dblink, then object is not resolved, but the schema, part1, part2 and dblink OUT parameters are filled in.

    a, b and c may be delimited identifiers, and may contain Globalization Support (NLS) characters (single and multibyte).

    context

    Must be an integer between 0 and 8.

    schema

    Schema of the object: c. If no schema is specified in name, then the schema is determined by resolving the name.

    part1

    First part of the name. The type of this name is specified part1_type(synonym or package).

    part2

    If this is non-NULL, then this is a subprogram name. If part1 is non-NULL, then the subprogram is within the package indicated by part1. If part1 is NULL, then the subprogram is a top-level subprogram.

    dblink

    If this is non-NULL, then a database link was either specified as part of name or name was a synonym which resolved to something with a database link. In this case, if further name translation is desired, then you must call the DBMS_UTILITY.NAME_RESOLVE procedure on this remote node.

    part1_type

    Type of part1 is:

    • 5 - synonym

    • 7 - procedure (top level)

    • 8 - function (top level)

    • 9 - package

    object_number

    Object identifier

    Exceptions

    All errors are handled by raising exceptions. A wide variety of exceptions are possible, based on the various syntax error that are possible when specifying object names.

    NAME_TOKENIZE Procedure

    This procedure calls the parser to parse the given name as "a [. b [. c ]][@ dblink ]". It strips double quotes, or converts to uppercase if there are no quotes. It ignores comments of all sorts, and does no semantic analysis. Missing values are left as NULL.

    Syntax

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

    Parameters

    For each of a, b, c, dblink, tell where the following token starts in anext, bnext, cnext, dnext respectively.

    PORT_STRING Function

    This function returns a string that identifies the operating system and the TWO TASK PROTOCOL version of the database. For example, "VAX/VMX-7.1.0.0"

    The maximum length is port-specific.

    Syntax

    DBMS_UTILITY.PORT_STRING 
       RETURN VARCHAR2;

    Pragmas

    pragma restrict_references(port_string, WNDS, RNDS, WNPS, RNPS);

    TABLE_TO_COMMA Procedures

    These procedures converts a PL/SQL table of names into a comma-delimited list of names. This takes a PL/SQL table, 1..n, terminated with n+1 null. The second version supports fully-qualified attribute names.

    Syntax

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

    Parameters

    Table 116-26 TABLE_TO_COMMA Procedure Parameters

    Parameter Description

    tab

    PL/SQL table which contains list of table names.

    tablen

    Number of tables in the PL/SQL table.

    list

    Comma separated list of tables.

    Return Values

    A comma-delimited list and the number of elements found in the table.

    VALIDATE Procedure

    This procedure makes invalid database objects valid.

    Syntax

      DBMS_UTILITY.VALIDATE(
        object_id       NUMBER);

    Parameters

    Table 116-27 VALIDATE Procedure Parameters

    Parameter Description

    object_id

    The ID number of object to be validated. This is the same as the value of the OBJECT_ID column from ALL_OBJECTS.