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 TYPEanydata_array ISTABLEOF ANYDATA INDEXBYBINARY_INTEGER;
-- Lists of database links TYPEdblink_array ISTABLEOFVARCHAR2(128) INDEXBYBINARY_INTEGER;
-- Order in which objects should be generated. TYPEindex_table_type ISTABLEOFBINARY_INTEGER INDEXBYBINARY_INTEGER;
-- List of active instance numbers and instance names -- Starting index of instance_table is 1; TYPEinstance_record IS RECORD ( inst_numberNUMBER, inst_nameVARCHAR2(60));
-- Instance_table is dense. TYPEinstance_table ISTABLEOF instance_record INDEXBYBINARY_INTEGER;
-- Lists of Long NAME: includes -- fully qualified attribute names. TYPElname_array ISTABLEOFVARCHAR2(4000) INDEXBYBINARY_INTEGER;
-- Lists of largeVARCHAR2s should be stored here TYPEmaxname_array ISTABLEOFVARCHAR2(32767) INDEXBYBINARY_INTEGER;
-- Lists of NAME TYPEname_array ISTABLEOFVARCHAR2(30)INDEXBYBINARY_INTEGER;
-- The order in which objects should be -- generated is returned here for users TYPEnumber_array ISTABLEOFNUMBER INDEXBYBINARY_INTEGER;
-- Lists of "USER"."NAME"."COLUMN"@LINK TYPEuncl_array ISTABLEOFVARCHAR2(227) INDEXBYBINARY_INTEGER;
SUBTYPE maxraw ISRAW(32767); |
Constants |
Name | Data Type | Value | inv_error_on_restrictions | PLS_INTEGER | 1 |
|
Dependencies | SELECTname FROMdba_dependencies WHEREreferenced_name = 'DBMS_UTILITY' UNION SELECTreferenced_name FROMdba_dependencies WHEREname = '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_tabdbms_utility.instance_table; inst_cntNUMBER; BEGIN IFdbms_utility.is_cluster_databaseTHEN 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'); ENDIF; 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 execdbms_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); |
CREATETABLEc2t_test ( readlineVARCHAR2(200));
INSERTINTOc2t_testVALUES('"1","Mainframe","31-DEC-2001"'); INSERTINTOc2t_testVALUES('"2","MPP","01-JAN-2002"'); INSERTINTOc2t_testVALUES('"3","Mid-Size","02-FEB-2003"'); INSERTINTOc2t_testVALUES('"4","PC","03-MAR-2004"'); INSERTINTOc2t_testVALUES('"5","Macintosh","04-APR-2005"'); COMMIT;
SELECT*FROMc2t_test;
CREATETABLEtest_import ( src_noNUMBER(5), src_descVARCHAR2(20), load_dateDATE);
CREATEORREPLACEPROCEDUREload_c2t_test IS
c_stringVARCHAR2(250); cntBINARY_INTEGER; my_tabledbms_utility.uncl_array;
BEGIN FORt_recIN(SELECT*FROMc2t_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');
INSERTINTOtest_import (src_no, src_desc, load_date) VALUES (TO_NUMBER(my_table(1)), my_table(2),TO_DATE(my_table(3))); ENDLOOP; COMMIT; ENDload_c2t_test; /
exec load_c2t_test;
SELECT*FROMtest_import; |
Overload 2 | dbms_utility.comma_to_table( list IN VARCHAR2, tablen OUT BINARY_INTEGER, tab OUT lname_array); |
CREATETABLEc2t_test ( readlineVARCHAR2(200));
INSERTINTOc2t_testVALUES('"1","Mainframe","31-DEC-2001"'); INSERTINTOc2t_testVALUES('"2","MPP","01-JAN-2002"'); INSERTINTOc2t_testVALUES('"3","Mid-Size","02-FEB-2003"'); INSERTINTOc2t_testVALUES('"4","PC","03-MAR-2004"'); INSERTINTOc2t_testVALUES('"5","Macintosh","04-APR-2005"'); COMMIT;
SELECT*FROMc2t_test;
CREATETABLEtest_import ( src_noNUMBER(5), src_descVARCHAR2(20), load_dateDATE);
CREATEORREPLACEPROCEDUREload_c2t_test IS
c_stringVARCHAR2(250); cntBINARY_INTEGER; my_tabledbms_utility.lname_array;
BEGIN FORt_recIN(SELECT*FROMc2t_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');
INSERTINTOtest_import (src_no, src_desc, load_date) VALUES (TO_NUMBER(my_table(1)), my_table(2),TO_DATE(my_table(3))); ENDLOOP; COMMIT; ENDload_c2t_test; /
exec load_c2t_test;
SELECT*FROMtest_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); |
execdbms_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); |
CREATEORREPLACETYPECourseListASTABLEOFVARCHAR2(30); /
CREATETABLEdepartment ( nameVARCHAR2(20), directorVARCHAR2(20), officeVARCHAR2(20), courses CourseList) NESTEDTABLEcourses STOREAScourses_tab;
set describe depth all linenum on indent on
desc department
INSERTINTOdepartment 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* FROMdepartment;
execdbms_utility.create_alter_type_error_table('UWCLASS', 'T_EXCEPT');
desc t_except
col error_text format a30
SELECT* FROMt_except;
ALTERTYPECourseList MODIFY ELEMENTTYPEVARCHAR2(30) CASCADE EXCEPTIONSINTOt_except;
SELECT* FROMt_except; |
|
CURRENT_INSTANCE |
Returns the current instance number | dbms_utility.current_instance RETURN NUMBER; |
SELECTdbms_utility.current_instance FROMDUAL; |
|
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
SELECTfile_name, file_id FROMdba_data_files;
SELECTdbms_utility.make_data_block_address(4, 6) FROMDUAL;
SELECTdbms_utility.data_block_address_block(16777222) FROMDUAL; |
|
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
SELECTfile_name, file_id FROMdba_data_files;
SELECTdbms_utility.make_data_block_address(4, 6) FROMDUAL;
SELECTdbms_utility.data_block_address_file(16777222) FROMDUAL; |
|
DB_VERSION |
Returns database's version | dbms_utility.db_version ( version OUT VARCHAR2, compatibility OUT VARCHAR2); |
set serveroutput on
DECLARE verVARCHAR2(100); compatVARCHAR2(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.
CREATEORREPLACEPROCEDUREsp_alter_user ( a_user_nameVARCHAR2, a_user_passwordVARCHAR2, a_adminVARCHAR2:= 'N') IS l_userVARCHAR2(255); l_user_grantsVARCHAR2(255); l_user_default_roleVARCHAR2(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); ENDsp_alter_user; /
CREATEORREPLACEPROCEDUREsp_create_user (a_user_nameVARCHAR2, a_user_passwordVARCHAR2, a_adminVARCHAR2:= 'N') IS l_userVARCHAR2(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); ENDsp_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; |
CREATEORREPLACEPROCEDURELog_Errors(i_buffVARCHAR2) IS g_start_posINTEGER:= 1; g_end_posINTEGER; FUNCTIONoutput_one_lineRETURNBOOLEANIS BEGIN g_end_pos :=INSTR(i_buff,CHR(10), g_start_pos);
CASEg_end_pos > 0 WHENTRUETHEN dbms_output.put_line(SUBSTR(i_buff, g_start_pos, g_end_pos-g_start_pos));
g_start_pos := g_end_pos+1; RETURNTRUE; WHENFALSETHEN dbms_output.put_line(SUBSTR(i_buff, g_start_pos, (LENGTH(i_buff)-g_start_pos)+1)); RETURNFALSE; ENDCASE; ENDOutput_One_Line;
BEGIN WHILEoutput_one_line()LOOP NULL; ENDLOOP; ENDLog_Errors; /
set doc off set feedback off set echo off
CREATEORREPLACEPROCEDUREP0 IS xcptEXCEPTION; pragma exception_init (xcpt, -1476); BEGIN RAISExcpt; ENDP0; /
CREATEORREPLACEPROCEDUREP1 IS BEGIN P0(); ENDP1; /
CREATEORREPLACEPROCEDUREP2 IS BEGIN P1(); ENDP2; /
CREATEORREPLACEPROCEDUREP3 IS BEGIN P2(); ENDP3; /
CREATEORREPLACEPROCEDUREP4 IS BEGIN P3(); ENDP4; /
CREATEORREPLACEPROCEDUREP5 IS BEGIN P4(); ENDP5; /
CREATEORREPLACEPROCEDUREtop_nolog IS BEGIN P5(); ENDtop_nolog; /
CREATEORREPLACEPROCEDUREtop_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 WHENOTHERSTHEN 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( '----------' ); ENDtop_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 iNUMBER; jNUMBER; kNUMBER; BEGIN i :=dbms_utility.get_cpu_time;
SELECTCOUNT(*) INTOj FROMall_tables t, all_indexes i WHEREt.table_name = i.table_name;
k :=dbms_utility.get_cpu_time;
dbms_output.put_line(k-i); END; /
DECLARE iNUMBER; jNUMBER; kNUMBER; BEGIN i :=dbms_utility.get_cpu_time;
SELECTCOUNT(*) INTOj FROMall_tables t, all_indexes i WHEREt.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); |
CREATETABLEtesttab ( testcolVARCHAR2(20));
CREATEVIEWtestview AS SELECT*FROMtesttab;
CREATETRIGGERtesttrig BEFOREINSERT ON testtab BEGIN NULL; ENDtesttrig; /
CREATEORREPLACEPROCEDUREtestproc IS iPLS_INTEGER; BEGIN SELECTCOUNT(*) INTOi FROMtesttab;
dbms_output.put_line(TO_CHAR(i)); ENDtestproc; /
set serveroutput on
execdbms_utility.get_dependency('TABLE', 'UWCLASS', 'TESTTAB'); |
|
GET_ENDIANNESS |
Undocumented | dbms_utility.get_endianness RETURN NUMBER; |
SELECTdbms_utility.get_endianness FROMDUAL; |
|
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
CREATETABLEt AS SELECT*FROMairplanes;
SELECTCOUNT(*) FROMt;
SELECTCOUNT(*) FROMt WHEREdbms_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, intervalIN OUT BINARY_INTEGER, strvalIN OUT VARCHAR2, listno IN BINARY_INTEGER DEFAULT 1) RETURN BINARY_INTEGER; |
set serveroutput on
DECLARE
iBINARY_INTEGER; pname v_$parameter.name%TYPE; intvalBINARY_INTEGER; strval v_$parameter.value%TYPE; xBINARY_INTEGER;
BEGIN pname := 'optimizer_max_permutations'; x :=dbms_utility.get_parameter_value(pname, intval, strval);
IFx = 0THEN-- 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)); ENDIF; END; /
SELECTtype, value FROMgv$parameter WHEREname = 'optimizer_max_permutations';
DECLARE iBINARY_INTEGER; pname v_$parameter.name%TYPE; intvalBINARY_INTEGER; strval v_$parameter.value%TYPE; xBINARY_INTEGER; BEGIN pname := 'utl_file_dir'; x :=dbms_utility.get_parameter_value(pname, intval, strval);
IFx = 0THEN-- 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)); ENDIF; END; /
SELECTtype, value FROMgv$parameter WHEREname = '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 hRAW(32767); nNUMBER; xNUMBER; 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; /
SELECTORA_HASH('Dan Morgan')FROMDUAL; |
|
GET_TIME |
Finds out the current time in 100th's of a second | dbms_utility.get_time RETURN NUMBER; |
set serveroutput on
DECLARE iNUMBER; jNUMBER; 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 rRAW(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); |
CREATETABLEtest ( testcolVARCHAR2(20));
CREATEORREPLACEPROCEDUREtestproc IS iPLS_INTEGER; BEGIN SELECTCOUNT(*) INTOi FROMtest; ENDtestproc; /
col object_name format a30
SELECTobject_id, object_name, object_type FROMuser_objects WHEREobject_name = 'TESTPROC';
execdbms_utility.invalidate(115638, 'plsql_code_type = native');
SELECTobject_id, object_name FROMuser_objects WHEREstatus = 'INVALID'; |
|
IS_BIT_SET |
Assist the view of DBA_PENDING_TRANSACTION | dbms_utility.is_bit_set(r IN RAW,n IN NUMBER) RETURN NUMBER; |
SELECTglobal_tran_fmt, global_foreign_id, branch_id FROMsys.pending_trans$ tran, sys.pending_sessions$ sess WHEREtran.local_tran_id = sess.local_tran_id ANDtran.state != 'collecting' ANDdbms_utility.is_bit_set(tran.session_vector, sess.session_id)=1; |
Demo sent by Stan Hartin for the library | CREATETABLEbunch_of_flags ( daylistVARCHAR2(8)NOTNULL);
INSERTINTObunch_of_flags (daylist)VALUES('11111111'); INSERTINTObunch_of_flags (daylist)VALUES('11111000'); INSERTINTObunch_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
SELECTdaylist, 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" FROMbunch_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 IFdbms_utility.is_cluster_databaseTHEN dbms_output.put_line('TRUE'); ELSE dbms_output.put_line('FALSE'); ENDIF; 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
SELECTfile_name, file_id FROMdba_data_files;
SELECTdbms_utility.make_data_block_address(6, 4) FROMDUAL; |
|
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 sVARCHAR2(30); p1VARCHAR2(30); p2VARCHAR2(30); dVARCHAR2(30); oNUMBER(10); obNUMBER(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 aVARCHAR2(30); bVARCHAR2(30); cVARCHAR2(30); dVARCHAR2(30); iBINARY_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; |
SELECTdbms_utility.old_current_schema FROMDUAL; |
|
OLD_CURRENT_USER |
Undocumented | dbms_utility.old_current_user RETURN VARCHAR2; |
SELECTdbms_utility.old_current_user FROMDUAL; |
|
PORT_STRING |
Returns the operating system and the TWO TASK PROTOCOL version of the database | dbms_utility.port_string RETURN VARCHAR2; |
SELECTdbms_utility.port_string FROMDUAL; |
|
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; |
SELECTsql_id,dbms_utility.sqlid_to_sqlhash(sql_id) FROMgv$sql WHERErownum < 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 xdbms_utility.uncl_array; yBINARY_INTEGER; zVARCHAR2(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 xdbms_utility.lname_array; yBINARY_INTEGER; zVARCHAR2(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); |
CREATETABLEtest ( testcolVARCHAR2(20));
CREATEORREPLACEPROCEDUREtestproc IS iPLS_INTEGER; BEGIN SELECTCOUNT(*) INTOi FROMtest; ENDtestproc; /
SELECTobject_id, object_name FROMuser_objects WHEREstatus = 'INVALID';
ALTERTABLEtest MODIFY (testcolVARCHAR2(25));
SELECTobject_id, object_name FROMuser_objects WHEREstatus = 'INVALID';
execdbms_utility.validate(63574);
SELECTobject_id, object_name FROMuser_objects WHEREstatus = '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')); |
CREATETABLEtest ( testcolVARCHAR2(20));
CREATEORREPLACEPROCEDUREtestproc IS iPLS_INTEGER; BEGIN SELECTCOUNT(*) INTOi FROMtest; ENDtestproc; /
SELECTobject_id, object_name FROMuser_objects WHEREstatus = 'INVALID';
ALTERTABLEtest MODIFY (testcolVARCHAR2(25));
SELECTobject_id, object_name FROMuser_objects WHEREstatus = 'INVALID';
execdbms_utility.validate('UWCLASS', 'TESTPROC');
SELECTobject_id, object_name FROMuser_objects WHEREstatus = 'INVALID'; |