104
DBMS_UTILITY

The DBMS_UTILITY package provides various utility subprograms.

This chapter contains the following topics:

Using DBMS_UTILITY

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.

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:

Summary of DBMS_UTILITY Subprograms

Table 104-1 DBMS_UTILITY Package Subprograms

Subprogram	Description
ANALYZE_DATABASE Procedure	Analyzes all the tables, clusters, and indexes in a database [see also Deprecated Subprograms]
ACTIVE_INSTANCES Procedure	Returns the active instance
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_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
FORMAT_CALL_STACK Function	Formats the current call 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
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_TOKENIZE 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

ANALYZE_DATABASE Procedure

Note:

This subprogam is obsolete with release Oracle Database Release 10g. It is retained in documentation for reasons of backward compatibility. For current functionality, see "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 104-2 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 104-3 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.

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 104-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_PART_OBJECT Procedure

This procedure is equivalent to SQL:

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

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 104-5 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 subprogam is obsolete with Oracle Database Release 10g. It is retained in documentation for reasons of backward compatibility. For current functionality, see "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 104-6 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 104-7 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 104-8 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 104-9 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. 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 <type> <schema>.<name>

Syntax

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

Parameters

Table 104-10 COMPILE_SCHEMA Procedure Parameters

Parameter	Description
`schema`	Name of the schema.

Exceptions

Table 104-11 COMPILE_SCHEMA Procedure Exceptions

Exception	Description
`ORA-20000`	Insufficient privileges for some object in this schema.

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 104-12 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 104-13 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

Table 104-14 DATA_BLOCK_ADDRESS_BLOCK Function Return Values

Returns	Description
`block`	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 104-15 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

Table 104-16 DATA_BLOCK_ADDRESS_FILE Function Return Values

Returns	Description
`file`	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 104-17 DB_VERSION Procedure Parameters

Parameter Description

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.

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 104-18 EXEC_DDL_STATEMENT Procedure Parameters

Parameter	Description
`parse_string`	DDL statement to be executed.

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 .

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.

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

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

Table 104-22 GET_PARAMETER_VALUE Function Return Values

Returns Description

Returns	Description
`partyp`	Parameter type: 0 if parameter is an integer/boolean parameter 1 if parameter is a string/file parameter

partyp

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 finds out the current time in 100th's of a second. It is primarily useful for determining elapsed time.

Syntax

DBMS_UTILITY.GET_TIME 
  RETURN NUMBER;

Return Values

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

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 104-23 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

Table 104-24 MAKE_DATA_BLOCK_ADDRESS Function Return Values

Returns	Description
`dba`	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 104-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, procedure or package).
`part2`	If this is non-`NULL`, then this is a procedure name within the package indicated by `part1`.
`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 later case, `part1_type` indicates a synonym.
`part1_type`	Type of `part1` is: 5 - synonym 7 - procedure (top level) 8 - function (top level) 9 - package If a synonym, then it means that `name` is a synonym that translates 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.
`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 104-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

Returns 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 104-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.`

Usage Notes

No errors are raised if the object does not exist or is already valid or is an object that cannot be validated.

104 DBMS_UTILITY

Using DBMS_UTILITY

Security Model

Types

dblink_array

index_table_type

instance_record

lname_array

name_array

number_array

uncl_array

Deprecated Subprograms

Summary of DBMS_UTILITY Subprograms

Table 104-1 DBMS_UTILITY Package Subprograms

ANALYZE_DATABASE Procedure

Syntax

Parameters

Table 104-2 ANALYZE_DATABASE Procedure Parameters

Exceptions

Table 104-3 ANALYZE_DATABASE Procedure Exceptions

Usage Notes

ACTIVE_INSTANCES Procedure

Syntax

Parameters

Table 104-4 ACTIVE_INSTANCES Procedure Parameters

ANALYZE_PART_OBJECT Procedure

Syntax

Parameters

Table 104-5 ANALYZE_PART_OBJECT Procedure Parameters

Usage Notes

ANALYZE_SCHEMA Procedure

Syntax

Parameters

Table 104-6 ANALYZE_SCHEMA Procedure Parameters

Exceptions

Table 104-7 ANALYZE_SCHEMA Procedure Exceptions

CANONICALIZE Procedure

Syntax

Parameters

Table 104-8 CANONICALIZE Procedure Parameters

Return Values

Usage Notes

Examples

COMMA_TO_TABLE Procedures

Syntax

Parameters

Table 104-9 COMMA_TO_TABLE Procedure Parameters

Return Values

Usage Notes

COMPILE_SCHEMA Procedure

Syntax

Parameters

Table 104-10 COMPILE_SCHEMA Procedure Parameters

Exceptions

Table 104-11 COMPILE_SCHEMA Procedure Exceptions

CREATE_ALTER_TYPE_ERROR_TABLE Procedure

Syntax

Parameters

Table 104-12 CREATE_ALTER_TYPE_ERROR_TABLE Procedure Parameters

Exceptions

CURRENT_INSTANCE Function

Syntax

DATA_BLOCK_ADDRESS_BLOCK Function

Syntax

Parameters

Table 104-13 DATA_BLOCK_ADDRESS_BLOCK Function Parameters

Pragmas

Return Values

Table 104-14 DATA_BLOCK_ADDRESS_BLOCK Function Return Values

Usage Notes

DATA_BLOCK_ADDRESS_FILE Function

Syntax

Parameters

Table 104-15 DATA_BLOCK_ADDRESS_FILE Function Parameters

Pragmas

Return Values

Table 104-16 DATA_BLOCK_ADDRESS_FILE Function Return Values

Usage Notes

DB_VERSION Procedure

Syntax

104
DBMS_UTILITY