Oracle DBMS_UTILITY 用法例子

Oracle DBMS_UTILITY

Version 11.1

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

Error Code	Description
ORA-20000	Insufficient privileges for some object in this schema
ORA-20001	Cannot recompile SYS objects
ORA-24237	inv_not_exist_or_no_priv
ORA-24238	inv_malformed_settings
ORA-24239	inv_restricted_object
ORA-29261	hash size is 0

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