CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

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
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_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
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [181 users online]    © 2010 psoug.org