8 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 and the COMPILE_SCHEMA procedure. This is necessary so that the SQL works correctly.

The package does not run as SYS.

Constants

The DBMS_UTILITY package uses the constants shown in Table 8-1.

Table 8-1 DBMS_UTILITY constants

Name Type Value Description

INV_ERROR_ON_RESTRICTIONS

BINARY_INTEGER

1

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


Notes:

  • The PLS_INTEGER and BINARY_INTEGER data types are identical. This document uses BINARY_INTEGER to indicate data types in reference information (such as for table types, record types, subprogram parameters, or subprogram return values), but may use either in discussion and examples.

  • The INTEGER and NUMBER(38) data types are also identical. This document uses INTEGER throughout.

Data types

dblink_array

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

Lists of database links would be stored here. (TimesTen does not support dblinks.)

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

uncl_array

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

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

Exceptions

The following table lists the exceptions raised by DBMS_UTILITY.

Table 8-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 privilege 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 8-3 DBMS_UTILITY Package Subprograms

Subprogram Description

CANONICALIZE procedure

Canonicalizes a given string.

COMMA_TO_TABLE procedure

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

COMPILE_SCHEMA procedure

Compiles all procedures, functions, packages, and views in the specified schema.

DB_VERSION procedure

Returns version information for the database.

Returns NULL for the compatibility setting because TimesTen does not support the system parameter COMPATIBLE.

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 hundredths of a second.

GET_DEPENDENCY procedure

Shows the dependencies on the object passed in.

GET_ENDIANNESS function

Returns the endianness of your database platform.

GET_HASH_VALUE function

Computes a hash value for the given string.

GET_SQL_HASH function

Computes the hash value for a given string using the MD5 algorithm.

GET_TIME function

Finds out the current time in hundredths of a second.

INVALIDATE procedure

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

IS_BIT_SET function

Checks the setting of a specified bit in a RAW value.

NAME_RESOLVE procedure

Resolves the given name of the form:

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

Where a, b, and c are SQL identifiers and dblink is a dblink.

Important: Do not use @dblink. TimesTen does not support database links.

NAME_TOKENIZE procedure

Calls the parser to parse the given name:

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

Where a, b, and c are SQL identifiers and dblink is a dblink. Strips double quotes or converts to uppercase if there are no quotes. Ignores comments and does not perform semantic analysis. Missing values are NULL.

Important: Do not use @dblink. TimesTen does not support database links.

TABLE_TO_COMMA procedure

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

VALIDATE procedure

Validates the object described either by owner, name, and namespace or by object ID.


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. For example, " table" becomes TABLE.

Syntax

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

Parameters

Table 8-4 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 value

The first canon_len bytes in canon_name

Usage notes

  • If the name value is NULL, the canon_name value becomes NULL.

  • If name is a dotted name (such as a."b".c), then for each component in the dotted name where the component begins and ends with a double quote, no transformation is performed on that 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 is concatenated in the input position, separated by ".".

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

  • Any other character after a[.b]* is 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 procedure

This procedure 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 dbms_utility.uncl_array); 

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

Parameters

Table 8-5 COMMA_TO_TABLE procedure parameters

Parameter Description

list

Comma-delimited list of names, where a name should have the following format for the first version of the procedure:

a[.b[.c]][@d]

Or the following format for the second version of the procedure:

a[.b]*

Where a, b, c, and d are simple identifiers (quoted or unquoted).

tablen

Number of tables in the PL/SQL table

tab

PL/SQL table that contains list of names


Return value

A PL/SQL table 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.

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

COMPILE_SCHEMA procedure

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

Syntax

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

Parameters

Table 8-6 COMPILE_SCHEMA procedure parameters

Parameter Description

schema

Name of the schema

compile_all

TRUE to compile everything within the schema regardless of whether status is VALID

FALSE to compile only objects with status INVALID

reuse_settings

Flag to specify whether the session settings in the objects should be reused, or the current session settings should be adopted instead


Usage notes

  • Note that this subprogram is a wrapper for the RECOMP_SERIAL procedure included with the UTL_RECOMP package.

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

  • To see the errors associated with invalid objects, you can use the ttIsql show errors command:

    Command> show errors [{FUNCTION | PROCEDURE | PACKAGE | PACKAGE BODY}
    [schema.]name];
    

    Examples:

    Command> show errors function foo;
    Command> show errors procedure fred.bar;
    Command> show errors package body emp_actions;
    

Exceptions

Table 8-7 COMPILE_SCHEMA procedure exceptions

Exception Description

ORA-20000

Raised for insufficient privileges for some object in this schema.

ORA-20001

Raised if SYS objects cannot be compiled.

ORA-20002

Raised if maximum iterations exceeded. Some objects may not have been recompiled.


DB_VERSION procedure

This procedure returns version information for the database.

Returns NULL for the compatibility setting because TimesTen does not support the system parameter COMPATIBLE.

Also see Chapter 9, "TT_DB_VERSION".

Syntax

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

Parameters

Table 8-8 DB_VERSION procedure parameters

Parameter Description

version

String that represents the internal software version of the database (for example, 11.2.2.1.0)

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

compatibility

Compatibility setting of the database

In TimesTen, DB_VERSION returns NULL for the compatibility setting because TimesTen does not support the system parameter COMPATIBLE.


FORMAT_CALL_STACK function

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

Syntax

DBMS_UTILITY.FORMAT_CALL_STACK 
  RETURN VARCHAR2;

Return value

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 value

The backtrace string (or a null string if no error is currently being handled)

Examples

Script format_error_backtrace.sql:

Execute the following script from ttIsql, using the run command.

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;
/

-- Define and raise an exception to view backtrace. 
-- See "EXCEPTION_INIT Pragma" in Oracle Database PL/SQL Language Reference.

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

Execute Top_Naive:

This shows the results of executing the Top_Naive procedure that is created in the script, assuming user SCOTT ran the script and executed the procedure.

Command> set serveroutput on
Command> begin
       > Top_Naive();
       > end;
       > /
 8507: ORA-01476: divisor is equal to zero
 8507: ORA-06512: at "SCOTT.P0", line 4
 8507: ORA-06512: at "SCOTT.P1", line 3
 8507: ORA-06512: at "SCOTT.P2", line 3
 8507: ORA-06512: at "SCOTT.P3", line 3
 8507: ORA-06512: at "SCOTT.P4", line 3
 8507: ORA-06512: at "SCOTT.P5", line 3
 8507: ORA-06512: at "SCOTT.TOP_NAIVE", line 3
 8507: ORA-06512: at line 2
The command failed.

This output shows the call stack at the point where an exception was raised. It shows the backtrace error message as the call stack unwound, starting at the unhandled exception ORA-01476 raised at SCOTT.P0 line 4, back to SCOTT.Top_Naive line 3.

Execute Top_With_Logging:

This shows the results of executing the Top_With_Logging() procedure that is created in the script, assuming user SCOTT ran the script and executed the procedure.

Command> begin
       > Top_With_Logging();
       > end;
       > /
Error_Stack...
ORA-01476: divisor is equal to zero
Error_Backtrace...
ORA-06512: at "SCOTT.P0", line 4
ORA-06512: at "SCOTT.P1", line 3
ORA-06512: at "SCOTT.P2", line 3
ORA-06512: at "SCOTT.P3", line 3
ORA-06512: at "SCOTT.P4", line 3
ORA-06512: at "SCOTT.P5", line 3
ORA-06512: at "SCOTT.TOP_WITH_LOGGING", line 6
----------
 
PL/SQL procedure successfully completed.

This output shows the call stack at the point where an exception was raised. It shows the backtrace error message as the call stack unwound, starting at the unhandled exception ORA-01476 raised at SCOTT.P0 line 4, back to SCOTT.Top_With_Logging line 6.

ORA-06512 information:

Oracle Database Error Messages provides the following information about the ORA-06512 error:

ORA-06512: at stringline string
    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
 DBA.

FORMAT_ERROR_STACK function

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

Syntax

DBMS_UTILITY.FORMAT_ERROR_STACK 
  RETURN VARCHAR2;

Return value

The error stack, up to 2000 bytes (or a null string if no error is currently being handled)

GET_CPU_TIME function

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.

Also see the GET_TIME function, which has a different intent.

Syntax

  DBMS_UTILITY.GET_CPU_TIME
   RETURN NUMBER;

Return value

The number of hundredths of a second of CPU processing time from some arbitrary point

Usage notes

This subprogram reports cycles (CPU time) used in performing work and is unrelated to clock time or any other fixed reference. It always returns a positive value. The amount of work performed is calculated by measuring the difference between a start point and end point for a particular operation, using a GET_CPU_TIME call at each point.

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 8-9 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_OUTPUT package to display results, so you must declare SET SERVEROUTPUT ON from ttIsql to view dependencies. Alternatively, any application that checks the DBMS_OUTPUT output buffers can invoke this subprogram and then retrieve the output through DBMS_OUTPUT subprograms such as GET_LINES.

GET_ENDIANNESS function

This function indicates the endianness of the database platform.

Syntax

  DBMS_UTILITY.GET_ENDIANNESS
   RETURN NUMBER;

Return value

A NUMBER value indicating the endianness of the database platform: 1 for big-endian or 2 for little-endian

GET_HASH_VALUE function

This function computes a hash value for the given string.

Syntax

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

Parameters

Table 8-10 GET_HASH_VALUE function parameters

Parameter Description

name

String to be hashed

base

Base value where the returned hash value is to start

hash_size

Desired size of the hash table


Return value

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 hash_size works best.

GET_SQL_HASH function

This function computes a hash value for the given string using the MD5 algorithm.

Syntax

DBMS_UTILITY.GET_SQL_HASH (
   name          IN   VARCHAR2
  [,hash          OUT  RAW,
   pre10ihash    OUT  NUMBER])
  RETURN NUMBER;

Parameters

Table 8-11 GET_SQL_HASH procedure parameters

Parameter Description

name

String to be hashed

hash

An optional field to store all 16 bytes of returned hash value

pre10ihash

An optional field to store a pre-10g Oracle Database version hash value


Return value

A hash value (last four bytes) based on the input string

The MD5 hash algorithm computes a 16-byte hash value, but TimesTen returns only the last four bytes to return an actual number. One could use an optional RAW parameter to get all 16 bytes and to store the pre-10g Oracle Database hash value of four bytes in the pre10ihash optional parameter.

GET_TIME function

This function returns a measure of current time in hundredths of a second. The difference between the times returned from two calls measures the total elapsed time (not just CPU processing time) between those two points.

Also see the GET_CPU_TIME function, which has a different intent.

Syntax

DBMS_UTILITY.GET_TIME 
  RETURN NUMBER;

Return value

The number of hundredths of a second from the time at which the subprogram is invoked

Usage notes

Numbers are returned in the range -2,147,483,648 to 2,147,483,647 depending on platform and system, and your application must take the sign of the number into account in determining the interval. For example, for two negative numbers, application logic must allow for the first (earlier) number to be larger than the second (later) number that is closer to zero. By the same token, your application should also allow for the first (earlier) number to be negative and the second (later) number to 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              IN  NUMBER
  [,p_plsql_object_settings  IN  VARCHAR2 DEFAULT NULL,
   p_option_flags           BINARY_INTEGER DEFAULT 0]);

Parameters

Table 8-12 INVALIDATE procedure parameters

Parameter Description

p_object_id

ID number of the object to be invalidated

This equals the value of the OBJECT_ID column from ALL_OBJECTS. If the p_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 privilege on the object being invalidated, otherwise the inv_not_exist_or_no_priv exception is raised.

p_plsql_object_settings

Optional parameter that is ignored if the object specified by p_object_id is not a PL/SQL object

If no value is specified for this parameter, the PL/SQL compiler settings are left unchanged, 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. If a setting is specified more than once, the inv_malformed_settings exception is 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, TimesTen uses that value. If a setting is omitted and REUSE SETTINGS was not specified or no value was specified for the parameter in an earlier compilation, then the database obtains the value for that setting from the session environment.

p_option_flags

Optional parameter that defaults to zero (no flags)

Only the inv_error_on_restrictions flag is supported (see "Constants"). With this flag, 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.


Usage notes

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

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

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

Exceptions

Table 8-13 INVALIDATE exceptions

Exception Description

INV_NOT_EXIST_OR_NO_PRIV

Raised when the object_id argument value is NULL or invalid, or when the caller does not have CREATE privilege 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.


Examples

This example invalidates a procedure created in the example in "FORMAT_ERROR_BACKTRACE function". From examining user_objects, you can see information for the procedures created in that example. The following describes user_objects then queries its contents.

Command> describe user_objects;
 
View SYS.USER_OBJECTS:
  Columns:
    OBJECT_NAME                     VARCHAR2 (30) INLINE
    SUBOBJECT_NAME                  VARCHAR2 (30) INLINE
    OBJECT_ID                       TT_BIGINT NOT NULL
    DATA_OBJECT_ID                  TT_BIGINT
    OBJECT_TYPE                     VARCHAR2 (17) INLINE NOT NULL
    CREATED                         DATE NOT NULL
    LAST_DDL_TIME                   DATE NOT NULL
    TIMESTAMP                       VARCHAR2 (78) INLINE NOT NULL
    STATUS                          VARCHAR2 (7) INLINE NOT NULL
    TEMPORARY                       VARCHAR2 (1) INLINE NOT NULL
    GENERATED                       VARCHAR2 (1) INLINE NOT NULL
    SECONDARY                       VARCHAR2 (1) INLINE NOT NULL
    NAMESPACE                       TT_INTEGER NOT NULL
    EDITION_NAME                    VARCHAR2 (30) INLINE
 
1 view found.
 
Command> select * from user_objects;
...
< LOG_ERRORS, <NULL>, 296, <NULL>, PROCEDURE, 2009-09-18 12:53:45, 2009-09-18 12
:58:22, 2009-09-18:12:58:22, VALID, N, N, N, 1, <NULL> >
< P0, <NULL>, 297, <NULL>, PROCEDURE, 2009-09-18 12:53:45, 2009-09-18 12:58:22,
2009-09-18:12:58:22, VALID, N, N, N, 1, <NULL> >
< P1, <NULL>, 298, <NULL>, PROCEDURE, 2009-09-18 12:53:45, 2009-09-18 12:58:22,
2009-09-18:12:58:22, VALID, N, N, N, 1, <NULL> >
< P2, <NULL>, 299, <NULL>, PROCEDURE, 2009-09-18 12:53:45, 2009-09-18 12:58:22,
2009-09-18:12:58:22, VALID, N, N, N, 1, <NULL> >
< P3, <NULL>, 300, <NULL>, PROCEDURE, 2009-09-18 12:53:45, 2009-09-18 12:58:22,
2009-09-18:12:58:22, VALID, N, N, N, 1, <NULL> >
< P4, <NULL>, 301, <NULL>, PROCEDURE, 2009-09-18 12:53:45, 2009-09-18 12:58:22,
2009-09-18:12:58:22, VALID, N, N, N, 1, <NULL> >
< P5, <NULL>, 302, <NULL>, PROCEDURE, 2009-09-18 12:53:45, 2009-09-18 12:58:22,
2009-09-18:12:58:22, VALID, N, N, N, 1, <NULL> >
< TOP_NAIVE, <NULL>, 303, <NULL>, PROCEDURE, 2009-09-18 12:53:45, 2009-09-18 12:
58:22, 2009-09-18:12:58:22, VALID, N, N, N, 1, <NULL> >
< TOP_WITH_LOGGING, <NULL>, 304, <NULL>, PROCEDURE, 2009-09-18 12:53:45, 2009-09
-18 15:19:16, 2009-09-18:15:19:16, VALID, N, N, N, 1, <NULL> >
...
20 rows found.

To invalidate the P5 procedure, for example, specify object_id 302 in the INVALIDATE call:

Command> begin
       > dbms_utility.invalidate(302, 'PLSQL_OPTIMIZE_LEVEL=2 REUSE SETTINGS');
       > end;
       > /

This marks the P5 procedure as invalid and sets its PLSQL_OPTIMIZE_LEVEL compiler setting to 2. The values of other compiler settings remain unchanged because REUSE SETTINGS is specified. Note that in addition to P5 being invalidated, any PL/SQL objects that refer to that object are invalidated. Given that Top_With_Logging and Top_Naive call P5, here are the results of the INVALIDATE call, querying for all user objects that are now invalid:

Command> select * from user_objects where status='INVALID';
< P5, <NULL>, 302, <NULL>, PROCEDURE, 2009-09-18 12:53:45, 2009-09-18 12:58:22,
2009-09-18:12:58:22, INVALID, N, N, N, 1, <NULL> >
< TOP_NAIVE, <NULL>, 303, <NULL>, PROCEDURE, 2009-09-18 12:53:45, 2009-09-18 12:
58:22, 2009-09-18:12:58:22, INVALID, N, N, N, 1, <NULL> >
< TOP_WITH_LOGGING, <NULL>, 304, <NULL>, PROCEDURE, 2009-09-18 12:53:45, 2009-09
-18 15:19:16, 2009-09-18:15:19:16, INVALID, N, N, N, 1, <NULL> >
3 rows found.

A user can explicitly recompile and revalidate an object by calling the VALIDATE procedure discussed later in this chapter, or by executing ALTER PROCEDURE, ALTER FUNCTION, or ALTER PACKAGE, as applicable, on the object. Alternatively, each object is recompiled and revalidated automatically the next time it is executed.

IS_BIT_SET function

This function checks the bit setting for the given bit in the given RAW value.

Syntax

DBMS_UTILITY.IS_BIT_SET (
   r       IN RAW,    
   n       IN NUMBER)  
  RETURN NUMBER;

Parameters

Table 8-14 IS_BIT_SET procedure parameters

Parameter Description

r

Source raw

n

Which bit in r to check


Return value

1 if bit n in RAW r is set, where bits are numbered high to low with the lowest bit being bit number 1

NAME_RESOLVE procedure

This procedure resolves the given name of the form:

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

Where a, b, and c are SQL identifiers and dblink is a dblink, including synonym translation and authorization checking as necessary.

Do not use @dblink. TimesTen does not support database links.

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 8-15 NAME_RESOLVE procedure parameters

Parameter Description

name

Name of the object

This can be of the form:

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

Where a, b, and c are SQL identifiers and dblink is a dblink. TimesTen does not support dblinks. No syntax checking is performed on the dblink. If a dblink is specified, or if the name resolves to something with a dblink, then the object is not resolved, but the schema, part1, part2, and dblink OUT parameters receive values.

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

context

An integer from 0 to 9, as follows:

  • 0 - Table

  • 1 - PL/SQL (for two-part names)

  • 2 - Sequences

  • 3 - Trigger (not applicable for TimesTen)

  • 4 - Java source (not applicable for TimesTen)

  • 5 - Java resource (not applicable for TimesTen)

  • 6 - Java class (not applicable for TimesTen)

  • 7 - Type (not applicable for TimesTen)

  • 8 - Java shared data (not applicable for TimesTen)

  • 9 - Index

schema

Schema of the object, c

If no schema is specified in name, then 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

Subprogram name, as applicable, or NULL.

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

dblink

Not applicable

TimesTen does not support database links.

part1_type

Type of part1, as follows:

  • 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 errors that are possible when specifying object names.

NAME_TOKENIZE procedure

This procedure calls the parser to parse the input name as:

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

Where a, b, and c are SQL identifiers and dblink is a 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.

Do not use @dblink. TimesTen does not support database links.

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

Table 8-16 NAME_TOKENIZE procedure parameters

Parameter Description

name

The input name, consisting of SQL identifiers (for example, scott.foo)

a

Output for the first token of the name

b

Output for the second token of the name (if applicable)

c

Output for the third token of the name (if applicable)

dblink

Output for the dblink of the name (not applicable for TimesTen)

nextpos

Next position after parsing the input name


Examples

Consider the following script to run in ttIsql:

declare
   a varchar2(30);
   b varchar2(30);
   c varchar2(30);
   d varchar2(30);
   next integer;

begin
   dbms_utility.name_tokenize('scott.foo', a, b, c, d, next);
   dbms_output.put_line('a: ' || a);
   dbms_output.put_line('b: ' || b);
   dbms_output.put_line('c: ' || c);
   dbms_output.put_line('d: ' || d);
   dbms_output.put_line('next: ' || next);
end;
/

This produces the following output.

a: SCOTT
b: FOO
c:
d:
next: 9
 
PL/SQL procedure successfully completed.

TABLE_TO_COMMA procedure

This procedure 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 being NULL. The second version supports fully qualified attribute names.

Syntax

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

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

Parameters

Table 8-17 TABLE_TO_COMMA procedure parameters

Parameter Description

tab

PL/SQL table that contains list of table names

tablen

Number of tables in the PL/SQL table

list

Comma-delimited list of tables


Return value

A VARCHAR2 value with a comma-delimited list and the number of elements found in the table

VALIDATE procedure

Validates the object described either by owner, name, and namespace or by object ID.

Syntax

DBMS_UTILITY.VALIDATE(
    object_id     IN  NUMBER);

DBMS_UTILITY.VALIDATE(
   owner          IN  VARCHAR2, 
   objname        IN  VARCHAR2, 
   namespace      NUMBER,
   edition_name   VARCHAR2 := NULL;

Parameters

Table 8-18 VALIDATE procedure parameters

Parameter Description

object_id

ID number of the object to be validated

See "INVALIDATE procedure" for information about object IDs.

owner

Name of the user who owns the object

Same as the OWNER field in ALL_OBJECTS.

objname

Name of the object to be validated

Same as the OBJECT_NAME field in ALL_OBJECTS.

namespace

Namespace of the object

Same as the namespace field in obj$. Equivalent numeric values are as follows:

  • 1 - Table or procedure

  • 2 - Body

  • 3 - Trigger (not applicable for TimesTen)

  • 4 - Index

  • 5 - Cluster

  • 9 - Directory

  • 10 - Queue

  • 11 - Replication object group

  • 12 - Replication propagator

  • 13 - Java source (not applicable for TimesTen)

  • 14 - Java resource (not applicable for TimesTen)

  • 58 - Model (data mining)

edition_name

Reserved for future use


Usage notes

  • Executing VALIDATE on a subprogram also validates subprograms that it references. (See the example below.)

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

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

Examples

This example starts where the INVALIDATE example in "INVALIDATE procedure" left off. Assume P5, Top_Naive, and Top_With_Logging are invalid, shown as follows:

Command> select * from user_objects where status='INVALID';
< P5, <NULL>, 302, <NULL>, PROCEDURE, 2009-09-18 12:53:45, 2009-09-18 12:58:22,
2009-09-18:12:58:22, INVALID, N, N, N, 1, <NULL> >
< TOP_NAIVE, <NULL>, 303, <NULL>, PROCEDURE, 2009-09-18 12:53:45, 2009-09-18 12:
58:22, 2009-09-18:12:58:22, INVALID, N, N, N, 1, <NULL> >
< TOP_WITH_LOGGING, <NULL>, 304, <NULL>, PROCEDURE, 2009-09-18 12:53:45, 2009-09
-18 15:19:16, 2009-09-18:15:19:16, INVALID, N, N, N, 1, <NULL> >
3 rows found.

Validating Top_With_Logging, for example, also validates P5, because it calls P5 (leaving only Top_Naive invalid):

Command> begin
       > dbms_utility.validate(304);
       > end;
       > /
 
PL/SQL procedure successfully completed.
 
Command> select * from user_objects where status='INVALID';
< TOP_NAIVE, <NULL>, 303, <NULL>, PROCEDURE, 2009-09-18 12:53:45, 2009-09-21 11:
14:37, 2009-09-21:11:14:37, INVALID, N, N, N, 1, <NULL> >
1 row found.