The DBMS_UTILITY package provides various utility subprograms.
This chapter contains the following topics:
Security Model
Constants
Exceptions
Record Types
Table Types
DBMS_UTILITY runs with the privileges of the calling user for the NAME_RESOLVE Procedure and the COMPILE_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.
The DBMS_UTILITY package uses the constants shown in Table 157-1, "DBMS_UTILITY Constants".
The following table lists the exceptions raised by DBMS_UTILITY.
Table 157-2 Exceptions Raised by DBMS_UTILITY
| Exception | Error Code | Description |
|---|---|---|
|
|
-24237 |
Raised by the |
|
|
-24238 |
Raised by the |
|
|
-24239 |
Raised by the |
The DBMS_UTILITY package defines a single RECORD type and TABLE types.
This type describes the order in which generated objects are returnedto a user.
This type stores lists of LONG NAME including fully qualified attribute names.
Table 157-4 DBMS_UTILITY Package Subprograms
| Subprogram | Description |
|---|---|
|
Returns the active instance |
|
|
Analyzes all the tables, clusters and indexes in a database |
|
|
Analyzes the given tables and indexes |
|
|
Analyzes all the tables, clusters and indexes in a schema |
|
|
Canonicalizes a given string |
|
|
Converts a comma-delimited list of names into a PL/SQL table of names |
|
|
Compiles all procedures, functions, packages, views and triggers in the specified schema |
|
|
Creates an error table to be used in the |
|
|
Returns the current connected instance number |
|
|
Gets the block number part of a data block address |
|
|
Gets the file number part of a data block address |
|
|
Returns version information for the database |
|
|
Executes the DDL statement in |
|
|
Formats the current call stack |
|
|
Formats the backtrace from the point of the current error to the exception handler where the error has been caught |
|
|
Formats the current error stack |
|
|
Returns the current CPU time in 100th's of a second |
|
|
Shows the dependencies on the object passed in. |
|
|
Gets the endianness of the database platform |
|
|
Computes a hash value for the given string |
|
|
Gets the value of specified init.ora parameter |
|
|
Computes a hash value for the given string using MD5 algorithm |
|
|
Returns the current time in 100th's of a second |
|
|
Returns timezeone transitions by |
|
|
Invalidates a database object and (optionally) modifies its PL/SQL compiler parameter settings |
|
|
Checks the bit setting for the given bit in the given |
|
|
Checks if the database is running in cluster database mode |
|
|
Creates a data block address given a file number and a block number |
|
|
Resolves the given name |
|
|
Calls the parser to parse the given name |
|
|
Returns the session value from |
|
|
Returns the session value from |
|
|
Returns a string that uniquely identifies the version of Oracle and the operating system |
|
|
Converts a SQL ID into a hash value |
|
|
Converts a PL/SQL table of names into a comma-delimited list of names |
|
|
Makes invalid database objects valid |
|
|
Waits until all transactions (other than the caller's own) that have locks on the listed tables and began prior to the specified SCN have either committed or been rolled back |
This procedure returns the active instance.
DBMS_UTILITY.ACTIVE_INSTANCES ( instance_table OUT INSTANCE_TABLE, instance_count OUT NUMBER);
This procedure analyzes all the tables, clusters and indexes in a database.
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);
Table 157-6 ANALYZE_DATABASE Procedure Parameters
| Parameter | Description |
|---|---|
|
|
One of |
|
|
Number of rows to estimate |
|
|
Percentage of rows to estimate. If |
|
|
Method options of the following format: [ [ [ |
This procedure is equivalent to SQL:
"ANALYZE TABLE|INDEX [<schema>.]<object_name> PARTITION <pname> [<command_type>] [<command_opt>] [<sample_clause>]
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 ');
Table 157-7 ANALYZE_PART_OBJECT Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Schema of the |
|
|
Name of object to be analyzed, must be partitioned |
|
|
Type of object, must be |
|
|
Must be |
|
|
Other options for the command type. For |
|
|
Sample clause to use when |
This procedure analyzes all the tables, clusters and indexes in a schema.
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);
Table 157-8 ANALYZE_SCHEMA Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Name of the schema |
|
|
One of |
|
|
Number of rows to estimate |
|
|
Percentage of rows to estimate. If |
|
|
Method options of the following format: [ [ [ |
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.
DBMS_UTILITY.CANONICALIZE( name IN VARCHAR2, canon_name OUT VARCHAR2, canon_len IN BINARY_INTEGER);
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.'
These procedures converts a comma-delimited list of names into a PL/SQL table of names. The second version supports fully-qualified attribute names.
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);
Table 157-10 COMMA_TO_TABLE Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Comma separated list of list of 'names', where a name should have the following format for the first overloading: and the following format for the second overloading: where |
|
|
Number of tables in the PL/SQL table |
|
|
PL/SQL table which contains list of names |
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.
The values in tab are copied from the original list, with no transformations.
The procedure fails if the string between separators is longer than 30 bytes.
This procedure compiles all procedures, functions, packages, views and triggers in the specified schema.
DBMS_UTILITY.COMPILE_SCHEMA ( schema IN VARCHAR2, compile_all IN BOOLEAN DEFAULT TRUE, reuse_settings IN BOOLEAN DEFAULT FALSE);
Table 157-11 COMPILE_SCHEMA Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Name of the schema |
|
|
If If |
|
|
Indicates whether the session settings in the objects should be reused, or whether the current session settings should be adopted instead |
Note that this subprogram is a wrapper for theRECOMP_SERIAL Procedure included with the UTL_RECOMP package.
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>
This procedure creates an error table to be used in the EXCEPTION clause of the ALTER TYPE statement.
DBMS_UTILITY.CREATE_ALTER_TYPE_ERROR_TABLE( schema_name IN VARCHAR2, table_name IN VARCHAR2);
This function returns the current connected instance number. It returns NULL when connected instance is down.
This procedure returns version information for the database.
Table 157-16 DB_VERSION Procedure Parameters
| Parameter | Description |
|---|---|
|
|
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. |
|
|
The compatibility setting of the database determined by the "compatible" If the parameter is not specified in the |
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.
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.
The backtrace string. A NULL string is returned if no error is currently being handled.
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.
*/
This function formats the current error stack. This can be used in exception handlers to look at the full error stack.
This function returns a measure of current CPU processing time in hundredths of a second. The difference between the times returned from two calls measures the CPU processing time (not the total elapsed time) between those two points.
This procedure shows the dependencies on the object passed in.
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.
This function gets the value of specified init.ora parameter.
DBMS_UTILITY.GET_PARAMETER_VALUE ( parnam IN VARCHAR2, intval IN OUT BINARY_INTEGER, strval IN OUT VARCHAR2, listno IN BINARY_INTEGER DEFAULT 1) RETURN BINARY_INTEGER;
Table 157-20 GET_PARAMETER_VALUE Function Parameters
| Parameter | Description |
|---|---|
|
|
Parameter name |
|
|
Value of an integer parameter or the value length of a string parameter |
|
|
Value of a string parameter |
|
|
List item number. If retrieving parameter values for a parameter that can be specified multiple times to accumulate values, use this parameter to get each individual parameter. |
Parameter type:
0 if parameter is an INTEGER/BOOLEAN parameter
1 if parameter is a string/file parameter
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
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;
This function computes a hash value for the given string using MD5 algorithm.
Dbms_utility.get_sql_hash ( name IN VARCHAR2, hash OUT RAW, pre10ihash OUT NUMBER) RETURN NUMBER;
A hash value (last 4 bytes) based on the input string. the MD5 hash algorithm computes a 16 byte hash value, but we only return the last 4 bytes so that we can return an actual number. one could use an optional raw parameter to get all 16 bytes and to store the pre 10i hash value of 4 bytes in the pre10i hash optional parameter.
This function returns 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.
Time is the number of 100th's of a second from the point in time at which the subprogram is invoked.
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.
This proceedure returns timezeone transitions by regionid from the timezone.dat file.
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.
DBMS_UTILITY.INVALIDATE ( p_object_id NUMBER, p_plsql_object_settings VARCHAR2 DEFAULT NULL, p_option_flags PLS_INTEGER DEFAULT 0);
Table 157-24 INVALIDATE Procedure Parameters
| Parameter | Description |
|---|---|
|
|
ID number of object to be invalidated. This is the same as the value of the |
|
|
Optional parameter that ignored if the object specified by |
|
|
Optional parameter defaults to zero (no flags). Option flags supported by invalidate.
|
Table 157-25 INVALIDATE Procedure Exceptions
| Exception | Description |
|---|---|
|
|
Raised when the |
|
|
Raised if a compiler setting is specified more than once in the |
|
|
Raised when different combinations of conditions pertaining to the |
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.
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.
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.
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.
This function finds out if this database is running in cluster database mode.
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.
This procedure resolves the given name, including synonym translation and authorization checking as necessary.
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);
Table 157-28 NAME_RESOLVE Procedure Parameters
| Parameter | Description |
|---|---|
|
|
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 a, b and c may be delimited identifiers, and may contain Globalization Support (NLS) characters (single and multibyte). |
|
|
Must be an integer between 0 and 9.
|
|
|
Schema of the object: c. If no schema is specified in |
|
|
First part of the name. The type of this name is specified |
|
|
If this is non- |
|
|
If this is non- |
|
|
Type of
|
|
|
Object identifier |
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.
DBMS_UTILITY.NAME_TOKENIZE ( name IN VARCHAR2, a OUT VARCHAR2, b OUT VARCHAR2, c OUT VARCHAR2, dblink OUT VARCHAR2, nextpos OUT BINARY_INTEGER);
Table 157-29 NAME_RESOLVE Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Input name, consisting of SQL identifiers (for example, scott.foo@dblink) |
|
|
Output for the first token of the name |
|
|
Output for the second token of the name (if applicable) |
|
|
Output for the third token of the name (if applicable) |
|
|
Output for the dblink of the name |
|
|
Next position after parsing the input name |
This function returns the session value from sys_context ('userenv', 'current_schema').
This function returns the session value from sys_context ('userenv', 'current_user').
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.
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.
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);
This procedure makes invalid database objects valid.
DBMS_UTILITY.VALIDATE(
object_id NUMBER);
DBMS_UTILITY.VALIDATE(
owner VARCHAR2,
objname VARCHAR2,
namespace NUMBER, edition_name := SYS_CONTEXT ('USERENV', 'CURRENT_EDITION'));
Table 157-32 VALIDATE Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Name of the user who owns the object. Same as the |
|
|
Name of the object to be validated. Same as the |
|
|
Namespace of the object. Same as the namespace field in
|
|
|
[Note: Currently not operable. Reserved for future use] |
No errors are raised if the object does not exist or is already valid or is an object that cannot be validated.
If the object being validated is not actual in the specified edition, the subprogram automatically switches into the edition in which the object is actual prior to validation. That is, a call to VALIDATE will not actualize the object in the specified edition.
The INVALIDATE Procedure invalidates a database object and optionally changes its PL/SQL compiler parameter settings. The object to be invalidated is specified by its object_id. The subprogram automatically switches to the edition in which the object is actual prior to invalidation. That is, a call to INVALIDATE will not actualize the object in the current edition.
This procedure waits until all transactions (other than the caller's own) that have locks on the listed tables and began prior to the specified scn have either committed or been rolled back.
DBMS_UTILITY.WAIT_ON_PENDING_DML (
tables IN VARCHAR2,
timeout IN BINARY_INTEGER,
scn IN OUT NUMBER)
RETURN BOOLEAN;
Table 157-33 WAIT_ON_PENDING_DML Function Parameters
| Parameter | Description |
|---|---|
|
|
Comma-separated list of one or more table names. The list must be valid for COMMA_TO_TABLE Procedures, and each item valid to the NAME_RESOLVE Procedure. Neither column specifiers nor DBLINK (database link) specifiers are allowed in the names, and each name must resolve to an existing table in the local database. |
|
|
Maximum number of seconds to wait, totalled across all tables/transactions. A |
|
|
SCN prior to which transactions must have begun to be considered relevant to this request. If the value is |