DBMS_UTILITY Package In Oracle

DBMS_UTILITY Package In Oracle

INTRODUCTION:

DBMS_UTILITY is a oracle supplied package which is a collection of many stand alone procedures which are used for various purposes.

STRUCTURE:

DBMS_UTILITY package defines a single record type and few table types.

RECORD TYPE:

RECORD TYPE	TABLE TYPE
INSTANCE_RECORD	DBLINK_ARRAY
	INDEX_TABLE_TYPE
	INSTANCE_TABLE
	LNAME_ARRAY
	NAME_ARRAY
	NUMBER_ARRAY
	UNCL_ARRAY

INSTANCE_RECORD:

TYPE INSTANCE_RECORD IS RECORD
(
 INST_NUMBER    NUMBER,
 INST_NAME      VARCHAR2(60)
);

DBLINK_ARRAY:

TYPE DBLINK_ARRAY IS TABLE OF VARCHAR2(128) INDEX BY BINARY_INTEGER;

INDEX_TABLE_TYPE:

TYPE INDEX_TABLE_TYPE IS TABLE OF BINARY_INTEGER INDEX BY BINARY_INTEGER;

INSTANCE_TABLE:

TYPE INSTANCE_TABLE IS TABLE OF INSTANCE_RECORD INDEX BY BINARY_INTEGER;

LNAME_ARRAY:

TYPE LNAME_ARRAY IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;

NAME_ARRAY:

TYPE NAME_ARRAY IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;

NUMBER_ARRAY:

TYPE NUMBER_ARRAY IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;

UNCL_ARRAY:

TYPE UNCL_ARRAY IS TABLE OF VARCHAR2(277) INDEX BY BINARY_INTEGER;

KEY SUBPROGRAMS OF DBMS_UTILITY PACKAGE:

• DB_VERSION PROCEDURE
• GET_CPU_TIME FUNCTION
• GET_TIME FUNCTION
• GET_DEPENDENCY PROCEDURE
• GET_PARAMETER_VALUE FUNCTION
• OLD_CURRENT_SCHEMA FUNCTION
• OLD_CURRENT_USER FUNCTION
• EXEC_DDL_STATEMENT PROCEDURE
• EXPAND_SQL_TEXT PROCEDURE
• INVALIDATE PROCEDURE
• VALIDATE PROCEDURE
• WAIT_ON_PENDING_DML FUNCTION
• ANALYZE_SCHEMA PROCEDURE
• COMPILE_SCHEMA PROCEDURE
• ANALYZE_PART_OBJECT PROCEDURE
• COMMA_TO_TABLE PROCEDURE
• TABLE_TO_COMMA PROCEDURE
• FORMAT_CALL_STACK FUNCTION
• FORMAT_ERROR_STACK FUNCTION
• FORMAT_ERROR_BACKTRACE FUNCTION

DB_VERSION PROCEDURE:

Returns the version information of database.

SYNTAX:

DBMS_UTILITY.DB_VERSION(VERSION OUT VARCHAR2,
                        COMPATIBILITY OUT VARCHAR2);

DECLARE
  V_VERSION VARCHAR2(100);
  V_COMPATIBILITY VARCHAR2(100);
BEGIN
  DBMS_UTILITY.DB_VERSION (VERSION => V_VERSION,
  COMPATIBILITY => V_COMPATIBILITY);
  DBMS_OUTPUT.PUT_LINE('Version -> '||V_VERSION||' Compatibility -> '||V_COMPATIBILITY);
END;
/

GET_CPU_TIME FUNCTION:

• This function returns a measure of current CPU processing time in hundredth of a second.
• This is the CPU processing time between two points and not the total processing time between those points.

SYNTAX:

DBMS_UTILITY.GET_CPU_TIME
RETURN NUMBER;

DECLARE
  CURSOR C1
  IS
  SELECT * FROM EMPLOYEES;
  V_EMP_REC C1%ROWTYPE;
  T1 NUMBER;
  T2 NUMBER;
BEGIN
  T1 := DBMS_UTILITY.GET_CPU_TIME;
  OPEN C1;
  LOOP
    FETCH C1 INTO V_EMP_REC;
    EXIT WHEN C1%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE_ID = '||V_EMP_REC.EMPLOYEE_ID||
                         ' NAME = '||V_EMP_REC.FIRST_NAME||' '||V_EMP_REC.LAST_NAME);
  END LOOP;
  T2 := DBMS_UTILITY.GET_CPU_TIME;
  DBMS_OUTPUT.PUT_LINE('TOTAL CPU TIME = '||(T2 - T1));
END;
/

Total time taken is 0.002 sec, while total CPU time is 0. It means the block is executed by CPU almost in no time.

GET_TIME FUNCTION:

• This function returns the current time in hundredth's of a second.
• It returns the total elapsed time between two points.

SYNTAX:

DBMS_UTILITY.GET_TIME
RETURN NUMBER;

DECLARE
  CURSOR C1
  IS
  SELECT * FROM EMPLOYEES;
  V_EMP_REC C1%ROWTYPE;
  T1 NUMBER;
  T2 NUMBER;
BEGIN
  T1 := DBMS_UTILITY.GET_TIME;
  OPEN C1;
  LOOP
    FETCH C1 INTO V_EMP_REC;
    EXIT WHEN C1%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE_ID = '||V_EMP_REC.EMPLOYEE_ID||
                         ' NAME = '||V_EMP_REC.FIRST_NAME||' '||V_EMP_REC.LAST_NAME);
  END LOOP;
  T2 := DBMS_UTILITY.GET_TIME;
  DBMS_OUTPUT.PUT_LINE('TOTAL CPU TIME = '||(T2 - T1));
END;
/

Task completed in 0.006 sec. If we take 100th of a second then total time taken is 0.006*100 = 0.6. If its rounded then it will become 1.

Lets take another example for more better understanding.

DECLARE
  CURSOR C1
  IS
  SELECT * FROM EMPLOYEES;
  V_EMP_REC C1%ROWTYPE;
  T1 NUMBER;
  T2 NUMBER;
BEGIN
  T1 := DBMS_UTILITY.GET_TIME;
  OPEN C1;
  LOOP
    FETCH C1 INTO V_EMP_REC;
    EXIT WHEN C1%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE_ID = '||V_EMP_REC.EMPLOYEE_ID||
                         ' NAME = '||V_EMP_REC.FIRST_NAME||' '||V_EMP_REC.LAST_NAME);
    DBMS_LOCK.SLEEP(0.1);
  END LOOP;
  T2 := DBMS_UTILITY.GET_TIME;
  DBMS_OUTPUT.PUT_LINE('TOTAL CPU TIME = '||(T2 - T1));
END;
/

GET_DEPENDENCY PROCEDURE:

This procedure displays the dependencies of the object passed.

SYNTAX:

DBMS_UTILITY.GET_DEPENDENCY(TYPE VARCHAR2,
SCHEMA VARCHAR2,
NAME VARCHAR2);

• TYPE –> Object Type, e.g. Table
• SCHEMA –> Name of the Schema
• NAME –> Name of the Object

EXEC DBMS_UTILITY.GET_DEPENDENCY('TABLE', 'HR', 'DEPARTMENTS');

GET_PARAMETER_VALUE FUNCTION:

• This function returns the value of the specified init.ora parameter.
• To execute the function you must have execute privileges of V$PARAMETER dynamic view.

SYNTAX:

DBMS_UTILITY.GET_PARAMETER_VALUE
(PARAM VARCHAR2,
INTVAL IN OUT BINARY_INTEGER,
STRVAL IN OUT VARCHAR2,
LISTNO BINARY_INTEGER DEFAULT 1
) RETURN BINARY_INTEGER;

PARAMETER DESCRIPTIONS:

• PARAM –> Name of the parameter
• INTVAL –> Value of an integer parameter or length of a string parameter
• STRVAL –> Value of a string parameter
• LISTNO –> List item number

RETURN TYPE:

• 0 if the parameter is an integer or boolean parameter
• 1 if the parameter is a string parameter.

CONN SYS AS SYSDBA/MANAGER;
GRANT SELECT ON V_$PARAMETER TO HR;

CONN HR/HR;

DECLARE
  V_ID NUMBER;
  V_STR VARCHAR2(40);
  V_RETURN NUMBER;
BEGIN
  V_RETURN := DBMS_UTILITY.GET_PARAMETER_VALUE('DB_NAME', V_ID, V_STR);
  DBMS_OUTPUT.PUT_LINE('DB_NAME = '||V_STR);
  V_RETURN := DBMS_UTILITY.GET_PARAMETER_VALUE('GLOBAL_DATABASE_NAME', V_ID, V_STR);
  DBMS_OUTPUT.PUT_LINE('GLOBAL_DATABASE_NAME = '||V_STR);
  V_RETURN := DBMS_UTILITY.GET_PARAMETER_VALUE('OPTIMIZER_MODE', V_ID, V_STR);
  DBMS_OUTPUT.PUT_LINE('OPTIMIZER_MODE = '||V_STR);
  V_RETURN := DBMS_UTILITY.GET_PARAMETER_VALUE('RESULT_CACHE_MODE', V_ID, V_STR);
  DBMS_OUTPUT.PUT_LINE('RESULT_CACHE_MODE = '||V_STR);
  V_RETURN := DBMS_UTILITY.GET_PARAMETER_VALUE('MAX_STRING_SIZE', V_ID, V_STR);
  DBMS_OUTPUT.PUT_LINE('MAX_STRING_SIZE = '||V_STR);
  V_RETURN := DBMS_UTILITY.GET_PARAMETER_VALUE('OPEN_CURSORS', V_ID, V_STR);
  DBMS_OUTPUT.PUT_LINE('OPEN_CURSORS = '||V_ID);
  V_RETURN := DBMS_UTILITY.GET_PARAMETER_VALUE('DDL_LOCK_TIMEOUT', V_ID, V_STR);
  DBMS_OUTPUT.PUT_LINE('DDL_LOCK_TIMEOUT = '||V_ID);
END;
/

OLD_CURRENT_SCHEMA FUNCTION:

This function returns the session value from SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA').

SELECT DBMS_UTILITY.OLD_CURRENT_SCHEMA FROM DUAL;

OLD_CURRENT_USER FUNCTION:

This function returns the session value of SYS_CONTEXT('USERENV', 'CURRENT_USER');

SELECT DBMS_UTILITY.OLD_CURRENT_USER FROM DUAL;

EXEC_DDL_STATEMENT PROCEDURE:

This procedure execute the DDL statement passed.

SYNTAX:

DBMS_UTILITY.EXEC_DDL_STATEMENT(PARSE_STRING VARCHAR2);

EXEC DBMS_UTILITY.EXEC_DDL_STATEMENT('CREATE TABLE EMPLOYEE_1 AS SELECT * FROM EMPLOYEES');

SELECT * FROM EMPLOYEE_1;

SELECT COUNT(1) COUNT FROM EMPLOYEE_1;

EXPAND_SQL_TEXT PROCEDURE:

This procedure returns the DDL statement of the view associated in the select query passed.

SYNTAX:

DBMS_UTILITY.EXPAND_SQL_TEXT(INPUT_SQL_TEXT CLOB,
                             OUTPUT_SQL_TEXT CLOB
                            );

• INPUT_SQL_TEXT –> Input sql query
• OUTPUT_SQL_TEXT –> View expanded query

CREATE OR REPLACE VIEW EMP_DEPT_COMB_VIEW
AS
SELECT A.EMPLOYEE_ID, A.FIRST_NAME, A.LAST_NAME, A.DEPARTMENT_ID, B.DEPARTMENT_NAME, B.LOCATION_ID
FROM EMPLOYEES A, DEPARTMENTS B
WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
ORDER BY 1;

DECLARE
  V_LOB CLOB;
BEGIN
  DBMS_UTILITY.EXPAND_SQL_TEXT(INPUT_SQL_TEXT => 'SELECT * FROM EMP_DEPT_COMB_VIEW',
                               OUTPUT_SQL_TEXT => V_LOB);
  DBMS_OUTPUT.PUT_LINE('VIEW QUERY -> '||V_LOB);
END;
/

PL/SQL procedure successfully completed.

VIEW QUERY -> SELECT "A1"."EMPLOYEE_ID" "EMPLOYEE_ID","A1"."FIRST_NAME" "FIRST_NAME","A1"."LAST_NAME" "LAST_NAME","A1"."DEPARTMENT_ID" "DEPARTMENT_ID","A1"."DEPARTMENT_NAME" "DEPARTMENT_NAME","A1"."LOCATION_ID" "LOCATION_ID" FROM  (SELECT "A3"."EMPLOYEE_ID" "EMPLOYEE_ID","A3"."FIRST_NAME" "FIRST_NAME","A3"."LAST_NAME" "LAST_NAME","A3"."DEPARTMENT_ID" "DEPARTMENT_ID","A2"."DEPARTMENT_NAME" "DEPARTMENT_NAME","A2"."LOCATION_ID" "LOCATION_ID" FROM "HR"."EMPLOYEES" "A3","HR"."DEPARTMENTS" "A2" WHERE "A3"."DEPARTMENT_ID"="A2"."DEPARTMENT_ID" ORDER BY "A3"."EMPLOYEE_ID") "A1"

After formatting its look like:

SELECT "A1"."EMPLOYEE_ID" "EMPLOYEE_ID",
       "A1"."FIRST_NAME" "FIRST_NAME",
       "A1"."LAST_NAME" "LAST_NAME",
       "A1"."DEPARTMENT_ID" "DEPARTMENT_ID",
       "A1"."DEPARTMENT_NAME" "DEPARTMENT_NAME",
       "A1"."LOCATION_ID" "LOCATION_ID"
FROM
    (SELECT "A3"."EMPLOYEE_ID" "EMPLOYEE_ID",
            "A3"."FIRST_NAME" "FIRST_NAME",
            "A3"."LAST_NAME" "LAST_NAME",
            "A3"."DEPARTMENT_ID" "DEPARTMENT_ID",
            "A2"."DEPARTMENT_NAME" "DEPARTMENT_NAME",
            "A2"."LOCATION_ID" "LOCATION_ID"
     FROM "HR"."EMPLOYEES" "A3",
          "HR"."DEPARTMENTS" "A2"
     WHERE "A3"."DEPARTMENT_ID"="A2"."DEPARTMENT_ID"
     ORDER BY "A3"."EMPLOYEE_ID"
    ) "A1"

INVALIDATE PROCEDURE:

• This procedure invalidates any database object and modifies its plsql compiler parameter setting.
• It also invalidate the objects which are directly or indirectly depends on the object being invalidated.

SYNTAX:

DBMS_UTILITY.INVALIDATE(P_OBJECT_ID NUMBER,
                        P_PLSQL_OBJECT_SETTINGS VARCHAR2 DEFAULT NULL,
                        P_OPTION_FLAGS PLS_INTEGER DEFAULT 0
);

CREATE OR REPLACE FUNCTION FUN_IS_EMP_EXISTS(P_EMPID EMPLOYEES.EMPLOYEE_ID%TYPE)
RETURN BOOLEAN
IS
  V_COUNT NUMBER := 0;
BEGIN
  SELECT COUNT(1)
  INTO V_COUNT
  FROM EMPLOYEES
  WHERE EMPLOYEE_ID = P_EMPID;
  --
  IF V_COUNT > 0 THEN
    RETURN TRUE;
  ELSE
    RETURN FALSE;
  END IF;
END;
/

CREATE OR REPLACE PROCEDURE SP_INVALIDATE_TEST
IS
  CURSOR C1
  IS
  SELECT * FROM EMP_DEPT_COMB_VIEW;
  --
  V_EMP_REC C1%ROWTYPE;
  V_COUNT NUMBER := 0;
BEGIN
  OPEN C1;
  LOOP
    FETCH C1 INTO V_EMP_REC;
    EXIT WHEN C1%NOTFOUND;
    IF FUN_IS_EMP_EXISTS(V_EMP_REC.EMPLOYEE_ID) THEN
      DBMS_OUTPUT.PUT_LINE('EMPLOYEE_ID '||V_EMP_REC.EMPLOYEE_ID||' EXITS');
    ELSE
      DBMS_OUTPUT.PUT_LINE('EMPLOYEE_ID '||V_EMP_REC.EMPLOYEE_ID||' DOES NOT EXITS');
    END IF;
  END LOOP;
  CLOSE C1;
END;
/

SELECT OBJECT_NAME, OBJECT_ID, OBJECT_TYPE, STATUS
FROM USER_OBJECTS
WHERE OBJECT_NAME IN ('FUN_IS_EMP_EXISTS', 'SP_INVALIDATE_TEST');

EXEC DBMS_UTILITY.INVALIDATE(74814, 'PLSQL_CODE_TYPE = NATIVE');

SELECT OBJECT_NAME, OBJECT_ID, OBJECT_TYPE, STATUS
FROM USER_OBJECTS
WHERE OBJECT_NAME IN ('FUN_IS_EMP_EXISTS', 'SP_INVALIDATE_TEST');

VALIDATE PROCEDURE:

This procedure makes invalid object as valid.

SYNTAX:

DBMS_UTILITY.VALIDATE(OBJECT_ID NUMBER);

DBMS_UTILITY.VALIDATE(OWNER VARCHAR2,
                      OBJECT_NAME VARCHAR2,
                      NAMESPACE NUMBER,
                      EDITION_NAME := SYS_CONTEXT('USERENV', 'CURRENT_EDITION')
                     );

PARAMETER	DESCRIPTION
OWNER	OWNER of ALL_OBJECTS
OBJECT_NAME	Name of the object
NAMESPACE	Namespace of the object. Equivalent values are: 1 -> TABLE/PROCEDURE/TYPE 2 -> BODY 3 -> TRIGGER 4 -> INDEX 5 -> CLUSTER 8 -> LOB 9 -> DIRECTORY 10 -> QUEUE 11 -> REPLICATION OBJECT GROUP 12 -> REPLICATION PROPAGATOR 13 -> JAVA SOURCE 14 -> JAVA RESOURCE 58 -> (DATA MINING) MODEL
EDITION_NAME	Not currently operable. (Reserved for future use)

Lets continue with the previous example.

EXEC DBMS_UTILITY.VALIDATE(74813);

Try other format:

EXEC DBMS_UTILITY.INVALIDATE(74814, 'PLSQL_CODE_TYPE = NATIVE');

EXEC DBMS_UTILITY.VALIDATE('HR', 'SP_INVALIDATE_TEST', 1);

WAIT_ON_PENDING_DML FUNCTION:

This function waits until all the transactions that have lock on the listed tables and began prior to specified SCN have either committed or been rolled back.

SYNTAX:

DBMS_UTILITY.WAIT_ON_PENDING_DML(TABLES VARCHAR2,
                                 TIMEOUT BINARY_INTEGER,
                                 SCN IN OUT NUMBER)
RETURN BOOLEAN;

CREATE TABLE EMPLOYEES_SCN
AS
SELECT * FROM EMPLOYEES
WHERE 1 = 2;

SESSION 1:

BEGIN
  FOR I IN (SELECT * FROM EMPLOYEES)
  LOOP
    INSERT INTO EMPLOYEES_SCN(EMPLOYEE_ID, 
                              FIRST_NAME, 
                              LAST_NAME, 
                              EMAIL, 
                              PHONE_NUMBER, 
                              HIRE_DATE, 
                              JOB_ID, 
                              SALARY,      
                              COMMISSION_PCT, 
                              MANAGER_ID, 
                              DEPARTMENT_ID
                             )
    VALUES(I.EMPLOYEE_ID, 
           I.FIRST_NAME, 
           I.LAST_NAME, 
           I.EMAIL, 
           I.PHONE_NUMBER, 
           I.HIRE_DATE, 
           I.JOB_ID, 
           I.SALARY, 
           I.COMMISSION_PCT, 
           I.MANAGER_ID, 
           I.DEPARTMENT_ID
          );
    DBMS_LOCK.SLEEP(.5);
  END LOOP;
END;
/

SESSION 2:

DECLARE
  V_RETURN BOOLEAN;
  V_SCN NUMBER;
BEGIN
  V_RETURN := DBMS_UTILITY.WAIT_ON_PENDING_DML(TABLES => 'EMPLOYEES_SCN',
                                               TIMEOUT => 20,
                                               SCN => V_SCN);
  DBMS_OUTPUT.PUT_LINE('SCN = '||V_SCN);
  IF V_RETURN THEN
    DBMS_OUTPUT.PUT_LINE('TRUE');
  ELSE
    DBMS_OUTPUT.PUT_LINE('FALSE');
  END IF;
END;
/

After committing the SESSION 1, rerun the block of SESSION 2.

ANALYZE_SCHEMA PROCEDURE:

This procedure analyzes all the tables, clusters and indexes in a database.

SYNTAX:

DBMS_UTILITY.ANALYZE_SCHEMA(SCHEMA VARCHAR2,
                            METHOD VARCHAR2,
                            ESTIMATE_ROWS NUMBER DEFAULT NULL,
                            ESTIMATE_PERCENT NUMBER DEFAULT NULL,
                            METHOD_OPT VARCHAR2 DEFAULT NULL
                           );

EXEC DBMS_UTILITY.ANALYZE_SCHEMA('HR', 'COMPUTE');

EXEC DBMS_UTILITY.ANALYZE_SCHEMA('HR', 'DELETE');

EXEC DBMS_UTILITY.ANALYZE_SCHEMA('HR', 'ESTIMATE', ESTIMATE_ROWS => 1000);

EXEC DBMS_UTILITY.ANALYZE_SCHEMA('HR', 'ESTIMATE', ESTIMATE_PERCENT => 25);

COMPILE_SCHEMA PROCEDURE:

This procedure compiles all the procedure, functions, packages, views and triggers in the specified schema.

SYNTAX:

DBMS_UTILITY.COMPILE_SCHEMA(SCHEMA VARCHAR2,
                            COMPILE_ALL BOOLEAN DEFAULT TRUE,
                            REUSE_SETTINGS BOOLEAN DEFAULT FALSE
                           );

SELECT * FROM USER_OBJECTS
WHERE STATUS = 'INVALID';

EXEC DBMS_UTILITY.COMPILE_SCHEMA('HR', FALSE);

SELECT * FROM USER_OBJECTS
WHERE STATUS = 'INVALID';

EXEC DBMS_UTILITY.COMPILE_SCHEMA('HR');

ANALYZE_PART_OBJECT PROCEDURE:

This procedure is used to analyze partition tables or indexes.

SYNTAX:

DBMS_UTILITY.ANALYZE_PART_OBJECT(SCHEMA VARCHAR2 DEFAULT NULL,
                                 OBJECT_NAME VARCHAR2 DEFAULT NULL,
                                 OBJECT_TYPE CHAR DEFAULT 'T',
                                 COMMAND_TYPE CHAR DEFAULT 'E',
                                 COMMAND_OPT VARCHAR2 DEFAULT NULL,
                                 SAMPLE_CLAUSE VARCHAR2 DEFAULT
                                'sample 5 percent');

CREATE TABLE EMP_PART
(EMPLOYEE_ID NUMBER(6),
FIRST_NAME VARCHAR2(30),
LAST_NAME VARCHAR2(30),
SALARY NUMBER)
PARTITION BY RANGE (SALARY)
(PARTITION P1 VALUES LESS THAN (1000),
PARTITION P2 VALUES LESS THAN (2000),
PARTITION P3 VALUES LESS THAN (3000)
);

CONN SYS AS SYSDBA/MANAGER123;
GRANT CREATE JOB TO HR;

CONN HR/HR;
EXEC DBMS_UTILITY.ANALYZE_PART_OBJECT('HR', 'EMP_PART', 'T', 'C');

COMMA_TO_TABLE PROCEDURE:

This procedure converts a comma separated list of names into plsql table of names.

SYNTAX:

DBMS_UTILITY.COMMA_TO_TABLE(LIST VARCHAR2,
                            TABLEN OUT BINARY_INTEGER,
                            TAB OUT UNCL_ARRAY);

DBMS_UTILITY.COMMA_TO_TABLE(LIST VARCHAR2,
                            TABLEN OUT BINARY_INTEGER,
                            TAB OUT LNAME_ARRAY);

SELECT LISTAGG(FIRST_NAME, ',') WITHIN GROUP (ORDER BY FIRST_NAME) NAME_TAB
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 30;

DECLARE
  CURSOR EMP_CUR
  IS
  SELECT LISTAGG(FIRST_NAME, ',') WITHIN GROUP (ORDER BY FIRST_NAME) NAME_TAB
  FROM EMPLOYEES
  WHERE DEPARTMENT_ID = 30
  ORDER BY FIRST_NAME;
  --
  V_CUR EMP_CUR%ROWTYPE;
  V_TABLEN BINARY_INTEGER;
  V_TAB DBMS_UTILITY.UNCL_ARRAY;
BEGIN
  OPEN EMP_CUR;
  LOOP
    FETCH EMP_CUR INTO V_CUR;
    EXIT WHEN EMP_CUR%NOTFOUND;
    DBMS_UTILITY.COMMA_TO_TABLE(LIST => V_CUR.NAME_TAB,
                                TABLEN => V_TABLEN,
                                TAB => V_TAB
                               );
    FOR I IN 1..V_TABLEN
    LOOP
      DBMS_OUTPUT.PUT_LINE('NAME = '||V_TAB(I));
    END LOOP;
  END LOOP;
  CLOSE EMP_CUR;
END;
/

TABLE_TO_COMMA PROCEDURE:

This procedure converts a PLSQL table of names into a comma separated list of names.

SYNTAX:

DBMS_UTILITY.TABLE_TO_COMMA(TAB UNCL_ARRAY,
                            TABLEN OUT BINARY_INTEGER,
                            LIST OUT VARCHAR2
                           );
DBMS_UTILITY.TABLE_TO_COMMA(TAB LNAME_ARRAY,
                            TABLEN OUT BINARY_INTEGER,
                            LIST OUT VARCHAR2
                           );

DECLARE
  CURSOR EMP_CUR
  IS
  SELECT LISTAGG(FIRST_NAME, ',') WITHIN GROUP (ORDER BY FIRST_NAME) NAME_TAB
  FROM EMPLOYEES
  WHERE DEPARTMENT_ID = 30
  ORDER BY FIRST_NAME;
  --
  V_CUR EMP_CUR%ROWTYPE;
  V_TABLEN BINARY_INTEGER;
  V_TAB DBMS_UTILITY.UNCL_ARRAY;
  V_LIST VARCHAR2(32767);
BEGIN
  OPEN EMP_CUR;
  LOOP
    FETCH EMP_CUR INTO V_CUR;
    EXIT WHEN EMP_CUR%NOTFOUND;
    DBMS_UTILITY.COMMA_TO_TABLE(LIST => V_CUR.NAME_TAB,
                                TABLEN => V_TABLEN,
                                TAB => V_TAB
                               );
    FOR I IN 1..V_TABLEN
    LOOP
      DBMS_OUTPUT.PUT_LINE('NAME = '||V_TAB(I));
    END LOOP;
  END LOOP;
  CLOSE EMP_CUR;
  --
  DBMS_UTILITY.TABLE_TO_COMMA(TAB => V_TAB,
                              TABLEN => V_TABLEN,
                              LIST => V_LIST
                             );
  DBMS_OUTPUT.NEW_LINE;
  DBMS_OUTPUT.PUT_LINE('LIST = '||V_LIST);
  
END;
/

FORMAT_CALL_STACK FUNCTION:

This procedure is useful for debugging undocumented code having complex logic with so many function packages and procedures. This is returned the sequence of execution in descending order.

SYNTAX:

DBMS_UTILITY.FORMAT_CALL_STACK RETURN VARCHAR2

CREATE OR REPLACE PACKAGE PKG_CALL_STACK
IS
  PROCEDURE SP_CALL_STACK_1;
  PROCEDURE SP_CALL_STACK_2;
  PROCEDURE SP_CALL_STACK_3;
END;
/

CREATE OR REPLACE PACKAGE BODY PKG_CALL_STACK
IS
  PROCEDURE SP_CALL_STACK_1
  IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_CALL_STACK);
  END;
  --
  PROCEDURE SP_CALL_STACK_2
  IS
  BEGIN
    SP_CALL_STACK_1;
  END;
  --
  PROCEDURE SP_CALL_STACK_3
  IS
    BEGIN
    SP_CALL_STACK_2;
  END;
END;
/

EXEC PKG_CALL_STACK.SP_CALL_STACK_3;

FORMAT_ERROR_STACK FUNCTION:

This function returns the current error stack. This is used for debugging purpose. This function can be used in an exception handler to verify the sequence of error occurred in current execution.

SYNTAX:

DBMS_UTILITY.FORMAT_ERROR_STACK RETURN VARCHAR2;

CREATE TABLE TAB_ERROR_STACK(STACK_ID NUMBER PRIMARY KEY);

CREATE OR REPLACE PACKAGE PKG_CALL_ERROR_STACK
IS
  PROCEDURE SP_CALL_ERROR_STACK_1;
  PROCEDURE SP_CALL_ERROR_STACK_2;
  PROCEDURE SP_CALL_ERROR_STACK_3;
END;
/

CREATE OR REPLACE PACKAGE BODY PKG_CALL_ERROR_STACK
IS
  PROCEDURE SP_CALL_ERROR_STACK_1
  IS
  BEGIN
    INSERT INTO TAB_ERROR_STACK VALUES(NULL);
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('ERROR - >'||SQLERRM);
  END;
  --
  PROCEDURE SP_CALL_ERROR_STACK_2
  IS
  BEGIN
    SP_CALL_ERROR_STACK_1;
    INSERT INTO TAB_ERROR_STACK VALUES(1);
    INSERT INTO TAB_ERROR_STACK VALUES(1);
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('ERROR AT SP_CALL_ERROR_STACK_2 ->'||SQLERRM);
  END;
  --
  PROCEDURE SP_CALL_ERROR_STACK_3
  IS
  BEGIN
    SP_CALL_ERROR_STACK_2;
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('ERROR AT SP_CALL_ERROR_STACK_3 -> '||DBMS_UTILITY.FORMAT_ERROR_STACK);
  END;
END;
/

EXEC PKG_CALL_ERROR_STACK.SP_CALL_ERROR_STACK_3;

FORMAT_ERROR_BACKTRACE FUNCTION:

• This function is used for debugging purposes.
• This is used in exception handler.
• This function returns the call stack at the point where the error is raised.
• It also shows the line no where the error is raised.
• Even if the calling program is a plsql block then also it will work.
• Together FORMAT_ERROR_STACK and FORMAT_ERROR_BACKTRACE is a good tool for developer for debugging.

SYNTAX:

DBMS_UTILITY.FORMAT_ERROR_BACKTRACE RETURN VARCHAR2;

Lets take the previous example:

CREATE OR REPLACE PACKAGE BODY PKG_CALL_ERROR_STACK
IS
  PROCEDURE SP_CALL_ERROR_STACK_1
  IS
  BEGIN
    INSERT INTO TAB_ERROR_STACK VALUES(NULL);
  END;
  --
  PROCEDURE SP_CALL_ERROR_STACK_2
  IS
  BEGIN
    SP_CALL_ERROR_STACK_1;
    INSERT INTO TAB_ERROR_STACK VALUES(1);
    INSERT INTO TAB_ERROR_STACK VALUES(1);
  END;
  --
  PROCEDURE SP_CALL_ERROR_STACK_3
  IS
  BEGIN
    SP_CALL_ERROR_STACK_2;
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('ERROR AT SP_CALL_ERROR_STACK_3 -> '||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
  END;
END;
/

CREATE OR REPLACE PACKAGE BODY PKG_CALL_ERROR_STACK
IS
  PROCEDURE SP_CALL_ERROR_STACK_1
  IS
  BEGIN
    INSERT INTO TAB_ERROR_STACK VALUES(NULL);
  END;
  --
  PROCEDURE SP_CALL_ERROR_STACK_2
  IS
  BEGIN
    SP_CALL_ERROR_STACK_1;
    INSERT INTO TAB_ERROR_STACK VALUES(1);
    INSERT INTO TAB_ERROR_STACK VALUES(1);
  END;
  --
  PROCEDURE SP_CALL_ERROR_STACK_3
  IS
  BEGIN
    SP_CALL_ERROR_STACK_2;
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('ERROR AT SP_CALL_ERROR_STACK_3 -> '||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
  END;
END;
/

RELATED TOPICS:

• DBMS_OUTPUT
• DBMS_LOCK
• UTL_FILE
• DBMS_SQL