| Oracle® TimesTen In-Memory Database PL/SQL Packages Reference Release 11.2.1 Part Number E14000-01 |
|
|
View PDF |
| Oracle® TimesTen In-Memory Database PL/SQL Packages Reference Release 11.2.1 Part Number E14000-01 |
|
|
View PDF |
The DBMS_UTILITY package provides various utility subprograms.
This chapter contains the following topics:
Security model
Constants
Types
Exceptions
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 DBMS_UTILITY package uses the constants shown in Table 7-1.
Table 7-1 DBMS_UTILITY constants
| Name | Type | Value | Description |
|---|---|---|---|
|
INV_ERROR_ON_RESTRICTIONS |
BINARY_INTEGER |
|
This constant is the only legal value for the |
Note:
Notes on data types: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.
TYPE dblink_array IS TABLE OF VARCHAR2(128) INDEX BY BINARY_INTEGER;
Lists of database links should be stored here.
TYPE index_table_type IS TABLE OF BINARY_INTEGER INDEX BY BINARY_INTEGER;
The order in which objects should be generated is returned here.
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.
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.
TYPE name_array IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
Lists of NAME should be stored here.
TYPE number_array IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
The order in which objects should be generated is returned here for users.
TYPE uncl_array IS TABLE OF VARCHAR2(227) INDEX BY BINARY_INTEGER;
Lists of "USER"."NAME"."COLUMN"@LINK should be stored here.
The following table lists the exceptions raised by DBMS_UTILITY.
Table 7-2 Exceptions Raised by DBMS_UTILITY
| Exception | Error Code | Description |
|---|---|---|
|
INV_NOT_EXIST_OR_NO_PRIV |
-24237 |
Raised by the INVALIDATE subprogram when the |
|
INV_MALFORMED_SETTINGS |
-24238 |
Raised by the INVALIDATE subprogram if a compiler setting is specified more than once in the |
|
INV_RESTRICTED_OBJECT |
-24239 |
Raised by the INVALIDATE subprogram when different combinations of conditions pertaining to the |
Table 7-3 DBMS_UTILITY Package Subprograms
| Subprogram | Description |
|---|---|
|
Canonicalizes a given string. |
|
|
Converts a comma-delimited list of names into a PL/SQL table of names. |
|
|
Compiles all procedures, functions, packages, and views in the specified schema. |
|
|
Returns version information for the database. Returns NULL for the compatibility setting because TimesTen does not support the system parameter |
|
|
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 hundredths of a second. |
|
|
Shows the dependencies on the object passed in. |
|
|
Returns the endianness of your database platform. |
|
|
Computes a hash value for the given string. |
|
|
Computes the hash value for a given string using the MD5 algorithm. |
|
|
Finds out the current time in hundredths of a second. |
|
|
Invalidates a database object and (optionally) modifies its PL/SQL compiler parameter settings. |
|
|
Returns bit setting. |
|
|
Resolves the given name of the form Do not use |
|
|
Calls the parser to parse the given name Do not use |
|
|
Converts a PL/SQL table of names into a comma-delimited list of names. |
|
|
Validates the object described either by owner, name, and namespace or by object ID. |
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 7-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
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.
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 dbms_utility.uncl_array); DBMS_UTILITY.COMMA_TO_TABLE ( list IN VARCHAR2, tablen OUT BINARY_INTEGER, tab OUT dbms_utility.lname_array);
Parameters
Table 7-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 overloading: And should have the following format for the second overloading: Where |
|
tablen |
Number of tables in the PL/SQL table. |
|
tab |
PL/SQL table which contains list of names. |
Return value
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.
The values in tab are copied from the original list, with no transformations.
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 7-6 COMPILE_SCHEMA procedure parameters
| Parameter | Description |
|---|---|
|
schema |
Name of the schema. |
|
compile_all |
If TRUE, will compile everything within the schema regardless of whether it is VALID. If FALSE, will compile only INVALID objects. |
|
reuse_settings |
Indicates whether the session settings in the objects should be reused, or whether the current session settings should be adopted instead. |
Exceptions
Table 7-7 COMPILE_SCHEMA procedure exceptions
| Exception | Description |
|---|---|
|
ORA-20000 |
Insufficient privileges for some object in this schema. |
|
ORA-20001 |
Cannot recompile |
|
ORA-20002 |
Maximum iterations exceeded. Some objects may not have been recompiled. |
Usage notes
Note that this subprogram is a wrapper for RECOMP_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 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;
This procedure returns version information for the database.
Returns NULL for the compatibility setting because TimesTen does not support the system parameter COMPATIBLE.
Syntax
DBMS_UTILITY.DB_VERSION ( version OUT VARCHAR2, compatibility OUT VARCHAR2);
Parameters
Table 7-8 DB_VERSION procedure parameters
| Parameter | Description |
|---|---|
|
version |
A string which represents the internal software version of the database (for example, 7.1.0.0.0). The length of this string is variable and is determined by the database version. |
|
compatibility |
The compatibility setting of the database. In TimesTen, DB_VERSION returns NULL for the compatibility setting because TimesTen does not support the system parameter |
This function formats the current call stack. This can be used on any stored procedure 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 value
This returns the call stack, up to 2000 bytes.
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. A NULL string is returned if no error is currently being handled.
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;
/
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.
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 value
This returns the error stack, up to 2000 bytes. A NULL string is returned if no error is currently being handled.
This function returns the current CPU time in hundredths of a second. The returned CPU time is the number of hundredths of a second from some arbitrary epoch.
Syntax
DBMS_UTILITY.GET_CPU_TIME RETURN NUMBER;
Return value
Time is the number of hundredths of a second from some arbitrary epoch.
Usage notes
This subprogram reports cycles (CPU time) used in performing work, and is unrelated to clock time or any other fixed reference. Since the base line is rubricator, and the relationship between work performed and the number generated is operating system specific, the amount of work performed is calculated by measuring the difference between a start point and end point for a particular operation.
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 7-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 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 endianness of the database platform.
Syntax
DBMS_UTILITY.GET_ENDIANNESS RETURN NUMBER;
Return value
The endianness of the database platform.
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 7-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. |
Pragmas
pragma restrict_references(get_hash_value, WNDS, RNDS, WNPS, RNPS);
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 the hash_size parameter works best.
This function computes a hash value for the given string using MD5 algorithm.
Syntax
DBMS_UTILITY.GET_SQL_HASH ( name IN VARCHAR2, hash OUT RAW, pre10ihash OUT NUMBER) RETURN NUMBER;
Pragmas
pragma restrict_references(GET_SQL_HASH, WNDS, RNDS, WNPS, RNPS);
Parameters
Table 7-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-Oracle 10g database version hash value. |
Return value
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-10g hash value of 4 bytes in the pre10ihash optional parameter.
This function determines the current time in hundredths of a second and is primarily used for determining elapsed time. The subprogram is called twice, at the beginning and end of a process. The first (earlier) number is subtracted from the second (later) number to determine the time elapsed.
Syntax
DBMS_UTILITY.GET_TIME RETURN NUMBER;
Return value
Time is the number of hundredths of a second from the point in time at which the subprogram is invoked.
Usage notes
Numbers are returned in the range -2147483648 to 2147483647 depending on platform and system, 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 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 7-12 INVALIDATE procedure parameters
| Parameter | Description |
|---|---|
|
p_object_id |
ID number of object to be invalidated. This is the same as the value of the |
|
p_plsql_object_settings |
This optional parameter is ignored if the object specified by |
|
p_option_flags |
This parameter is optional and defaults to zero (no flags). Option flags supported by invalidate.
|
Exceptions
Table 7-13 INVALIDATE exceptions
| Exception | Description |
|---|---|
|
INV_NOT_EXIST_OR_NO_PRIV |
Raised when the |
|
INV_MALFORMED_SETTINGS |
Raised if a compiler setting is specified more than once in the |
|
INV_RESTRICTED_OBJECT |
Raised when different combinations of conditions pertaining to the |
Usage notes
The object type (object_type column from ALL_OBJECTS) of the object specified by p_object_id must be a PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY, 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 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.
This example invalidates one of the procedures 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 will mark the P5 procedure invalid and set its PLSQL_OPTIMIZE_LEVEL compiler setting to 2. The values of other compiler settings will 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.
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;
Pragmas
pragma restrict_references (IS_BIT_SET, WNDS, RNDS, WNPS, RNPS);
Parameters
Table 7-14 IS_BIT_SET procedure parameters
| Parameter | Description |
|---|---|
|
r |
Source raw. |
|
n |
Which bit in |
Return value
This function returns 1 if bit n in RAW r is set. Bits are numbered high to low with the lowest bit being bit number 1.
This procedure resolves the given name of the form [[a.]b.]c[@d], where a, b, and c are SQL identifiers and d is a dblink, including synonym translation and authorization checking as necessary.
Do not use @d. 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 7-15 NAME_RESOLVE procedure parameters
| Parameter | Description |
|---|---|
|
name |
Name of the object. This can be of the form The |
|
context |
Must be an integer between 0 and 9.
|
|
schema |
Schema of the object: |
|
part1 |
First part of the name. The type of this name is specified |
|
part2 |
If this is non-NULL, then this is a subprogram name. If |
|
dblink |
If this is non-NULL, then a database link was specified as part of TimesTen does not support database links. |
|
part1_type |
Type of
|
|
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.
This procedure calls the parser to parse the given 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
For each of a, b, c, and dblink, tell where the following token starts in anext, bnext, cnext, dnext respectively.
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 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 7-16 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-delimited list of tables. |
Return value
A comma-delimited list and the number of elements found in the table.
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 7-17 VALIDATE procedure parameters
| Parameter | Description |
|---|---|
|
owner |
Name of the user who owns the object. Same as the |
|
objname |
Name of the object to be validated. Same as the |
|
namespace |
Namespace of the object. Same as the namespace field in
|
|
edition_name |
Reserved for future use. |
Usage notes
Executing VALIDATE on a subprogram will also validate subprograms that it references. (See the example below.)
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 actually 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 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, will also validate 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.
|
 Copyright © 1996, 2009, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|