Sayantan's Blog On Oracle

Sayantan's blogs on Oracle

Learn Oracle with Sayantan

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 TYPETABLE TYPE
INSTANCE_RECORDDBLINK_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)
);
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:

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;
/
DBMS_UTILITY Package In Oracle: Database Version
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
);
PARAMETERDESCRIPTION
P_OBJECT_IDThis is the object_id column from ALL_OBJECTS.
The user must have the create privilege on the object being modified. Otherwise inv_not_exists_or_no_priv exception is raised.
P_PLSQL_OBJECT_SETTINGSThis is an optional parameter and is applicable only for PLSQL objects. For other objects it will be null.
P_OPTION_FLAGSThis is an optional flag, default value is 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')
                     );
PARAMETERDESCRIPTION
OWNEROWNER of ALL_OBJECTS
OBJECT_NAMEName of the object
NAMESPACENamespace 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_NAMENot 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;
PARAMETERDESCRIPTION
TABLESComma separated list of tables
TIMEOUTMaximum no of seconds to wait
SCNSCN prior to which transactions must have begun and will be considered relevant to this request
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
                           );
PARAMETERDESCRIPTION
SCHEMAName of the schema
METHODPossible values are ->
ESTIMATE
COMPUTE
DELETE

If ESTIMATE then ESTIMATE_ROWS
& ESTIMATE_PERCENT will be non zero.
ESTIMATE_ROWSNo of rows to estimate
ESTIMATE_PERCENTPercentage of rows to estimate.
If ESTIMATE_ROWS is specified,
this parameter will be ignored.
METHOD_OPTMethod options of the following format:
[FOR TABLES]
[FOR ALL [INDEXED] COLUMNS][SIZE n]
[FOR ALL INDEXES]
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
                           );
PARAMETERDESCRIPTION
SCHEMAName of the schema
COMPILE_ALLIf TRUE, it will compile all the
objects in the schema irrespective
of the fact that the object is VALID or not
REUSE_SETTINGSIndicate whether the current session's settings
in the object will be reused, or the current session's settings should be adopted.
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');
PARAMETERDESCRIPTION
SCHEMAName of the object's schema
OBJECT_NAMEName of the partitioned object to be analyzed
OBJECT_TYPEType of object, T for tables, I for index
COMMAND_TYPEIndicating type of analysis to be performed.
Valid values are:
C -> COMPUTE_STATISTICS
E -> ESTIMATE_STATISTICS
D -> DELETE_STATISTICS
V -> VALIDATE_STRUCTURE
COMMAND_OPTOther options for command type
If COMMAND_TYPE in C, E then COMMAND_OPT can be for :
FOR TABLES,
FOR ALL LOCAL INDEXES
FOR ALL COLUMNS

If COMMAND_TYPE is V the COMMAND_OPT can be CASCADE if OBJECT_TYPE is T for TABLES
SAMPLE_CLAUSESpecifies SAMPLE_CLAUSE when COMMAND_TYPE is E for ESTIMATE.
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);
PARAMETERDESCRIPTION
LISTComma separated list of names
TABLENNo of tables in plsql table
TABPLSQL table which contains the list of names
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
                           );
PARAMETERDESCRIPTION
TABPLSQL table which contains the list of names
TABLENNo of tables in plsql table
LISTComma separated list of names
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;
/

Leave a Comment

Your email address will not be published.