Oracle PL/SQL Programming: Covers Versions Through Oracle Database 11g Release 2 (Animal Guide)

The DBMS_UTILITY package includes several utility modules you might find useful when managing objects in the database.

C.16.1 The ANALYZE_SCHEMA procedure

This procedure analyzes all the tables, clusters, and indexes in the specified schema. The specification is:

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

C.16.2 The COMMA_TO_TABLE procedure

The COMMA_TO_TABLE procedure parses a comma-delimited list and places each name into a PL/SQL table. The specification is:

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

C.16.3 The COMPILE_SCHEMA procedure

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

PROCEDURE DBMS_UTILITY.COMPILE_SCHEMA (schema VARCHAR2);

C.16.4 The FORMAT_CALL_STACK function

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

FUNCTION DBMS_UTILITY.FORMAT_CALL_STACK RETURN VARCHAR2;

C.16.5 The FORMAT_ERROR_STACK function

This function formats and returns the current error stack. You might use this in an exception handler to examine the sequence of errors raised. The specification is:

FUNCTION DBMS_UTILITY.FORMAT_ERROR_STACK RETURN VARCHAR2;

C.16.6 The GET_TIME function

This function returns the number of 100ths of seconds which have elapsed from an arbitrary time. Without GET_TIME, Oracle functions can only record and provide elapsed time in second intervals, which is a very coarse granularity in today's world of computing. With GET_TIME, you can get a much finer understanding of the processing times of lines in your program. The specification is:

FUNCTION DBMS_UTILITY.GET_TIME RETURN NUMBER;

C.16.7 The IS_PARALLEL_SERVER function

This function helps determine if the database is running in Parallel Server mode. The specification is:

FUNCTION DBMS_UTILITY.IS_PARALLEL_SERVER RETURN BOOLEAN;

The function returns TRUE if the database is running in Parallel Server mode; otherwise it returns FALSE.

C.16.8 The NAME_RESOLVE procedure

This procedure resolves the name of an object into its component parts, performing synonym translations as necessary. The specification is:

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

C.16.9 The NAME_TOKENIZE procedure

The NAME_TOKENIZE procedure calls the PL/SQL parser to parse the given name that is in the following format:

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

where dblink is the name of a database link. NAME_TOKENIZE follows these rules:

• Strips off all double quotes

• Converts to uppercase if there are no quotes

• Ignores any inline comments

• Does no semantic analysis

• Leaves any missing values as NULL

The specification is:

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

C.16.10 The PORT_STRING function

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

FUNCTION DBMS_UTILITY.PORT_STRING RETURN VARCHAR2;

C.16.11 The TABLE_TO_COMMA procedure

The TABLE_TO_COMMA procedure converts a PL/SQL table into a comma-delimited list. The specification is:

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

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