Oracle DBMS_UTILITY

Search the Reference Library pages:

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
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 BY BINARY_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

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_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

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_name VARCHAR2,
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_name VARCHAR2,
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';

Related Topics

DBMS_ODCI

Exception Handling

Partitioned Tables

-----