General Information |
Purpose | Container for a group of unrelated utility procedures and functions |
First Availability | 7.3.4 |
Source | {ORACLE_HOME}/rdbms/admin/dbmsutil.sql |
Built-in Data Types | -- array of anydata TYPE anydata_array IS TABLE OF ANYDATA INDEX BY BINARY_INTEGER; -- Lists of database links TYPE dblink_array IS TABLE OF VARCHAR2(128) INDEX BY BINARY_INTEGER; -- Order in which objects should be generated. TYPE index_table_type IS TABLE OF BINARY_INTEGER INDEX BY BINARY_INTEGER; -- List of active instance numbers and instance names -- Starting index of instance_table is 1; TYPE instance_record IS RECORD ( inst_number NUMBER, inst_name VARCHAR2(60)); -- Instance_table is dense. TYPE instance_table IS TABLE OF instance_record INDEX BY BINARY_INTEGER; -- Lists of Long NAME: includes -- fully qualified attribute names. TYPE lname_array IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER; -- Lists of large VARCHAR2s should be stored here TYPE maxname_array IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER; -- Lists of NAME TYPE name_array IS TABLE OF VARCHAR2(30) INDEX BYBINARY_INTEGER; -- The order in which objects should be -- generated is returned here for users TYPE number_array IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; -- Lists of "USER"."NAME"."COLUMN"@LINK TYPE uncl_array IS TABLE OF VARCHAR2(227) INDEX BY BINARY_INTEGER; SUBTYPE maxraw IS RAW(32767); |
Constants |
Name | Data Type | Value | inv_error_on_restrictions | PLS_INTEGER | 1 | |
Dependencies | SELECT name FROM dba_dependencies WHERE referenced_name = 'DBMS_UTILITY' UNION SELECT referenced_name FROM dba_dependencies WHERE name = 'DBMS_UTILITY'; |
Exceptions |
|
|
ACTIVE_INSTANCES |
Identify active instances in a cluster | dbms_utility.active_instances ( instance_table OUT INSTANCE_TABLE, instance_count OUT NUMBER); |
set serveroutput on DECLARE inst_tab dbms_utility.instance_table; inst_cnt NUMBER; BEGIN IF dbms_utility.is_cluster_database THEN dbms_utility.active_instances(inst_tab, inst_cnt); dbms_output.put_line('-' || inst_tab.FIRST); dbms_output.put_line(TO_CHAR(inst_cnt)); ELSE dbms_output.put_line('Not A Clustered Database'); END IF; END; / |
|
ANALYZE_DATABASE |
Analyzes all the tables, clusters, and indexes in a database Deprecated in 10g | dbms_utility.analyze_database ( method IN VARCHAR2, estimate_rows IN NUMBER DEFAULT NULL, -- # of rows to est. estimate_percent IN NUMBER DEFAULT NULL, -- % of rows for est. method_opt IN VARCHAR2 DEFAULT NULL); -- method options: ESTIMATE, COMPUTE and DELETE -- method_opt options: FOR TABLE FOR ALL [INDEXED] COLUMNS] [SIZE n] FOR ALL INDEXES |
-- requires grant of ANALYZE ANY exec dbms_utility.analyze_database('ESTIMATE', 100, NULL, 'FOR TABLE'); |
|
ANALYZE_PART_OBJECT |
Equivalent to analyze table or index for partitioned objects | dbms_utility.analyze_part_object ( schema IN VARCHAR2 DEFAULT NULL, object_name IN VARCHAR2 DEFAULT NULL, object_type IN CHAR DEFAULT 'T', command_type IN CHAR DEFAULT 'E', command_opt IN VARCHAR2 DEFAULT NULL, sample_clause IN VARCHAR2 DEFAULT 'sample 5 percent'); -- command type options C (compute statistics) D (delete statistics) E (estimate statistics) V (validate structure) |
-- assumes a partitioned table named 'part_tab' dbms_utility.analyze_part_object('UWCLASS', 'PART_TAB', 'T', 'E', 'V'); |
|
ANALYZE_SCHEMA |
Analyzes all the tables, clusters, and indexes in a schema Deprecated in 10g | dbms_utility.analyze_schema ( schema IN VARCHAR2, method IN VARCHAR2, estimate_rows IN NUMBER DEFAULT NULL, estimate_percent IN NUMBER DEFAULT NULL, method_opt IN VARCHAR2 DEFAULT NULL); -- method options COMPUTE DELETE ESTIMATE |
dbms_utility.analyze_schema('UWCLASS','ESTIMATE', NULL, 10) |
|
CANONICALIZE |
Canonicalizes a given string | dbms_utility.canonicalize( name IN VARCHAR2, canon_name OUT VARCHAR2 canon_len IN BINARY_INTEGER); |
set serveroutput on DECLARE cname user_tables.table_name%TYPE; BEGIN dbms_utility.canonicalize('uwclass.test', cname, 16); dbms_output.put_line(cname); END; / |
|
COMMA_TO_TABLE |
Parses a comma delimited string Overload 1 | dbms_utility.comma_to_table( list IN VARCHAR2, tablen OUT BINARY_INTEGER, tab OUT UNCL_ARRAY); |
CREATE TABLE c2t_test ( readline VARCHAR2(200)); INSERT INTO c2t_test VALUES ('"1","Mainframe","31-DEC-2001"'); INSERT INTO c2t_test VALUES ('"2","MPP","01-JAN-2002"'); INSERT INTO c2t_test VALUES ('"3","Mid-Size","02-FEB-2003"'); INSERT INTO c2t_test VALUES ('"4","PC","03-MAR-2004"'); INSERT INTO c2t_test VALUES ('"5","Macintosh","04-APR-2005"'); COMMIT; SELECT * FROM c2t_test; CREATE TABLE test_import ( src_no NUMBER(5), src_desc VARCHAR2(20), load_date DATE); CREATE OR REPLACE PROCEDURE load_c2t_test IS c_string VARCHAR2(250); cnt BINARY_INTEGER; my_table dbms_utility.uncl_array; BEGIN FOR t_rec IN (SELECT * FROM c2t_test) LOOP dbms_utility.comma_to_table(t_rec.readline, cnt, my_table); my_table(1) := TRANSLATE(my_table(1), 'A"','A'); my_table(2) := TRANSLATE(my_table(2), 'A"','A'); my_table(3) := TRANSLATE(my_table(3), 'A"','A'); INSERT INTO test_import (src_no, src_desc, load_date) VALUES (TO_NUMBER(my_table(1)), my_table(2),TO_DATE(my_table(3))); END LOOP; COMMIT; END load_c2t_test; / exec load_c2t_test; SELECT * FROM test_import; |
Overload 2 | dbms_utility.comma_to_table( list IN VARCHAR2, tablen OUT BINARY_INTEGER, tab OUT lname_array); |
CREATE TABLE c2t_test ( readline VARCHAR2(200)); INSERT INTO c2t_test VALUES ('"1","Mainframe","31-DEC-2001"'); INSERT INTO c2t_test VALUES ('"2","MPP","01-JAN-2002"'); INSERT INTO c2t_test VALUES ('"3","Mid-Size","02-FEB-2003"'); INSERT INTO c2t_test VALUES ('"4","PC","03-MAR-2004"'); INSERT INTO c2t_test VALUES ('"5","Macintosh","04-APR-2005"'); COMMIT; SELECT * FROM c2t_test; CREATE TABLE test_import ( src_no NUMBER(5), src_desc VARCHAR2(20), load_date DATE); CREATE OR REPLACE PROCEDURE load_c2t_test IS c_string VARCHAR2(250); cnt BINARY_INTEGER; my_table dbms_utility.lname_array; BEGIN FOR t_rec IN (SELECT * FROM c2t_test) LOOP dbms_utility.comma_to_table(t_rec.readline, cnt, my_table); my_table(1) := TRANSLATE(my_table(1), 'A"','A'); my_table(2) := TRANSLATE(my_table(2), 'A"','A'); my_table(3) := TRANSLATE(my_table(3), 'A"','A'); INSERT INTO test_import (src_no, src_desc, load_date) VALUES (TO_NUMBER(my_table(1)), my_table(2),TO_DATE(my_table(3))); END LOOP; COMMIT; END load_c2t_test; / exec load_c2t_test; SELECT * FROM test_import; |
|
COMPILE_SCHEMA |
Compiles all procedures, functions, packages, and triggers in the specified schema | dbms_utility.compile_schema( schema IN VARCHAR2, compile_all IN BOOLEAN DEFAULT TRUE, reuse_settings IN BOOLEAN DEFAULT FALSE); |
exec dbms_utility.compile_schema('UWCLASS'); |
|
CREATE_ALTER_TYPE_ERROR_TABLE |
Creates an error table to be used in the EXCEPTION clause of the ALTER TYPE statement | dbms_utility.create_alter_type_error_table( schema_name IN VARCHAR2, table_name IN VARCHAR2); |
CREATE OR REPLACE TYPE CourseList AS TABLE OF VARCHAR2(30); / CREATE TABLE department ( name VARCHAR2(20), director VARCHAR2(20), office VARCHAR2(20), courses CourseList) NESTED TABLE courses STORE AS courses_tab; set describe depth all linenum on indent on desc department INSERT INTO department VALUES ('English', 'Lynn Saunders', 'Breakstone Hall 205', CourseList ( 'Expository Writing', 'Film and Literature', 'Modern Science Fiction', 'Discursive Writing', 'Modern English Grammar', 'Introduction to Shakespeare', 'Modern Drama', 'The Short Story', 'The American Novel')); COMMIT; SELECT * FROM department; exec dbms_utility.create_alter_type_error_table('UWCLASS', 'T_EXCEPT'); desc t_except col error_text format a30 SELECT * FROM t_except; ALTER TYPE CourseList MODIFY ELEMENT TYPE VARCHAR2(30) CASCADE EXCEPTIONS INTO t_except; SELECT * FROM t_except; |
|
CURRENT_INSTANCE |
Returns the current instance number | dbms_utility.current_instance RETURN NUMBER; |
SELECT dbms_utility.current_instance FROM DUAL; |
|
DATA_BLOCK_ADDRESS_BLOCK |
Returns the block number portion of a data block address | dbms_utility.data_block_address_block(dba IN NUMBER) RETURN NUMBER; |
col file_name format a50 SELECT file_name, file_id FROM dba_data_files; SELECT dbms_utility.make_data_block_address(4, 6) FROM DUAL; SELECT dbms_utility.data_block_address_block(16777222) FROM DUAL; |
|
DATA_BLOCK_ADDRESS_FILE |
Returns the file number portion of a data block address | dbms_utility.data_block_address_file(dba IN NUMBER) RETURN NUMBER |
col file_name format a50 SELECT file_name, file_id FROM dba_data_files; SELECT dbms_utility.make_data_block_address(4, 6) FROM DUAL; SELECT dbms_utility.data_block_address_file(16777222) FROM DUAL; |
|
DB_VERSION |
Returns database's version | dbms_utility.db_version ( version OUT VARCHAR2, compatibility OUT VARCHAR2); |
set serveroutput on DECLARE ver VARCHAR2(100); compat VARCHAR2(100); BEGIN dbms_utility.db_version(ver, compat); dbms_output.put_line('Version: ' || ver ||' Compatible: ' || compat); END; / |
|
EXEC_DDL_STMNT |
Execute A DDL Statement | dbms_utility.exec_ddl_statement(parse_string IN VARCHAR2); |
-- Create a stored procedure owned by a schema with the alter any user system privilege. CREATE OR REPLACE PROCEDURE sp_alter_user ( a_user_nameVARCHAR2, a_user_password VARCHAR2, a_admin VARCHAR2 := 'N') IS l_user VARCHAR2(255); l_user_grants VARCHAR2(255); l_user_default_role VARCHAR2(255); BEGIN l_user := 'alter user ' || a_user_name || ' identified by ' || a_user_password; -- If they need roles granted l_user_grants := 'GRANT connect,resource TO ' || a_user_name; l_user_default_role := 'alter user ' || a_user_name || ' default role dba'; dbms_utility.exec_ddl_statement(l_user); dbms_utility.exec_ddl_statement(l_user_grants); dbms_utility.exec_ddl_statement(l_user_default_role); END sp_alter_user; / CREATE OR REPLACE PROCEDURE sp_create_user (a_user_nameVARCHAR2, a_user_password VARCHAR2, a_admin VARCHAR2 := 'N') IS l_user VARCHAR2(255); BEGIN l_user := 'create user ' || a_user_name || ' identified by ' || a_user_password || ' temporary tablespace temp'; dbms_utility.exec_ddl_statement(l_user); sp_alter_user(a_user_name, a_user_password, a_admin); END sp_create_user; / |
|
FORMAT_CALL_STACK |
Formats the current call stack | dbms_utility.format_call_stack RETURN VARCHAR2; |
See Exception Handling Link Below |
|
FORMAT_ERROR_BACKTRACE |
Formats the backtrace from the point of the current error to the exception handler where the error has been caught. Returns NULL if an error is not currently raise Thanks Ken Naim for catching the RAISE error in this demo | dbms_utility.format_error_backtrace RETURN VARCHAR2; |
CREATE OR REPLACE PROCEDURE Log_Errors(i_buff VARCHAR2) IS g_start_pos INTEGER := 1; g_end_pos INTEGER; FUNCTION output_one_line RETURN BOOLEAN IS BEGIN g_end_pos := INSTR(i_buff, CHR(10), g_start_pos); CASE g_end_pos > 0 WHEN TRUE THEN dbms_output.put_line(SUBSTR(i_buff, g_start_pos, g_end_pos-g_start_pos)); g_start_pos := g_end_pos+1; RETURN TRUE; WHEN FALSE THEN dbms_output.put_line(SUBSTR(i_buff, g_start_pos, (LENGTH(i_buff)-g_start_pos)+1)); RETURN FALSE; END CASE; END Output_One_Line; BEGIN WHILE output_one_line() LOOP NULL; END LOOP; END Log_Errors; / set doc off set feedback off set echo off CREATE OR REPLACE PROCEDURE P0 IS xcpt EXCEPTION; pragma exception_init (xcpt, -1476); BEGIN RAISE xcpt; END P0; / CREATE OR REPLACE PROCEDURE P1 IS BEGIN P0(); END P1; / CREATE OR REPLACE PROCEDURE P2 IS BEGIN P1(); END P2; / CREATE OR REPLACE PROCEDURE P3 IS BEGIN P2(); END P3; / CREATE OR REPLACE PROCEDURE P4 IS BEGIN P3(); END P4; / CREATE OR REPLACE PROCEDURE P5 IS BEGIN P4(); END P5; / CREATE OR REPLACE PROCEDURE top_nolog IS BEGIN P5(); END top_nolog; / CREATE OR REPLACE PROCEDURE top_logging IS /* 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_logging; / set serveroutput on exec top_nolog; /* ERROR at line 1: ORA-01476: divisor is equal to zero ORA-06512: at "U.P0", line 4 ORA-06512: at "U.P1", line 3 ORA-06512: at "U.P2", line 3 ORA-06512: at "U.P3", line 3 ORA-06512: at "U.P4", line 2 ORA-06512: at "U.P5", line 2 ORA-06512: at "U.TOPNOLOG", line 3 */ exec top_logging /* Error_Stack... ORA-01476: divisor is equal to zero Error_Backtrace... ORA-06512: at "U.P0", line 4 ORA-06512: at "U.P1", line 3 ORA-06512: at "U.P2", line 3 ORA-06512: at "U.P3", line 3 ORA-06512: at "U.P4", line 2 ORA-06512: at "U.P5", line 2 ORA-06512: at "U.TOP_LOGGING", line 6 ---------- */ /* ORA-06512: Cause: Backtrace message as the stack is unwound by unhandled exceptions. */ |
|
FORMAT_ERROR_STACK |
Formats the current error stack | dbms_utility.format_error_stack RETURN VARCHAR2; |
See Exception Handling Link |
|
GET_CPU_TIME |
Returns the current CPU time in 100th's of a second | dbms_utility.get_cpu_time RETURN NUMBER; |
set serveroutput on DECLARE i NUMBER; j NUMBER; k NUMBER; BEGIN i := dbms_utility.get_cpu_time; SELECT COUNT(*) INTO j FROM all_tables t, all_indexes i WHERE t.table_name = i.table_name; k := dbms_utility.get_cpu_time; dbms_output.put_line(k-i); END; / DECLARE i NUMBER; j NUMBER; k NUMBER; BEGIN i := dbms_utility.get_cpu_time; SELECT COUNT(*) INTO j FROM all_tables t, all_indexes i WHERE t.tablespace_name = i.tablespace_name; k := dbms_utility.get_cpu_time; dbms_output.put_line(k-i); END; / |
|
GET_DEPENDENCY |
Shows the dependencies on the object passed in | dbms_utility.get_dependency( type IN VARCHAR2, schema IN VARCHAR2, name IN VARCHAR2); |
CREATE TABLE testtab ( testcol VARCHAR2(20)); CREATE VIEW testview AS SELECT * FROM testtab; CREATE TRIGGER testtrig BEFORE INSERT ON testtab BEGIN NULL; END testtrig; / CREATE OR REPLACE PROCEDURE testproc IS i PLS_INTEGER; BEGIN SELECT COUNT(*) INTO i FROM testtab; dbms_output.put_line(TO_CHAR(i)); END testproc; / set serveroutput on exec dbms_utility.get_dependency('TABLE', 'UWCLASS', 'TESTTAB'); |
|
GET_ENDIANNESS |
Undocumented | dbms_utility.get_endianness RETURN NUMBER; |
SELECT dbms_utility.get_endianness FROM DUAL; |
|
GET_HASH_VALUE |
Calculate a Hash Value From An Input | dbms_utility.get_hash_value( name IN VARCHAR2, base IN NUMBER, hash_size IN NUMBER) RETURN NUMBER; |
set linesize 121 CREATE TABLE t AS SELECT * FROM airplanes; SELECT COUNT(*) FROM t; SELECT COUNT(*) FROM t WHERE dbms_utility.get_hash_value(ROWID || TO_CHAR(SYSDATE, 'HH:MI:SS'), 1, 100) = 1; / / / |
|
GET_PARAMETER_VALUE |
Gets the value of specified init.ora parameter | dbms_utility.get_parameter_value ( parnam IN VARCHAR2, interval IN OUT BINARY_INTEGER, strval IN OUT VARCHAR2, listno IN BINARY_INTEGER DEFAULT 1) RETURN BINARY_INTEGER; |
set serveroutput on DECLARE i BINARY_INTEGER; pname v_$parameter.name%TYPE; intval BINARY_INTEGER; strval v_$parameter.value%TYPE; x BINARY_INTEGER; BEGIN pname := 'optimizer_max_permutations'; x := dbms_utility.get_parameter_value(pname, intval, strval); IF x = 0 THEN -- integer or boolean dbms_output.put_line('IntVal: ' || TO_CHAR(intval)); ELSE dbms_output.put_line('StrVal: ' || strval); dbms_output.put_line('IntVal: ' || TO_CHAR(intval)); END IF; END; / SELECT type, value FROM gv$parameter WHERE name = 'optimizer_max_permutations'; DECLARE i BINARY_INTEGER; pname v_$parameter.name%TYPE; intval BINARY_INTEGER; strval v_$parameter.value%TYPE; x BINARY_INTEGER; BEGIN pname := 'utl_file_dir'; x := dbms_utility.get_parameter_value(pname, intval, strval); IF x = 0 THEN -- integer or boolean dbms_output.put_line('IntVal: ' || TO_CHAR(intval)); ELSE dbms_output.put_line('StrVal: ' || strval); dbms_output.put_line('IntVal: ' || TO_CHAR(intval)); END IF; END; / SELECT type, value FROM gv$parameter WHERE name = 'utl_file_dir'; |
|
GET_SQL_HASH |
Compute 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; |
set serveroutput on DECLARE h RAW(32767); n NUMBER; x NUMBER; BEGIN x := dbms_utility.get_sql_hash('Dan Morgan', h, n); dbms_output.put_line('Return Value: ' || TO_CHAR(x)); dbms_output.put_line('Hash: ' || h); dbms_output.put_line('Pre10iHash: ' || TO_CHAR(n)); END; / SELECT ORA_HASH('Dan Morgan') FROM DUAL; |
|
GET_TIME |
Finds out the current time in 100th's of a second | dbms_utility.get_time RETURN NUMBER; |
set serveroutput on DECLARE i NUMBER; j NUMBER; BEGIN i := dbms_utility.get_time; dbms_lock.sleep(1.6); j := dbms_utility.get_time; dbms_output.put_line(j-i); END; / |
|
GET_TZ_TRANSITIONS |
Get time zone transitions from the timezone.dat file | dbms_utility.get_tz_transitions( regionid IN NUMBER, transitions OUT RAW); |
set serveroutput on DECLARE r RAW(22); BEGIN dbms_utility.get_tz_transitions(10, r); dbms_output.put_line(r); dbms_utility.get_tz_transitions(12, r); dbms_output.put_line(r); END; / |
|
INVALIDATE |
Force object invalidation | dbms_utility.invalidate( p_object_id IN NUMBER, p_plsql_object_settings IN VARCHAR2 DEFAULT NULL, p_option_flags IN PLS_INTEGER DEFAULT 0); |
CREATE TABLE test ( testcol VARCHAR2(20)); CREATE OR REPLACE PROCEDURE testproc IS i PLS_INTEGER; BEGIN SELECT COUNT(*) INTO i FROM test; END testproc; / col object_name format a30 SELECT object_id, object_name, object_type FROM user_objects WHERE object_name = 'TESTPROC'; exec dbms_utility.invalidate(115638, 'plsql_code_type = native'); SELECT object_id, object_name FROM user_objects WHERE status = 'INVALID'; |
|
IS_BIT_SET |
Assist the view of DBA_PENDING_TRANSACTION | dbms_utility.is_bit_set(r IN RAW, n IN NUMBER) RETURN NUMBER; |
SELECT global_tran_fmt, global_foreign_id, branch_id FROM sys.pending_trans$ tran, sys.pending_sessions$ sess WHERE tran.local_tran_id = sess.local_tran_id AND tran.state != 'collecting' AND dbms_utility.is_bit_set(tran.session_vector, sess.session_id)=1; |
Demo sent by Stan Hartin for the library | CREATE TABLE bunch_of_flags ( daylist VARCHAR2(8) NOT NULL); INSERT INTO bunch_of_flags (daylist) VALUES ('11111111'); INSERT INTO bunch_of_flags (daylist) VALUES ('11111000'); INSERT INTO bunch_of_flags (daylist) VALUES ('00000111'); COMMIT; col raw_daylist format a20 col 29 format 999 col 25 format 999 col 21 format 999 col 17 format 999 col 13 format 999 col 09 format 999 col 05 format 999 col 01 format 999 SELECT daylist, utl_raw.cast_to_raw(daylist) RAW_DAYLIST, dbms_utility.is_bit_set(daylist, 29) "29", dbms_utility.is_bit_set(daylist, 25) "25", dbms_utility.is_bit_set(daylist, 21) "21", dbms_utility.is_bit_set(daylist, 17) "17", dbms_utility.is_bit_set(daylist, 13) "13", dbms_utility.is_bit_set(daylist, 9) "09", dbms_utility.is_bit_set(daylist, 5) "05", dbms_utility.is_bit_set(daylist, 1) "01" FROM bunch_of_flags; |
|
IS_CLUSTER_DATABASE |
Returns TRUE if this instance was started in cluster database mode; otherwise FALSE | dbms_utility.is_cluster_database RETURN BOOLEAN; |
set serveroutput on BEGIN IF dbms_utility.is_cluster_database THEN dbms_output.put_line('TRUE'); ELSE dbms_output.put_line('FALSE'); END IF; END; / |
|
MAKE_DATA_BLOCK_ADDRESS |
Creates a data block address, an internal structure used to identify a block in the database, given a file number and a block number | dbms_utility.make_data_block_address( file_number IN NUMBER, block_number IN NUMBER) RETURN NUMBER; |
col file_name format a50 SELECT file_name, file_id FROM dba_data_files; SELECT dbms_utility.make_data_block_address(6, 4) FROM DUAL; |
|
NAME_RESOLVE |
Resolves the given name, including synonym translation and authorization checking as necessary | dbms_utility.name_resolve ( name IN VARCHAR2, context IN NUMBER, -- integer from 0 to 9 schema OUT VARCHAR2, part1 OUT VARCHAR2, part2 OUT VARCHAR2, dblink OUT VARCHAR2, part1_type OUT NUMBER, object_number OUT NUMBER); context 0 = table context 1 = function, procedure, package context 2 = sequence context 3 = trigger context 4 = java store context 5 = java resource context 6 = java class context 7 = type context 8 = java shared data context 9 = index part1_type 5 = synonym part1_type 7 = procedure (top level) part1_type 8 = function (top level) part1_type 9 = package Metalink Note 1008700.6 states that it only works properly for procedures, functions and packages |
set serveroutput on DECLARE s VARCHAR2(30); p1 VARCHAR2(30); p2 VARCHAR2(30); d VARCHAR2(30); o NUMBER(10); ob NUMBER(10); BEGIN dbms_utility.name_resolve('UWCLASS.PERSON.SSN', 2, s, p1, p2, d, o, ob); dbms_output.put_line('Owner: ' || s); dbms_output.put_line('Table: ' || p1); dbms_output.put_line('Column: ' || p2); dbms_output.put_line('Link: ' || d); END; / |
|
NAME_TOKENIZE |
Calls the parser to parse the given name as "a [. b [. c ]][@ dblink ]". It strips double quotes, or converts to uppercase if there are no quotes. It ignores comments of all sorts, and does no semantic analysis. Missing values are left as NULL. | dbms_utility.name_tokenize name IN VARCHAR2, a OUT VARCHAR2, b OUT VARCHAR2, c OUT VARCHAR2, dblink OUT VARCHAR2, nextpos OUT BINARY_INTEGER); |
set serveroutput on DECLARE a VARCHAR2(30); b VARCHAR2(30); c VARCHAR2(30); d VARCHAR2(30); i BINARY_INTEGER; BEGIN dbms_utility.name_tokenize('UWCLASS.PERSON.SSN', a, b, c, d, i); dbms_output.put_line('Owner: ' || a); dbms_output.put_line('Table: ' || b); dbms_output.put_line('Column: ' || c); dbms_output.put_line('Link: ' || d); END; / |
|
OLD_CURRENT_SCHEMA |
Undocumented | dbms_utility.old_current_schema RETURN VARCHAR2; |
SELECT dbms_utility.old_current_schema FROM DUAL; |
|
OLD_CURRENT_USER |
Undocumented | dbms_utility.old_current_user RETURN VARCHAR2; |
SELECT dbms_utility.old_current_user FROM DUAL; |
|
PORT_STRING |
Returns the operating system and the TWO TASK PROTOCOL version of the database | dbms_utility.port_string RETURN VARCHAR2; |
SELECT dbms_utility.port_string FROM DUAL; |
|
SQLID_TO_SQLHASH |
Compute a hash value for the given string using the md5 algorithm | dbms_utility.sqlid_to_sqlhash(sql_id IN VARCHAR2) RETURN NUMBER; |
SELECT sql_id, dbms_utility.sqlid_to_sqlhash(sql_id) FROM gv$sql WHERE rownum < 21; |
|
TABLE_TO_COMMA |
Converts a PL/SQL table of names into a comma-delimited list Overload 1 | dbms_utility.table_to_comma ( tab IN UNCL_ARRAY, tablen OUT BINARY_INTEGER, list OUT VARCHAR2); |
set serveroutput on DECLARE x dbms_utility.uncl_array; y BINARY_INTEGER; z VARCHAR2(4000); BEGIN x(1) := 'ABC,DEF'; x(2) := 'GHI,JKL,MNO'; x(3) := 'PQR,STU,VWX,YZ1'; x(4) := '2,3,4,5,6'; x(5) := 'ABC,January,Morgan,University of Washington'; dbms_output.put_line('1: ' || x(1)); dbms_output.put_line('2: ' || x(2)); dbms_output.put_line('3: ' || x(3)); dbms_output.put_line('4: ' || x(4)); dbms_output.put_line('5: ' || x(5)); dbms_utility.table_to_comma(x, y, z); dbms_output.put_line('Array Size: ' || TO_CHAR(y)); dbms_output.put_line('List: ' || z); END; / |
Overload 2 | dbms_utility.table_to_comma ( tab IN lname_array, tablen OUT BINARY_INTEGER, list OUT VARCHAR2); |
set serveroutput on DECLARE x dbms_utility.lname_array; y BINARY_INTEGER; z VARCHAR2(4000); BEGIN x(1) := 'ABC,DEF'; x(2) := 'GHI,JKL,MNO'; x(3) := 'PQR,STU,VWX,YZ1'; x(4) := '2,3,4,5,6'; x(5) := 'ABC,January,Morgan,University of Washington'; dbms_output.put_line('1: ' || x(1)); dbms_output.put_line('2: ' || x(2)); dbms_output.put_line('3: ' || x(3)); dbms_output.put_line('4: ' || x(4)); dbms_output.put_line('5: ' || x(5)); dbms_utility.table_to_comma(x, y, z); dbms_output.put_line('Array Size: ' || TO_CHAR(y)); dbms_output.put_line('List: ' || z); END; / |
|
VALIDATE |
Validates invalid objects Overload 1 | dbms_utility.validate(object_id IN NUMBER); |
CREATE TABLE test ( testcol VARCHAR2(20)); CREATE OR REPLACE PROCEDURE testproc IS i PLS_INTEGER; BEGIN SELECT COUNT(*) INTO i FROM test; END testproc; / SELECT object_id, object_name FROM user_objects WHERE status = 'INVALID'; ALTER TABLE test MODIFY (testcol VARCHAR2(25)); SELECT object_id, object_name FROM user_objects WHERE status = 'INVALID'; exec dbms_utility.validate(63574); SELECT object_id, object_name FROM user_objects WHERE status = 'INVALID'; |
Overload 2 Note: Editions are not supported in 11gR1 | dbms_utility.validate( owner IN VARCHAR2, objname IN VARCHAR2, namespace IN NUMBER, -- namespace field from obj$ edition IN VARCHAR2 := SYS_CONTEXT('USERENV','CURRENT_EDITION_NAME')); |
CREATE TABLE test ( testcol VARCHAR2(20)); CREATE OR REPLACE PROCEDURE testproc IS i PLS_INTEGER; BEGIN SELECT COUNT(*) INTO i FROM test; END testproc; / SELECT object_id, object_name FROM user_objects WHERE status = 'INVALID'; ALTER TABLE test MODIFY (testcol VARCHAR2(25)); SELECT object_id, object_name FROM user_objects WHERE status = 'INVALID'; exec dbms_utility.validate('UWCLASS', 'TESTPROC'); SELECT object_id, object_name FROM user_objects WHERE status = 'INVALID'; |