Oracle DBMS_UTILITY
Version 23c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
Purpose Container for a group of unrelated utility procedures and functions
AUTHID DEFINER
Constants
Name Data Type Value
Invalidate Option Flag
INV_ERROR_ON_RESTRICTIONS PLS_INTEGER 1
SYMLINK Constants
NO_SYMLINK PLS_INTEGER 0
HAS_SYMLINK PLS_INTEGER 1
Data Types -- 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));

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

-- 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 dbms_id
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(32767)
INDEX BY BINARY_INTEGER;

-- Lists of NAME that might be quoted should be stored here
TYPE quoted_name_array IS TABLE OF dbms_quoted_id
INDEX BY BINARY_INTEGER;

SUBTYPE maxraw IS RAW(32767);
Dependencies SELECT name FROM dba_dependencies WHERE referenced_name = 'DBMS_UTILITY'
UNION
SELECT referenced_name FROM dba_dependencies WHERE name = 'DBMS_UTILITY';

-- this query returns 284 objects
Documented Yes
Exceptions
Error Code Reason
ORA-00900 Input is not valid
ORA-00942 Current user does not have select privs on all the views and tables recursively referenced in the input SQL
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-24251 Raised if the input_sql text is not a select statement
ORA-29261 hash size is 0
ORA-29477 Input LOB size exceeds the maximum size of 4GB -1
First Available 7.3.4
Security Model Owned by SYS with EXECUTE granted to DBSFWUSER, DVSYS, GSMADMIN_INTERNAL, ORDSYS, PUBLIC, and WMSYS.
Source {ORACLE_HOME}/rdbms/admin/dbmsutil.sql
Subprograms
ACTIVE_INSTANCES EXPAND_SQL_TEXT INVALIDATE
ANALYZE_DATABASE FORMAT_CALL_STACK IS_BIT_SET
ANALYZE_PART_OBJECT FORMAT_ERROR_BACKTRACE IS_CLUSTER_DATABASE
ANALYZE_SCHEMA FORMAT_ERROR_STACK MAKE_DATA_BLOCK_ADDRESS
CANONICALIZE GET_COLUMN_LIMIT NAME_RESOLVE
COMMA_TO_TABLE GET_CPU_TIME NAME_TOKENIZE
COMPILE_SCHEMA GET_DEPENDENCY OLD_CURRENT_SCHEMA
CREATE_ALTER_TYPE_ERROR_TABLE GET_ENDIANNESS OLD_CURRENT_USER
CURRENT_INSTANCE GET_HASH_VALUE PORT_STRING
DATA_BLOCK_ADDRESS_BLOCK GET_PARAMETER_VALUE SQLID_TO_SQLHASH
DATA_BLOCK_ADDRESS_FILE GET_SQL_HASH TABLE_TO_COMMA
DB_VERSION GET_TIME VALIDATE
DIRECTORY_HAS_SYMLINK GET_TZ_TRANSITIONS WAIT_ON_PENDING_DML
EXEC_DDL_STATEMENT INIT_NUMBER_ARRAY  
 
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

Deprecated as of 12.2: Oracle recommends DBMS_STATS as the replacement		 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'
exec 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,               -- values COMPUTE, DELETE, ESTIMATE
estimate_rows    IN NUMBER   DEFAULT NULL,
estimate_percent IN NUMBER   DEFAULT NULL,
method_opt       IN VARCHAR2 DEFAULT NULL);
exec 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);
conn uwclass/uwclass@pdbdev

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 AUTHID DEFINER 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);
conn uwclass/uwclass@pdbdev

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 AUTHID CURRENT_USER 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);
conn uwclass/uwclass@pdbdev

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 the database's version and compatibility parameter

Oracle documentation does not say that this procedure is deprecated but its use in 19c returns a the following:
PLW-06020: reference to a deprecated entity: DB_VERSION declared
in unit DBMS_UTILITY[532,13]. DBMS_UTILITY.DB_VERSION is
deprecated!		 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;
/
 
DIRECTORY_HAS_SYMLINK
Determines if the file system directory referenced by the directory object contains a symbolic or hard link dbms_utility.directory_has_symlink(dirobj IN VARCHAR2)
RETURN PLS_INTEGER;
col directory_name format a40
col directory_path format a60

SELECT directory_name, directory_path, dbms_utility.directory_has_symlink(directory_name)
FROM dba_directories
ORDER BY 1;
 
EXEC_DDL_STATEMENT
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') AUTHID DEFINER 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') AUTHID DEFINER 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;
/
 
EXPAND_SQL_TEXT
Recursively replaces any view references in the input SQL query with the corresponding view subquery dbms_utility.expand_sql_text(
input_sql_text  IN         CLOB,
output_sql_text OUT NOCOPY CLOB);
conn uwclass/uwclass@pdbdev

CREATE VIEW uwclass.expandv AS
SELECT * FROM servers;

SELECT DISTINCT srvr_id
FROM uwclass.expandv
WHERE srvr_id NOT IN (
  SELECT srvr_id
  FROM uwclass.expandv
  MINUS
  SELECT srvr_id
  FROM uwclass.serv_inst);

EXPLAIN PLAN FOR
SELECT DISTINCT srvr_id
FROM uwclass.expandv
WHERE srvr_id NOT IN (
  SELECT srvr_id
  FROM uwclass.expandv
  MINUS
  SELECT srvr_id
  FROM uwclass.serv_inst);

SELECT * FROM TABLE(dbms_xplan.display);

DECLARE
 vClobIn  CLOB := 'SELECT DISTINCT srvr_id
FROM uwclass.expandv
WHERE srvr_id NOT IN (
  SELECT srvr_id
  FROM uwclass.expandv
  MINUS
  SELECT srvr_id
  FROM uwclass.serv_inst)';
 vClobOut CLOB;
BEGIN
  dbms_utility.expand_sql_text(vClobIn, vClobOut);
  dbms_output.put_line(vClobOut);
END;
/
CREATE VIEW uwclass.layer1 AS
SELECT * FROM uwclass.servers;

CREATE VIEW uwclass.layer2 AS
SELECT srvr_id, netaddress
FROM uwclass.layer1
WHERE status = 'Y';

CREATE VIEW uwclass.table_join AS
SELECT DISTINCT srvr_id
FROM uwclass.layer2
WHERE srvr_id NOT IN (
  SELECT srvr_id
  FROM uwclass. servers
  MINUS
  SELECT srvr_id
  FROM uwclass.serv_inst);

DECLARE
 vClobIn  CLOB := 'SELECT * FROM uwclass.table_join';
 vClobOut CLOB;
BEGIN
  dbms_utility.expand_sql_text(vClobIn, vClobOut);
  dbms_output.put_line(vClobOut);
END;
/

SELECT "A1"."SRVR_ID" "SRVR_ID" FROM (SELECT DISTINCT "A2"."SRVR_ID" "SRVR_ID" FROM (SELECT "A3"."SRVR_ID" "SRVR_ID","A3"."NETADDRESS" "NETADDRESS" FROM (SELECT "A4"."SRVR_ID"
"SRVR_ID","A4"."NETWORK_ID" "NETWORK_ID","A4"."STATUS" "STATUS","A4"."LATITUDE" "LATITUDE","A4"."LONGITUDE" "LONGITUDE","A4"."NETADDRESS" "NETADDRESS" FROM "UWCLASS"."SERVERS" "A4")
"A3" WHERE "A3"."STATUS"='Y') "A2" WHERE "A2"."SRVR_ID"<>ALL ( (SELECT "A7"."SRVR_ID" "SRVR_ID" FROM "UWCLASS"."SERVERS" "A7")MINUS (SELECT "A6"."SRVR_ID" "SRVR_ID" FROM
"UWCLASS"."SERV_INST" "A6"))) "A1"

PL/SQL procedure successfully completed.
set serveroutput on

DECLARE
 outVal CLOB;
BEGIN
  dbms_utility.expand_sql_text('SELECT * FROM all_procedures', outVal);
  dbms_output.put_line(outVal);
END;
/
SELECT "A1"."OWNER" "OWNER","A1"."OBJECT_NAME" "OBJECT_NAME","A1"."PROCEDURE_NAME" "PROCEDURE_NAME","A1"."OBJECT_ID" "OBJECT_ID","A1"."SUBPROGRAM_ID" "SUBPROGRAM_ID","A1"."OVERLOAD"
"OVERLOAD","A1"."OBJECT_TYPE" "OBJECT_TYPE","A1"."AGGREGATE" "AGGREGATE","A1"."PIPELINED" "PIPELINED","A1"."IMPLTYPEOWNER" "IMPLTYPEOWNER","A1"."IMPLTYPENAME"
"IMPLTYPENAME","A1"."PARALLEL" "PARALLEL","A1"."INTERFACE" "INTERFACE","A1"."DETERMINISTIC" "DETERMINISTIC","A1"."AUTHID" "AUTHID","A1"."RESULT_CACHE"
"RESULT_CACHE","A1"."ORIGIN_CON_ID" "ORIGIN_CON_ID","A1"."POLYMORPHIC" "POLYMORPHIC" FROM (SELECT "A2"."OWNER" "OWNER","A2"."OBJECT_NAME" "OBJECT_NAME","A2"."PROCEDURE_NAME"
"PROCEDURE_NAME","A2"."OBJECT_ID" "OBJECT_ID","A2"."SUBPROGRAM_ID" "SUBPROGRAM_ID","A2"."OVERLOAD" "OVERLOAD","A2"."OBJECT_TYPE" "OBJECT_TYPE","A2"."AGGREGATE"
"AGGREGATE","A2"."PIPELINED" "PIPELINED","A2"."IMPLTYPEOWNER" "IMPLTYPEOWNER","A2"."IMPLTYPENAME" "IMPLTYPENAME","A2"."PARALLEL" "PARALLEL","A2"."INTERFACE"
"INTERFACE","A2"."DETERMINISTIC" "DETERMINISTIC","A2"."AUTHID" "AUTHID","A2"."RESULT_CACHE" "RESULT_CACHE","A2"."ORIGIN_CON_ID" "ORIGIN_CON_ID","A2"."POLYMORPHIC" "POLYMORPHIC" FROM
"SYS"."INT$DBA_PROCEDURES" "A2" WHERE "A2"."OWNER"=SYS_CONTEXT('USERENV','CURRENT_USER') OR EXISTS (SELECT NULL "NULL" FROM (SELECT "A4"."PRIV_NUMBER" "PRIV_NUMBER","A4"."SCOPE"
"SCOPE","A4"."CON_ID" "CON_ID" FROM (SELECT "A5"."PRIV_NUMBER" "PRIV_NUMBER","A5"."SCOPE" "SCOPE","A5"."CON_ID" "CON_ID" FROM (SELECT "A6"."INST_ID" "INST_ID",(-"A6"."KZSPRPRV")
"PRIV_NUMBER",DECODE(BITAND("A6"."KZSPRFLG",7),1,'COMMON',2,'APPLICATION',4,'LOCAL',NULL) "SCOPE","A6"."CON_ID" "CON_ID" FROM "SYS"."X$KZSPR" "A6") "A5" WHERE
"A5"."INST_ID"=USERENV('INSTANCE')) "A4" WHERE "A4"."CON_ID"=0 OR "A4"."CON_ID"=4) "A8" WHERE "A8"."PRIV_NUMBER"=(-144) OR "A8"."PRIV_NUMBER"=(-141)) OR
OBJ_ID("A2"."OWNER","A2"."OBJECT_NAME","A2"."OBJECT_TYPE#","A2"."OBJECT_ID")=ANY (SELECT "A7"."OBJ#" "OBJ#" FROM "SYS"."OBJAUTH$" "A7" WHERE "A7"."GRANTEE#"=ANY (SELECT
"A9"."KZSROROL" "KZSROROL" FROM (SELECT "A3"."ADDR" "ADDR","A3"."INDX" "INDX","A3"."INST_ID" "INST_ID","A3"."CON_ID" "CON_ID","A3"."KZSROROL" "KZSROROL","A3"."KZSROFLG" "KZSROFLG"
FROM "SYS"."X$KZSRO" "A3" WHERE "A3"."CON_ID"=0 OR "A3"."CON_ID"=4) "A9") AND "A7"."PRIVILEGE#"=12)) "A1"

PL/SQL procedure successfully completed.
 
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

CREATE OR REPLACE FUNCTION test(inval VARCHAR2) RETURN VARCHAR2 IS
 shortstr VARCHAR2(5);
BEGIN
  shortstr := inval;
  RETURN shortstr;
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line(dbms_utility.format_error_stack());
END;
/

set serveroutput on

SELECT test('ABCDE') FROM dual;
SELECT test('ABCDEF') FROM dual;
 
GET_COLUMN_LIMIT (new 23c)
Returns the maximum number of columns that can be supported in a heap table dbms_utility.get_column_limit RETURN NUMBER;
SELECT dbms_utility.get_column_limit;

 GET_COLUMN_LIMIT
-----------------
             1000
 
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

Deprecated as of 12.2: Oracle recommends querying DBA_DEPENDENCIES		 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
Returns the Endianness value of the operating system: 1for big and 2 for little 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;
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 (Deprecated in 12.2)
Returns the value of a specified initialization parameter

Deprecated as of 12.2: Oracle recommends querying v$parameter directly		 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;
 intval BINARY_INTEGER;
 pname  v_$parameter.name%TYPE;
 strval v_$parameter.value%TYPE;
 x      BINARY_INTEGER;
BEGIN
  pname := 'optimizer_adaptive_plans';
  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;
/
IntVal: 1

PL/SQL procedure successfully completed.

SELECT type, value
FROM gv$parameter
WHERE name = 'optimizer_adaptive_plans';

 TYPE VALUE
----- ------
    1 TRUE

DECLARE
 i      BINARY_INTEGER;
 intval BINARY_INTEGER;
 pname  v_$parameter.name%TYPE;
 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;
/
DECLARE
*
ERROR at line 1:
ORA-20000: get_parameter_value: invalid or unsupported parameter "utl_file_dir"
ORA-06512: at "SYS.DBMS_UTILITY", line 140
ORA-06512: at "SYS.DBMS_UTILITY", line 130
ORA-06512: at line 9
 
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;
/
 
INIT_NUMBER_ARRAY
Used to define a constant of type number_array which serves as default value when an input parameter of type number_array is not supplied. The function is not needed when an associative array constructor is provided by the PL/SQL. dbms_utility.init_number_array RETURN number_array;
DECLARE
 nArray dbms_utility.number_array := dbms_utility.init_number_array;
BEGIN
  nArray(1) := 42;
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);
conn uwclass/uwclass@pdbdev

CREATE TABLE test (
testcol VARCHAR2(20));

CREATE OR REPLACE PROCEDURE testproc AUTHID CURRENT_USER 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 submitted by Stan Hartin for the library
conn uwclass/uwclass@pdbdev
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, 136)
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;
conn sys@pdbdev as sysdba

SELECT sys_context('USERENV', 'CURRENT_SCHEMA') FROM dual;

alter session set current_schema=UWCLASS;

SELECT sys_context('USERENV', 'CURRENT_SCHEMA') FROM dual;

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;

PORT_STRING
--------------------
x86_64/Linux 2.4.xx
 
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);
conn uwclass/uwclass@pdbdev

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 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'));
conn uwclass/uwclass@pdbdev

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';
 
WAIT_ON_PENDING_DML
Waits until all transactions (other than the caller's own) that have locks on the listed tables and began prior to the specified SCN have either committed or been rolled back dbms_utility.wait_on_pending_dml(
tables  IN     VARCHAR2,
timeout IN     BINARY_INTEGER,
scn     IN OUT NUMBER)
RETURN BOOLEAN;

Note:
$if utl_ident.is_oracle_server $then
  function wait_on_pending_dml(tables IN VARCHAR2, timeout IN BINARY INTEGER,
  scn in out number)
  return boolean;
$else
  /* wait_on_pending_dml is not supported */
$end
set serveroutput on

DECLARE
 outscn NUMBER;
BEGIN
  IF dbms_utility.wait_on_pending_dml('UWCLASS.SERVERS,UWCLASS.SERV_INST', 2, outscn) THEN
    dbms_output.put_line('TRUE');
  ELSE
    dbms_output.put_line('FALSE');
  END IF;
END;
/

Related Topics
Built-in Functions
Built-in Packages
DBMS_ODCI
Editioning Demo 3
Exception Handling
Partitioned Tables
PRVT_UTILITY
Real Application Clusters (RAC)
UTL_CALL_STACK
UTL_RECOMP
UTL_RECOMP
What's New In 21c
What's New In 23c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx