Table of Contents
This chapter briefly introduces the DBMS_UTILITY package, and describes how to use the procedures and functions of the package.
This section describes the string array type provided by the DBMS_UTILITY package.
This section describes the procedures and functions provided by the DBMS_UTILITY package, in alphabetical order.
Analyzes a comma-delimited list of table names, and converts it to an index table that contains the table names.
Details about the COMMA_TO_TABLE procedure are as follows:
Prototype
DBMS_UTILITY.COMMA_TO_TABLE
(
list IN VARCHAR2,
tablen OUT BINARY_INTEGER,
tab OUT UNCL_ARRAY
);
Parameter
| Parameter | Description |
|---|---|
| list | Comma-delimited list of table names. The table names are specified as: a [. b [. c]][ @ d ] where a, b, c, and d are identifiers that are optionally grouped using quotes. |
| tablen | Number of names to create in the table. |
| tab | Index table to store the table names. |
Example
declare
tab_len binary_integer;
tab dbms_utility.uncl_array;
begin
DBMS_UTILITY.COMMA_TO_TABLE('a,"b".c,d.e.f,g.h@i', tab_len, tab);
for i in 1..tab_len loop
dbms_output.put_line(tab(i));
end loop;
end;
/
Returns instance number of the current instance.
Details about the CURRENT_INSTANCE function are as follows.
Prototype
DBMS_UTILTIY.CURRENT_INSTANCE RETURN NUMBER;
Example
SELECT dbms_utility.current_instance FROM dual;
Returns the contents of the call stack as a character string. The return value cannot exceed 32,767 bytes.
Details about the COMMA_TO_TABLE function are as follows:
Prototype
DBMS_UTILITY.FORMAT_CALL_STACK RETURN VARCHAR2;
Example
create or replace procedure proc3
as
begin
dbms_output.put_line (dbms_utility.format_call_stack);
end;
/
create or replace procedure proc2
as
begin
proc3;
end;
/
create or replace procedure proc1
as
begin
proc2;
end;
/
begin
proc1;
end;
/
-- ----- PSM Call Stack -----
-- object line object
-- handle number name
-- 0x7f50fa3943a8 4 procedure SYS.PROC3
-- 0x7f50fa3942d0 4 procedure SYS.PROC2
-- 0x7f50fa3941f8 4 procedure SYS.PROC1
-- 0x7f50fa394120 2 anonymous block
Returns a BACKTRACE error message string. If the returned string exceeds 712 bytes, it may return an incorrect result.
Details about the FORMAT_ERROR_BACKTRACE function are as follows:
Prototype
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE RETURN VARCHAR2;
Example
CREATE OR REPLACE PROCEDURE BACKTRACE_EXAMPLE_PR0
IS
variable positive;
BEGIN
variable := -1;
END;
/
CREATE OR REPLACE PROCEDURE BACKTRACE_EXAMPLE_PR1 IS
BEGIN
BACKTRACE_EXAMPLE_PR0 ();
END;
/
CREATE OR REPLACE PROCEDURE BACKTRACE_EXAMPLE_PR2 IS
BEGIN
BACKTRACE_EXAMPLE_PR1 ();
END;
/
CREATE OR REPLACE PROCEDURE BACKTRACE_EXAMPLE_PR3 IS
BEGIN
BACKTRACE_EXAMPLE_PR2 ();
END;
/
CREATE OR REPLACE PROCEDURE BACKTRACE_EXAMPLE_PR4 IS
BEGIN BACKTRACE_EXAMPLE_PR3 ();
END;
/
CREATE OR REPLACE PROCEDURE BACKTRACE_EXAMPLE_PR5 IS
BEGIN BACKTRACE_EXAMPLE_PR4 ();
END;
/
CREATE OR REPLACE PROCEDURE BACKTRACE_EXAMPLE_PR6 IS
BEGIN
BACKTRACE_EXAMPLE_PR5 ();
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE (DBMS_UTILITY.FORMAT_ERROR_BACKTRACE() );
END;
/
begin
BACKTRACE_EXAMPLE_PR6 ();
end;
/
-- TBR-15163: Unhandled exception at SYS.BACKTRACE_EXAMPLE_PR0, line 5.
-- TBR-15163: Unhandled exception at SYS.BACKTRACE_EXAMPLE_PR1, line 3.
-- TBR-15163: Unhandled exception at SYS.BACKTRACE_EXAMPLE_PR2, line 3.
-- TBR-15163: Unhandled exception at SYS.BACKTRACE_EXAMPLE_PR3, line 3.
-- TBR-15163: Unhandled exception at SYS.BACKTRACE_EXAMPLE_PR4, line 2.
-- TBR-15163: Unhandled exception at SYS.BACKTRACE_EXAMPLE_PR5, line 2.
-- TBR-15163: Unhandled exception at SYS.BACKTRACE_EXAMPLE_PR6, line 3.
Returns error stack details as a string. If the returned string exceeds 712 bytes, it may return an incorrect result.
Details about the FORMAT_ERROR_STACK function are as follows:
Prototype
DBMS_UTILITY.FORMAT_ERROR_STACK RETURN VARCHAR2;
Example
CREATE OR REPLACE PROCEDURE CALLSTACK_EXAMPLE_PR0
IS
variable positive;
BEGIN
variable := -1;
END;
/
CREATE OR REPLACE PROCEDURE CALLSTACK_EXAMPLE_PR1 IS
BEGIN
CALLSTACK_EXAMPLE_PR0 ();
END;
/
CREATE OR REPLACE PROCEDURE CALLSTACK_EXAMPLE_PR2 IS
BEGIN
CALLSTACK_EXAMPLE_PR1 ();
END;
/
begin
CALLSTACK_EXAMPLE_PR2 ();
exception when others then
dbms_output.put_line (DBMS_UTILITY.FORMAT_ERROR_STACK);
dbms_output.put_line ('exception handled.');
end;
/
--TBR-15035: Number is out of range.
--exception handled.
Gets the hash value of the given string.
Details about the GET_HASH_VALUE function are as follows:
Prototype
DBMS_UTILITY.GET_HASH_VALUE
(
name IN VARCHAR2,
base IN NUMBER,
hash_size IN NUMBER
)
RETURN NUMBER;
Parameters
| Parameter | Description |
|---|---|
| name | String to compute the hash value for. |
| base | Starting value to generate hash values at. |
| hash_size | Hash table size (must be >= 1). |
Example
declare
hash_value number;
begin
hash_value := DBMS_UTILITY.GET_HASH_VALUE('Oleg', 1000, 100);
dbms_output.put_line(hash_value);
end;
/
Gets the defined initial parameter value.
Details about the GET_PARAMETER_VALUE function are as follows:
Prototype
DBMS_UTILITY.GET_PARAMETER_VALUE
(
parnam IN VARCHAR2,
intval IN OUT BINARY_INTEGER,
strval IN OUT VARCHAR2
)
RETURN BINARY_INTEGER;
Parameters
| Parameter | Description |
|---|---|
| parnam | Parameter name to compute the value for. |
| intval | Value of integer parameter or length value of string parameter. |
| strval | Value of string parameter. |
Example
DECLARE
parnam VARCHAR2(256);
intval BINARY_INTEGER;
strval VARCHAR2(256);
partyp BINARY_INTEGER;
BEGIN
partyp := dbms_utility.get_parameter_value('MAX_SESSION_COUNT',
intval, strval);
dbms_output.put('parameter value is: ');
IF partyp = 1 THEN
dbms_output.put_line(strval);
ELSE
dbms_output.put_line(intval);
END IF;
IF partyp = 1 THEN
dbms_output.put('parameter value length is: ');
dbms_output.put_line(intval);
END IF;
dbms_output.put('parameter type is: ');
IF partyp = 1 THEN
dbms_output.put_line('string');
ELSE
dbms_output.put_line('integer');
END IF;
END;
Gets the current time in hundredths of a second. This function is called at the beginning and end of a process to measure the elapsed time.
Details about the GET_TIME function are as follows:
Prototype
DBMS_UTILITY.GET_TIME RETURN NUMBER;
Example
declare
s number;
e number;
c sys_refcursor;
begin
s := DBMS_UTILITY.GET_TIME;
open c for select * from all_objects;
close c;
e := DBMS_UTILITY.GET_TIME;
dbms_output.put_line(e - s);
end;
/