The DBMS_UTILITY
package provides various utility subprograms.
This chapter contains the following topics:
Security model
Constants
Data 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 8-1.
Table 8-1 DBMS_UTILITY constants
Name | Type | Value | Description |
---|---|---|---|
|
|
|
This constant is the only legal value for the |
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.
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.)
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, including fully qualified attribute names.
TYPE name_array IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
Lists of NAME
should be stored here.
The following table lists the exceptions raised by DBMS_UTILITY
.
Table 8-2 Exceptions Raised by DBMS_UTILITY
Exception | Error Code | Description |
---|---|---|
|
-24237 |
Raised by the |
|
-24238 |
Raised by the |
|
-24239 |
Raised by the |
Table 8-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 |
|
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. |
|
Checks the setting of a specified bit in a |
|
Resolves the given name of the form: [[a.]b.]c[@dblink] Where Important: Do not use |
|
Calls the parser to parse the given name: 'a[.b[.c]][@dblink]" Where Important: 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
.
DBMS_UTILITY.CANONICALIZE( name IN VARCHAR2, canon_name OUT VARCHAR2, canon_len IN BINARY_INTEGER);
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.'
This procedure 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 dbms_utility.uncl_array); DBMS_UTILITY.COMMA_TO_TABLE ( list IN VARCHAR2, tablen OUT BINARY_INTEGER, tab OUT dbms_utility.lname_array);
Table 8-5 COMMA_TO_TABLE procedure parameters
Parameter | Description |
---|---|
|
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 |
|
Number of tables in the PL/SQL table |
|
PL/SQL table that 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.
This procedure compiles all procedures, functions, packages, and views in the specified schema.
DBMS_UTILITY.COMPILE_SCHEMA ( schema IN VARCHAR2, compile_all IN BOOLEAN DEFAULT TRUE, reuse_settings IN BOOLEAN DEFAULT FALSE);
Table 8-6 COMPILE_SCHEMA procedure parameters
Parameter | Description |
---|---|
|
Name of the schema |
|
|
|
Flag to specify whether the session settings in the objects should be reused, or the current session settings should be adopted instead |
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;
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".
Table 8-8 DB_VERSION procedure parameters
Parameter | Description |
---|---|
|
String that represents the internal software version of the database (for example, 18.1.4.1.0) The length of this string is variable and is determined by the database version. |
|
Compatibility setting of the database In TimesTen, |
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.
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.
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.
This function formats the current error stack. It 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.
Also see the GET_TIME function, which has a different intent.
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.
This procedure shows the dependencies on the object passed in.
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
.
This function computes a hash value for the given string.
DBMS_UTILITY.GET_HASH_VALUE ( name IN VARCHAR2, base IN NUMBER, hash_size IN NUMBER) RETURN NUMBER;
This function computes a hash value for the given string using the MD5 algorithm.
DBMS_UTILITY.GET_SQL_HASH ( name IN VARCHAR2, hash OUT RAW, pre10ihash OUT NUMBER) RETURN NUMBER;
A hash value (last four bytes) based on the input string
The MD5 hash algorithm computes a 16-byte hash value, but TimesTen uses only the last four bytes to return a number. The hash
output parameter gets all 16 bytes.
This example displays the 16-byte hash value (ignoring both the four-byte returned hash value and the pre-10g hash value).
Command> declare ignore_hash_4b number := 0; ignore_pre10ihash number := 0; hash_16B RAW(16); query_text varchar2(255); begin query_text := 'SELECT * FROM dual'; -- Calculate the hash of the SQL text ignore_hash_4b := DBMS_UTILITY.GET_SQL_HASH( query_text, hash_16B, ignore_pre10ihash); dbms_output.put_line('>' || query_text || '< hash is ' || hash_16B); end; / >SELECT * FROM dual< hash is 462D200E640BC1CBBDFE01B36A231600 PL/SQL procedure successfully completed.
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.
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.
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 IN NUMBER [,p_plsql_object_settings IN VARCHAR2 DEFAULT NULL, p_option_flags BINARY_INTEGER DEFAULT 0]);
Table 8-12 INVALIDATE procedure parameters
Parameter | Description |
---|---|
|
ID number of the object to be invalidated This equals the value of the |
|
Optional parameter that is ignored if the object specified by If no value is specified for this parameter, the PL/SQL compiler settings are left unchanged, equivalent to |
|
Optional parameter that defaults to zero (no flags) Only the |
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.
Table 8-13 INVALIDATE 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 |
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.
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 dblinks.
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 8-15 NAME_RESOLVE procedure parameters
Parameter | Description |
---|---|
|
Name of the object This can be of the form: [[a.]b.]c[@dblink] Where The |
|
An integer from 0 to 9, as follows:
|
|
Schema of the object, If no schema is specified in |
|
First part of the name The type of this name is specified |
|
Subprogram name, as applicable, or If |
|
Not applicable TimesTen does not support dblinks. |
|
Type of
|
|
Object identifier |
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 dblinks.
DBMS_UTILITY.NAME_TOKENIZE ( name IN VARCHAR2, a OUT VARCHAR2, b OUT VARCHAR2, c OUT VARCHAR2, dblink OUT VARCHAR2, nextpos OUT BINARY_INTEGER);
Table 8-16 NAME_TOKENIZE procedure parameters
Parameter | Description |
---|---|
|
The input name, consisting of SQL identifiers (for example, |
|
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 (not applicable for TimesTen) |
|
Next position after parsing the input name |
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.
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.
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);
Validates the object described either by owner, name, and namespace or by object ID.
DBMS_UTILITY.VALIDATE( object_id IN NUMBER); DBMS_UTILITY.VALIDATE( owner IN VARCHAR2, objname IN VARCHAR2, namespace NUMBER, edition_name VARCHAR2 := NULL;
Table 8-18 VALIDATE procedure parameters
Parameter | Description |
---|---|
|
ID number of the object to be validated See "INVALIDATE procedure" for information about object IDs. |
|
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
|
|
Reserved for future use |
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.
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.