Sayantan's Blog On Oracle

WORKING WITH PLSQL EXCEPTION

EXCEPTION DEFINITION:

• Exception is an identifier in PLSQL that raised during the execution of a PLSQL block and that terminates the main body of actions.
• A PLSQL block always terminates when an exception is raised, but we can add an exception handler to perform the final action.
• Exception raised due to
  • design issues
  • coding mistakes
  • hardware failure
  • or any other resources

• We cannot anticipate all possible errors. But we can add an handler to continue the program as if it would continue without the presence of exception.

A PLSQL block has an optional exception handling part where we can add more than one handler.

Examples :

CREATE TABLE EMPLOYEES_EXCEPTION
AS
SELECT * FROM EMPLOYEES;

EXAMPLE OF PLSQL EXCEPTIONS:

Example 1:

PLSQL Block without an Exception Section:

DECLARE
  PROCEDURE EMP_PROC (P_EMPID EMPLOYEES_EXCEPTION.EMPLOYEE_ID%TYPE)
  IS
    V_FIRST_NAME EMPLOYEES_EXCEPTION.FIRST_NAME%TYPE;
    V_LAST_NAME  EMPLOYEES_EXCEPTION.LAST_NAME%TYPE;
    V_SALARY     EMPLOYEES_EXCEPTION.SALARY%TYPE;
  BEGIN
    SELECT FIRST_NAME,
           LAST_NAME,
           SALARY
    INTO V_FIRST_NAME,
         V_LAST_NAME,
         V_SALARY
    FROM EMPLOYEES_EXCEPTION
    WHERE EMPLOYEE_ID = P_EMPID;
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE NAME -> '||V_FIRST_NAME||' '||V_LAST_NAME||' -> SALARY -> '||V_SALARY);
  END;
BEGIN
  EMP_PROC(1000);
END;
/

Example 2:

DECLARE
  PROCEDURE EMP_PROC (P_EMPID EMPLOYEES_EXCEPTION.EMPLOYEE_ID%TYPE)
  IS
    V_FIRST_NAME EMPLOYEES_EXCEPTION.FIRST_NAME%TYPE;
    V_LAST_NAME  EMPLOYEES_EXCEPTION.LAST_NAME%TYPE;
    V_SALARY     EMPLOYEES_EXCEPTION.SALARY%TYPE;
  BEGIN
    SELECT FIRST_NAME,
           LAST_NAME,
           SALARY
    INTO V_FIRST_NAME,
         V_LAST_NAME,
         V_SALARY
    FROM EMPLOYEES_EXCEPTION
    WHERE EMPLOYEE_ID = P_EMPID;
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE NAME -> '||V_FIRST_NAME||' '||V_LAST_NAME||' -> SALARY -> '||V_SALARY);
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('ERROR -> EMPLOYEE DOES NOT EXISTS -> '||SQLERRM);
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('OTHER ERROR -> '||SQLERRM);
  END;
BEGIN
  EMP_PROC(1000);
END;
/

Example 3:

DECLARE
  PROCEDURE EMP_PROC (P_EMPID EMPLOYEES_EXCEPTION.EMPLOYEE_ID%TYPE)
  IS
    V_FIRST_NAME EMPLOYEES_EXCEPTION.FIRST_NAME%TYPE;
    V_LAST_NAME  EMPLOYEES_EXCEPTION.LAST_NAME%TYPE;
    V_SALARY     EMPLOYEES_EXCEPTION.SALARY%TYPE;
  BEGIN
    SELECT FIRST_NAME,
           LAST_NAME,
           SALARY
    INTO V_FIRST_NAME,
         V_LAST_NAME,
         V_SALARY
    FROM EMPLOYEES_EXCEPTION
    WHERE EMPLOYEE_ID = P_EMPID;
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE NAME -> '||V_FIRST_NAME||' '||V_LAST_NAME||' -> SALARY -> '||V_SALARY);
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('ERROR -> EMPLOYEE DOES NOT EXISTS -> '||SQLERRM);
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('OTHER ERROR -> '||SQLERRM);
  END;
BEGIN
  EMP_PROC('Q11');
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('EXTERNAL BLOCKS OTHER ERROR -> '||SQLERRM);
END;
/

TYPES OF EXCEPTIONS:

• INTERNALLY DEFINED
• PREDEFINED
• USER-DEFINED

INTERNALLY DEFINED:

• Internally defined exceptions are those exceptions which are raised implicitly during runtime.
• They (Internally defined exceptions) have an error code but does not have a name unless PLSQL or we provide a name to it.
• Few examples are
  • ORA-00060 -> Deadlock detected while waiting for resource
  • ORA-27102 -> Out of memory

Example:

CREATE TABLE EMPLOYEES_DEADLOCK_1
AS
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY
FROM EMPLOYEES;

CREATE TABLE EMPLOYEES_DEADLOCK_2
AS
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY
FROM EMPLOYEES;

Example 1:

Open 2 sessions of HR schema. Execute below 2 script in first and second session respectively.

SESSION 1:

DECLARE
  V_EMPLOYEE_ID_1 EMPLOYEES_DEADLOCK_1.EMPLOYEE_ID%TYPE;
  V_FIRST_NAME_1  EMPLOYEES_DEADLOCK_1.FIRST_NAME%TYPE;
  V_SALARY_1      EMPLOYEES_DEADLOCK_1.SALARY%TYPE;
  --
  V_EMPLOYEE_ID_2 EMPLOYEES_DEADLOCK_2.EMPLOYEE_ID%TYPE;
  V_FIRST_NAME_2  EMPLOYEES_DEADLOCK_2.FIRST_NAME%TYPE;
  V_SALARY_2      EMPLOYEES_DEADLOCK_2.SALARY%TYPE;
BEGIN
  SELECT EMPLOYEE_ID,
         FIRST_NAME,
         SALARY
  INTO V_EMPLOYEE_ID_1,
       V_FIRST_NAME_1,
       V_SALARY_1
  FROM EMPLOYEES_DEADLOCK_1
  WHERE EMPLOYEE_ID = 100
  FOR UPDATE;
  --
  DBMS_LOCK.SLEEP(30);
  --
  SELECT EMPLOYEE_ID,
         FIRST_NAME,
         SALARY
  INTO V_EMPLOYEE_ID_2,
       V_FIRST_NAME_2,
       V_SALARY_2
  FROM EMPLOYEES_DEADLOCK_2
  WHERE EMPLOYEE_ID = 100
  FOR UPDATE;
  --
  ROLLBACK;
END;
/

SESSION 2:

DECLARE
  V_EMPLOYEE_ID_1 EMPLOYEES_DEADLOCK_1.EMPLOYEE_ID%TYPE;
  V_FIRST_NAME_1  EMPLOYEES_DEADLOCK_1.FIRST_NAME%TYPE;
  V_SALARY_1      EMPLOYEES_DEADLOCK_1.SALARY%TYPE;
  --
  V_EMPLOYEE_ID_2 EMPLOYEES_DEADLOCK_2.EMPLOYEE_ID%TYPE;
  V_FIRST_NAME_2  EMPLOYEES_DEADLOCK_2.FIRST_NAME%TYPE;
  V_SALARY_2      EMPLOYEES_DEADLOCK_2.SALARY%TYPE;
BEGIN
  SELECT EMPLOYEE_ID,
         FIRST_NAME,
         SALARY
  INTO V_EMPLOYEE_ID_2,
       V_FIRST_NAME_2,
       V_SALARY_2
  FROM EMPLOYEES_DEADLOCK_2
  WHERE EMPLOYEE_ID = 100
  FOR UPDATE;
  
  --
  DBMS_LOCK.SLEEP(30);
  --
  SELECT EMPLOYEE_ID,
         FIRST_NAME,
         SALARY
  INTO V_EMPLOYEE_ID_1,
       V_FIRST_NAME_1,
       V_SALARY_1
  FROM EMPLOYEES_DEADLOCK_1
  WHERE EMPLOYEE_ID = 100
  FOR UPDATE;
  --
  ROLLBACK;
END;
/

In first code executed in SESSION 1:

• First the row of employee_id 100 get locked in EMPLOYEES_DEADLOCK_1 table.
• Then there is a pause of 30 sec
• Then the row of employee_id 100 get locked in EMPLOYEES_DEADLOCK_2 table.

In second code executed in SESSION 2:

• All the reverse things are happened that happened in SESSION 1.
• There is a pause of 30 seconds to switch between sessions.
• Finally one of the sessions will
  • detect the deadlock
  • rollback its transactions
  • throws a deadlock error
• Other transaction in other session will complete its transaction successfully.

SESSION 1 :

SESSION 2:

Example 2:

HANDLING THE DEADLOCK EXCEPTION:

SESSION 1:

DECLARE
  V_EMPLOYEE_ID_1 EMPLOYEES_DEADLOCK_1.EMPLOYEE_ID%TYPE;
  V_FIRST_NAME_1  EMPLOYEES_DEADLOCK_1.FIRST_NAME%TYPE;
  V_SALARY_1      EMPLOYEES_DEADLOCK_1.SALARY%TYPE;
  --
  V_EMPLOYEE_ID_2 EMPLOYEES_DEADLOCK_2.EMPLOYEE_ID%TYPE;
  V_FIRST_NAME_2  EMPLOYEES_DEADLOCK_2.FIRST_NAME%TYPE;
  V_SALARY_2      EMPLOYEES_DEADLOCK_2.SALARY%TYPE;
  --
  DEADLOCK_EXCEPTION EXCEPTION;
  PRAGMA EXCEPTION_INIT(DEADLOCK_EXCEPTION, -60);
BEGIN
  SELECT EMPLOYEE_ID,
         FIRST_NAME,
         SALARY
  INTO V_EMPLOYEE_ID_1,
       V_FIRST_NAME_1,
       V_SALARY_1
  FROM EMPLOYEES_DEADLOCK_1
  WHERE EMPLOYEE_ID = 100
  FOR UPDATE;
  --
  DBMS_LOCK.SLEEP(30);
  --
  SELECT EMPLOYEE_ID,
         FIRST_NAME,
         SALARY
  INTO V_EMPLOYEE_ID_2,
       V_FIRST_NAME_2,
       V_SALARY_2
  FROM EMPLOYEES_DEADLOCK_2
  WHERE EMPLOYEE_ID = 100
  FOR UPDATE;
  --
  ROLLBACK;
EXCEPTION
  WHEN DEADLOCK_EXCEPTION THEN
    DBMS_OUTPUT.PUT_LINE('DEADLOCK DETECTED IN SESSION 1');
    ROLLBACK;
END;
/

SESSION 2:

DECLARE
  V_EMPLOYEE_ID_1 EMPLOYEES_DEADLOCK_1.EMPLOYEE_ID%TYPE;
  V_FIRST_NAME_1  EMPLOYEES_DEADLOCK_1.FIRST_NAME%TYPE;
  V_SALARY_1      EMPLOYEES_DEADLOCK_1.SALARY%TYPE;
  --
  V_EMPLOYEE_ID_2 EMPLOYEES_DEADLOCK_2.EMPLOYEE_ID%TYPE;
  V_FIRST_NAME_2  EMPLOYEES_DEADLOCK_2.FIRST_NAME%TYPE;
  V_SALARY_2      EMPLOYEES_DEADLOCK_2.SALARY%TYPE;
  --
  DEADLOCK_EXCEPTION EXCEPTION;
  PRAGMA EXCEPTION_INIT(DEADLOCK_EXCEPTION, -60);
BEGIN
  SELECT EMPLOYEE_ID,
         FIRST_NAME,
         SALARY
  INTO V_EMPLOYEE_ID_2,
       V_FIRST_NAME_2,
       V_SALARY_2
  FROM EMPLOYEES_DEADLOCK_2
  WHERE EMPLOYEE_ID = 100
  FOR UPDATE;
  
  --
  DBMS_LOCK.SLEEP(30);
  --
  SELECT EMPLOYEE_ID,
         FIRST_NAME,
         SALARY
  INTO V_EMPLOYEE_ID_1,
       V_FIRST_NAME_1,
       V_SALARY_1
  FROM EMPLOYEES_DEADLOCK_1
  WHERE EMPLOYEE_ID = 100
  FOR UPDATE;
  --
  ROLLBACK;
EXCEPTION
  WHEN DEADLOCK_EXCEPTION THEN
    DBMS_OUTPUT.PUT_LINE('DEADLOCK DETECTED IN SESSION 2');
    ROLLBACK;  
END;
/

SESSION 1:

SESSION 2:

PREDEFINED EXCEPTION:

• Predefined exceptions are kind of Internally defined exception but they have provided a name
• PLSQL globally declares those names in STANDARD package.
• Predefined exceptions are raised implicitly during runtime
• Since Predefined exceptions have names we can directly add an exception handler against them.

Some popular Predefined exceptions are:

EXCEPTION NAME	ERROR CODE
NO_DATA_FOUND	+100
ZERO_DIVIDE	-1476
VALUE_ERROR	-6502
TOO_MANY_ROWS	-1422
PROGRAM_ERROR	-6501
INVALID_NUMBER	-1722
DUP_VAL_ON_INDEX	-1
ROWTYPE_MISMATCH	-6504
INVALID_CURSOR	-1001
CURSOR_ALREADY_OPEN	-6511
COLLECTION_IS_NULL	-6531
SUBSCRIPT_OUTSIDE_LIMIT	-6532
SUBSCRIPT_BEYOND_COUNT	-6533
STORAGE_ERROR	-6500
TIMEOUT_ON_RESOURCE	-51
NOT_LOGGED_ON	-1012
LOGIN_DENIED	-1017
NO_DATA_NEEDED	-6548
SYS_INVALID_ROWID	-1410
ACCESS_INTO_NULL	-6530
CASE_NOT_FOUND	-6592
SELF_IS_NULL	-30625

NO_DATA_FOUND EXAMPLE:

DECLARE
  V_FIRST_NAME     EMPLOYEES_EXCEPTION.FIRST_NAME%TYPE;
  V_LAST_NAME      EMPLOYEES_EXCEPTION.LAST_NAME%TYPE;
BEGIN
  SELECT FIRST_NAME,
         LAST_NAME
  INTO V_FIRST_NAME,
       V_LAST_NAME
  FROM EMPLOYEES_EXCEPTION
  WHERE EMPLOYEE_ID = 1000;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE DOES NOT EXISTS');
END;
/

ZERO_DIVIDE EXAMPLE:

DECLARE
  V_DEPT_TOTAL     NUMBER := 0;
  V_DEPT_COUNT     NUMBER := 0;
  V_AVG_SAL        NUMBER := 0;
  CURSOR CUR_DEPT(P_DEPTID EMPLOYEES_EXCEPTION.DEPARTMENT_ID%TYPE)
  IS
  SELECT SUM(SALARY) DEPT_TOTAL,
         COUNT(EMPLOYEE_ID) DEPT_COUNT
  FROM EMPLOYEES_EXCEPTION
  WHERE DEPARTMENT_ID = P_DEPTID;
BEGIN
  FOR I IN CUR_DEPT(300)
  LOOP
    V_DEPT_TOTAL := I.DEPT_TOTAL;
    V_DEPT_COUNT := I.DEPT_COUNT;
  END LOOP;
  V_AVG_SAL := ROUND(NVL(V_DEPT_TOTAL, 0.0000001)/V_DEPT_COUNT, 0);
  DBMS_OUTPUT.PUT_LINE('AVERAGE SALARY = '||V_AVG_SAL);
EXCEPTION
  WHEN ZERO_DIVIDE THEN
    DBMS_OUTPUT.PUT_LINE('EMPLOYEES DO NOT EXIST IN THE DEPARTMENT');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('ERROR -> '||SQLERRM);
END;
/

VALUE_ERROR EXAMPLE:

DECLARE
  V_FULL_NAME     VARCHAR2(10);
BEGIN
  SELECT FIRST_NAME||' '||LAST_NAME
  INTO V_FULL_NAME
  FROM EMPLOYEES_EXCEPTION
  WHERE EMPLOYEE_ID = 100;
EXCEPTION
  WHEN VALUE_ERROR THEN
    DBMS_OUTPUT.PUT_LINE('VALUE ERROR -> FULL NAME LENGTH IS SHORTER THAN COMBINED LENGTH OF FIRST AND LAST NAME');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('ERROR -> '||SQLERRM);
END;
/

TOO_MANY_ROWS EXAMPLE:

CREATE TABLE EMP_EXCEPTION_TEST
AS
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY
FROM EMPLOYEES
WHERE 1 = 2;

INSERT INTO EMP_EXCEPTION_TEST
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY
FROM EMPLOYEES
WHERE EMPLOYEE_ID = 100;

INSERT INTO EMP_EXCEPTION_TEST
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY
FROM EMPLOYEES
WHERE EMPLOYEE_ID = 100;

SELECT * FROM EMP_EXCEPTION_TEST;

DECLARE
  V_FULL_NAME     VARCHAR2(60);
  V_EMPID         EMP_EXCEPTION_TEST.EMPLOYEE_ID%TYPE := 100;
BEGIN
  SELECT FIRST_NAME||' '||LAST_NAME
  INTO V_FULL_NAME
  FROM EMP_EXCEPTION_TEST
  WHERE EMPLOYEE_ID = V_EMPID;
EXCEPTION
  WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE('MORE THAN ONE EMPLOYEE EXISTS FOR THIS EMPLOYEE_ID -> '||V_EMPID);
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('ERROR -> '||SQLERRM);
END;
/

PROGRAM_ERROR EXAMPLE:

DECLARE
  V_COUNT         NUMBER := 0;
  V_EMPID         EMP_EXCEPTION_TEST.EMPLOYEE_ID%TYPE := 100;
BEGIN
  SELECT COUNT(1)
  INTO V_COUNT
  FROM EMP_EXCEPTION_TEST
  WHERE EMPLOYEE_ID = V_EMPID;
  IF V_COUNT > 1 THEN
    RAISE PROGRAM_ERROR;
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('ERROR -> MORE THAN ONE EMPLOYEE EIXIST FOR EMPLOYEE_ID -> '||V_EMPID||' -> '||SQLERRM);
END;
/

INVALID_NUMBER EXAMPLE:

DECLARE
  V_COUNT         NUMBER := 0;
  V_EMPID         VARCHAR2(5) := '100A';
BEGIN
  SELECT COUNT(1)
  INTO V_COUNT
  FROM EMP_EXCEPTION_TEST
  WHERE EMPLOYEE_ID = V_EMPID;
EXCEPTION
  WHEN INVALID_NUMBER THEN
    DBMS_OUTPUT.PUT_LINE('ERROR -> EMPLOYEE_ID SHOULD BE NUMBER -> '||SQLERRM);
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('ERROR -> '||SQLERRM);
END;
/

DUP_VAL_ON_INDEX EXAMPLE:

DUP_VAL_ON_INDEX is occurred when a program is trying to insert a duplicate value into a database column. And that is constrained by a unique index.

TRUNCATE TABLE EMP_EXCEPTION_TEST;

CREATE UNIQUE INDEX EMPID_UNIQUE_INDX ON EMP_EXCEPTION_TEST(EMPLOYEE_ID);

BEGIN
  INSERT INTO EMP_EXCEPTION_TEST
  SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY
  FROM EMPLOYEES
  WHERE EMPLOYEE_ID = 100;
  --
  INSERT INTO EMP_EXCEPTION_TEST
  SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY
  FROM EMPLOYEES
  WHERE EMPLOYEE_ID = 100;
EXCEPTION
  WHEN DUP_VAL_ON_INDEX  THEN
    DBMS_OUTPUT.PUT_LINE('ERROR -> DUPLICATE EMPLOYEE CANNOT BE INSERTED -> '||SQLERRM);
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('ERROR -> '||SQLERRM);
END;
/

ROWTYPE_MISMATCH EXAMPLE:

Occurred when host variable and plsql cursor variable are involved in an assignment where both having different return types.

DESC EMPLOYEES_EXCEPTION;

Name           Null     Type         
-------------- -------- ------------ 
EMPLOYEE_ID             NUMBER(6)    
FIRST_NAME              VARCHAR2(20) 
LAST_NAME      NOT NULL VARCHAR2(25) 
EMAIL          NOT NULL VARCHAR2(25) 
PHONE_NUMBER            VARCHAR2(20) 
HIRE_DATE      NOT NULL DATE         
JOB_ID         NOT NULL VARCHAR2(10) 
SALARY                  NUMBER(8,2)  
COMMISSION_PCT          NUMBER(2,2)  
MANAGER_ID              NUMBER(6)    
DEPARTMENT_ID           NUMBER(4)

DESC DESC EMP_EXCEPTION_TEST;

Name        Null     Type         
----------- -------- ------------ 
EMPLOYEE_ID          NUMBER(6)    
FIRST_NAME           VARCHAR2(20) 
LAST_NAME   NOT NULL VARCHAR2(25) 
SALARY               NUMBER(8,2)

DECLARE
  TYPE EMP_REF_CUR IS REF CURSOR;
  V_EMP_REF_CUR EMP_REF_CUR;
  V_EMPREC  EMP_EXCEPTION_TEST%ROWTYPE;
BEGIN
  OPEN V_EMP_REF_CUR FOR
  SELECT * FROM EMPLOYEES_EXCEPTION;
  LOOP
    FETCH V_EMP_REF_CUR INTO V_EMPREC;
    EXIT WHEN V_EMP_REF_CUR%NOTFOUND;
  END LOOP;
EXCEPTION
  WHEN ROWTYPE_MISMATCH THEN
    DBMS_OUTPUT.PUT_LINE('TARGET TABLE STRUCTURE IS NOT MATCHED WITH SOURCE TABLE -> ERROR -> '||SQLERRM);
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('ERROR -> '||SQLERRM);
END;
/

INVALID_CURSOR EXCEPTION:

INVALID_CURSOR exception occurred when

• you try to close a cursor which is not yet opened.
• you try to fetch from a cursor which is not yet opened.

Example 1:

DECLARE
  TYPE EMP_REF_CUR IS REF CURSOR;
  V_EMP_REF_CUR EMP_REF_CUR;
  V_EMPREC  EMPLOYEES_EXCEPTION%ROWTYPE;
BEGIN
  CLOSE V_EMP_REF_CUR;
  OPEN V_EMP_REF_CUR FOR
  SELECT * FROM EMPLOYEES_EXCEPTION;
  LOOP
    FETCH V_EMP_REF_CUR INTO V_EMPREC;
    EXIT WHEN V_EMP_REF_CUR%NOTFOUND;
  END LOOP;
  CLOSE V_EMP_REF_CUR;
EXCEPTION
  WHEN INVALID_CURSOR THEN
    DBMS_OUTPUT.PUT_LINE('CURSOR IS NOT OPENED -> ERROR -> '||SQLERRM);
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('ERROR -> '||SQLERRM);
END;
/

Example 2:

DECLARE
  TYPE EMP_REF_CUR IS REF CURSOR;
  V_EMP_REF_CUR EMP_REF_CUR;
  V_EMPREC  EMPLOYEES_EXCEPTION%ROWTYPE;
BEGIN
  LOOP
    FETCH V_EMP_REF_CUR INTO V_EMPREC;
    EXIT WHEN V_EMP_REF_CUR%NOTFOUND;
  END LOOP;
  CLOSE V_EMP_REF_CUR;
EXCEPTION
  WHEN INVALID_CURSOR THEN
    DBMS_OUTPUT.PUT_LINE('CURSOR IS NOT OPENED -> ERROR -> '||SQLERRM);
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('ERROR -> '||SQLERRM);
END;
/

CURSOR_ALREADY_OPEN EXCEPTION:

CURSOR_ALREADY_OPEN exception occurred when you try to open a cursor which is already opened. A cursor for loop implicitly open the cursor which it refers. So for cursor for loop its not needed to open the cursor inside the loop.

DECLARE
  CURSOR EMP_CUR
  IS
  SELECT EMPLOYEE_ID 
  FROM EMPLOYEES_EXCEPTION;
  V_EMP_ID EMPLOYEES_EXCEPTION.EMPLOYEE_ID%TYPE;
BEGIN
  OPEN EMP_CUR;
  LOOP
    FETCH EMP_CUR INTO V_EMP_ID;
    EXIT WHEN EMP_CUR%NOTFOUND;
  END LOOP;
  OPEN EMP_CUR;
  CLOSE EMP_CUR;
EXCEPTION
  WHEN CURSOR_ALREADY_OPEN  THEN
    DBMS_OUTPUT.PUT_LINE('CURSOR IS ALREADY OPENED -> ERROR -> '||SQLERRM);
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('ERROR -> '||SQLERRM);
END;
/

DECLARE
  CURSOR EMP_CUR
  IS
  SELECT EMPLOYEE_ID 
  FROM EMPLOYEES_EXCEPTION;
  V_EMP_ID EMPLOYEES_EXCEPTION.EMPLOYEE_ID%TYPE;
BEGIN
  FOR I IN EMP_CUR
  LOOP
    OPEN EMP_CUR;
    V_EMP_ID := I.EMPLOYEE_ID;
  END LOOP;
EXCEPTION
  WHEN CURSOR_ALREADY_OPEN  THEN
    DBMS_OUTPUT.PUT_LINE('CURSOR IS ALREADY OPENED -> ERROR -> '||SQLERRM);
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('ERROR -> '||SQLERRM);
END;
/

COLLECTION_IS_NULL EXCEPTION:

COLLECTION_IS_NULL exception is occurred when you try to apply a collection method on an uninitialized Nested Table or Varray.

Example 1:

DECLARE
  TYPE T_EMP_ID IS TABLE OF NUMBER;
  V_EMP_ID T_EMP_ID;
BEGIN
  V_EMP_ID(1) := 1;
  DBMS_OUTPUT.PUT_LINE('NUMBER -> '||V_EMP_ID(1));
EXCEPTION
  WHEN COLLECTION_IS_NULL THEN
    DBMS_OUTPUT.PUT_LINE('PLEASE INITIALIZE THE COLLECTION..');
END;
/

Example 2:

DECLARE
  TYPE T_EMP_ID IS VARRAY(10) OF NUMBER;
  V_EMP_ID T_EMP_ID;
BEGIN
  V_EMP_ID(1) := 1;
  DBMS_OUTPUT.PUT_LINE('NUMBER -> '||V_EMP_ID(1));
EXCEPTION
  WHEN COLLECTION_IS_NULL THEN
    DBMS_OUTPUT.PUT_LINE('PLEASE INITIALIZE THE COLLECTION..');
END;
/

SUBSCRIPT_OUTSIDE_LIMIT EXAMPLE:

Occurs when a program references a nested table or varray with an index number which is outside a legal range.

DECLARE
  TYPE T_EMP_ID IS TABLE OF NUMBER;
  V_EMP_ID T_EMP_ID := T_EMP_ID(1, 2, 3, 4);
BEGIN
  DBMS_OUTPUT.PUT_LINE('NUMBER -> '||V_EMP_ID(-1));
EXCEPTION
  WHEN SUBSCRIPT_OUTSIDE_LIMIT THEN
    DBMS_OUTPUT.PUT_LINE('SUBSCRIPT IS OUTSIDE LIMIT');
END;
/

SUBSCRIPT_BEYOND_COUNT EXAMPLE:

Occurs when a program refers a Nested table or Varray with an index larger than the no of elements in the collection.

DECLARE
  TYPE T_EMP_ID IS TABLE OF NUMBER;
  V_EMP_ID T_EMP_ID := T_EMP_ID(1, 2, 3, 4);
BEGIN
  DBMS_OUTPUT.PUT_LINE('NUMBER -> '||V_EMP_ID(5));
EXCEPTION
  WHEN SUBSCRIPT_BEYOND_COUNT THEN
    DBMS_OUTPUT.PUT_LINE('SUBSCRIPT IS BEYOND COUNT');
END;
/

STORAGE_ERROR:

STORAGE_ERROR exception occurs when the PLSQL is run out of memory or when memory has been corrupted. This may result from a software or hardware problem or simply not having enough memory to run Oracle properly.

TIMEOUT_ON_RESOURCE:

TIMEOUT_ON_RESOURCE exception occurs when Oracle is waiting for a resource that does not become available within the time out period.

NOT_LOGGED_ON:

NOT_LOGGED_ON exception occurs when your program issues a database call without being connected to Oracle.

LOGIN_DENIED:

LOGIN_DENIED exception occurs when you try to login to Oracle with an invalid user id and password.

NO_DATA_NEEDED:

A pipelined table function may create more data than is needed by the process querying it. If this happened then pipelined table function execution stops and raises the NO_DATA_NEEDED exception. This doesn't need to be explicitly handled except you have included an OTHERS exception handler.

SYS_INVALID_ROWID:

Occurs when the conversion of a character string into an universal rowid fails because the character string does not represent a valid rowid.

ACCESS_INTO_NULL:

ACCESS_INTO_NULL exception occurs when your program tries to assign values to the attributes of an uninitialized object.

CASE_NOT_FOUND:

CASE_NOT_FOUND exception occurs when none of the choices of the WHEN clause in a CASE statement is selected and there is no ELSE clause.

SELF_IS_NULL:

Occurs when your program attempts to call a MEMBER method on a null instance. It implies the built in parameter SELF which is the first parameter of MEMBER method is null.

USER-DEFINED EXCEPTION:

• In Oracle, developer can create their own exception and handle them.
• User defined exception which are created in the declaration part of a subprogram are visible only in that subprogram. It implies they are local to that sub program.
• User defined exception created in the package specification are public exception. They are visible to any subprogram who have an access to the package.

Example:

RAISING USER DEFINED EXCEPTION WITH RAISE STATEMENT:

DECLARE
  V_COUNT NUMBER := 0;
  EMP_EXCEPTION EXCEPTION;
BEGIN
  SELECT COUNT(1) 
  INTO V_COUNT
  FROM EMPLOYEES_EXCEPTION
  WHERE EMPLOYEE_ID = 1000;
  
  IF V_COUNT = 0 THEN
    RAISE EMP_EXCEPTION;
  END IF;
EXCEPTION
  WHEN EMP_EXCEPTION THEN
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE DOES NOT EXISTS.. ');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('ERROR -> '||SQLERRM);
END;
/

EXPLICITLY RAISED PREDEFINED EXCEPTION:

DECLARE
  V_COUNT NUMBER := 0;
BEGIN
  SELECT COUNT(1) 
  INTO V_COUNT
  FROM EMPLOYEES_EXCEPTION
  WHERE EMPLOYEE_ID = 1000;
  
  IF V_COUNT = 0 THEN
    RAISE NO_DATA_FOUND;
  END IF;
EXCEPTION
  WHEN NO_DATA_FOUND  THEN
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE DOES NOT EXISTS.. ');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('ERROR -> '||SQLERRM);
END;
/

RAISING CURRENT EXCEPTION WITH RAISE STATEMENT:

Example 1:

DECLARE
  V_COUNT NUMBER := 0;
BEGIN
  BEGIN
    SELECT COUNT(1) 
    INTO V_COUNT
    FROM EMPLOYEES_EXCEPTION
    WHERE EMPLOYEE_ID = 1000;
    
    IF V_COUNT = 0 THEN
      RAISE NO_DATA_FOUND;
    END IF;
  EXCEPTION
    WHEN NO_DATA_FOUND  THEN
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE DOES NOT EXISTS.. ');
    RAISE;
  END;
EXCEPTION
  WHEN NO_DATA_FOUND  THEN
    DBMS_OUTPUT.PUT_LINE('PLEASE ENTER CORRECT EMPLOYEE.. ');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('ERROR -> '||SQLERRM);
END;
/

Example 2:

DECLARE
  V_COUNT       NUMBER := 0;
  EMP_EXCEPTION EXCEPTION;
BEGIN
  BEGIN
    SELECT COUNT(1)
    INTO V_COUNT
    FROM EMPLOYEES_EXCEPTION
    WHERE EMPLOYEE_ID = 1000;
    IF V_COUNT = 0 THEN
      RAISE EMP_EXCEPTION;
    END IF;
  EXCEPTION
    WHEN EMP_EXCEPTION THEN
      DBMS_OUTPUT.PUT_LINE('EMPLOYEE DOES NOT EXISTS.. ');
      RAISE;
  END;
EXCEPTION
  WHEN EMP_EXCEPTION THEN
    DBMS_OUTPUT.PUT_LINE('PLEASE ENTER CORRECT EMPLOYEE.. ');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('ERROR -> '||SQLERRM);
END;
/

USING RAISE_APPLICATION_ERROR:

• RAISE_APPLICATION_ERROR is a procedure of DBMS_STANDARD package
• You can use RAISE_APPLICATION_ERROR only from a stored procedure or subprogram.
• You can use RAISE_APPLICATION_ERROR to raise user defined exception and return error code and error message to the invoker.
• The error code is an integer range from -20000 to -20999.
• The error message is a string of size 2048 bytes.

CREATE OR REPLACE PROCEDURE EMPLOYEE_STATUS(P_EMPID EMPLOYEES_EXCEPTION.EMPLOYEE_ID%TYPE)
IS
  V_COUNT NUMBER := 0;
BEGIN
  SELECT COUNT(1)
  INTO V_COUNT
  FROM EMPLOYEES_EXCEPTION
  WHERE EMPLOYEE_ID = P_EMPID;
  
  IF V_COUNT = 0 THEN
    RAISE_APPLICATION_ERROR(-20000, 'EMPLOYEE DOES NOT EXISTS..');
  END IF;
END;
/

DECLARE
  V_EMP_EXCEPTION EXCEPTION;
  PRAGMA EXCEPTION_INIT(V_EMP_EXCEPTION, -20000);
BEGIN
  EMPLOYEE_STATUS(1000);
EXCEPTION
  WHEN V_EMP_EXCEPTION THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM(-20000));
END;
/

UNHANDLED EXCEPTION:

• If there is no handler for a raised exception, plsql returns the exception to the invoker or host environment. The host environment determines the outcome.
• If a stored subprogram ends up with an unhandled exception, it does not rollback the database changes made by the subprogram.

DISCONTINUE TRANSACTION POST EXCEPTION:

EXAMPLE 1: Not explicitly rollback the transactions

SELECT EMPLOYEE_ID,
       FIRST_NAME,
       LAST_NAME,
       SALARY
FROM EMPLOYEES_EXCEPTION
WHERE DEPARTMENT_ID = 30;

CREATE TABLE EMP_ERROR_DML_TEST
AS
SELECT EMPLOYEE_ID,
       FIRST_NAME,
       LAST_NAME,
       SALARY
FROM EMPLOYEES_EXCEPTION
WHERE 1 = 2;

DECLARE
  CURSOR EMP_CUR
  IS
  SELECT EMPLOYEE_ID,
         FIRST_NAME,
         LAST_NAME,
         SALARY
  FROM EMPLOYEES_EXCEPTION
  WHERE DEPARTMENT_ID = 30
  ORDER BY EMPLOYEE_ID;
  V_FIRST_NAME EMPLOYEES_EXCEPTION.FIRST_NAME%TYPE;
  V_COUNT      NUMBER := 0;
BEGIN
  FOR I IN EMP_CUR
  LOOP
    IF UPPER(I.FIRST_NAME) = UPPER('Guy') THEN
      V_FIRST_NAME := I.FIRST_NAME||'LENGTH_TO_BE_INCREASED';
    ELSE
      V_FIRST_NAME := I.FIRST_NAME;
    END IF;
    --
    INSERT INTO EMP_ERROR_DML_TEST VALUES(I.EMPLOYEE_ID, V_FIRST_NAME, I.LAST_NAME, I.SALARY);
  END LOOP;
EXCEPTION
  WHEN VALUE_ERROR THEN
    SELECT COUNT(1)
    INTO V_COUNT
    FROM EMP_ERROR_DML_TEST;
    DBMS_OUTPUT.PUT_LINE('TOTAL RECORD INSERTED BEFORE ERROR ->'||V_COUNT); 
    DBMS_OUTPUT.PUT_LINE('VALUE ERROR -> '||SQLERRM);
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('ERROR -> '||SQLERRM);
END;
/

SELECT * FROM EMP_ERROR_DML_TEST;

All the data inserted before the error occurred are not rolled back.

EXAMPLE 2: Explicitly rolled back the transactions.

DECLARE
  CURSOR EMP_CUR
  IS
  SELECT EMPLOYEE_ID,
         FIRST_NAME,
         LAST_NAME,
         SALARY
  FROM EMPLOYEES_EXCEPTION
  WHERE DEPARTMENT_ID = 30
  ORDER BY EMPLOYEE_ID;
  V_FIRST_NAME EMPLOYEES_EXCEPTION.FIRST_NAME%TYPE;
  V_COUNT      NUMBER := 0;
BEGIN
  FOR I IN EMP_CUR
  LOOP
    IF UPPER(I.FIRST_NAME) = UPPER('Guy') THEN
      V_FIRST_NAME := I.FIRST_NAME||'LENGTH_TO_BE_INCREASED';
    ELSE
      V_FIRST_NAME := I.FIRST_NAME;
    END IF;
    --
    INSERT INTO EMP_ERROR_DML_TEST VALUES(I.EMPLOYEE_ID, V_FIRST_NAME, I.LAST_NAME, I.SALARY);
  END LOOP;
EXCEPTION
  WHEN VALUE_ERROR THEN
    SELECT COUNT(1)
    INTO V_COUNT
    FROM EMP_ERROR_DML_TEST;
    DBMS_OUTPUT.PUT_LINE('TOTAL RECORD INSERTED BEFORE ERROR ->'||V_COUNT); 
    DBMS_OUTPUT.PUT_LINE('VALUE ERROR -> '||SQLERRM);
    ROLLBACK;
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('ERROR -> '||SQLERRM);
    ROLLBACK;
END;
/

SELECT * FROM EMP_ERROR_DML_TEST;

All the transactions are rolled back.

CONTINUE TRANSACTION POST EXCEPTION:

EXAMPLE 3: Continue the transactions even after the error occurred.

DECLARE
  CURSOR EMP_CUR
  IS
  SELECT EMPLOYEE_ID,
         FIRST_NAME,
         LAST_NAME,
         SALARY
  FROM EMPLOYEES_EXCEPTION
  WHERE DEPARTMENT_ID = 30
  ORDER BY EMPLOYEE_ID;
  V_FIRST_NAME EMPLOYEES_EXCEPTION.FIRST_NAME%TYPE;
  V_COUNT      NUMBER := 0;
BEGIN
  FOR I IN EMP_CUR
  LOOP
    BEGIN
      SAVEPOINT EMP_SAVEPOINT;
      IF UPPER(I.FIRST_NAME) IN (UPPER('Guy'), UPPER('Alexander')) THEN
        V_FIRST_NAME := I.FIRST_NAME||'LENGTH_TO_BE_INCREASED';
      ELSE
        V_FIRST_NAME := I.FIRST_NAME;
      END IF;
      --
      INSERT INTO EMP_ERROR_DML_TEST VALUES(I.EMPLOYEE_ID, V_FIRST_NAME, I.LAST_NAME, I.SALARY);
    EXCEPTION
      WHEN VALUE_ERROR THEN
        DBMS_OUTPUT.PUT_LINE('VALUE ERROR -> '||SQLERRM||' FOR EMPLOYEE -> '||I.FIRST_NAME||' '||I.LAST_NAME||' EMPLOYEE_ID -> '||I.EMPLOYEE_ID);
        ROLLBACK TO EMP_SAVEPOINT;  --Undo the transaction
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('ERROR -> '||SQLERRM||'FOR EMPLOYEE -> '||I.FIRST_NAME||' '||I.LAST_NAME||' EMPLOYEE_ID -> '||I.EMPLOYEE_ID);
        ROLLBACK TO EMP_SAVEPOINT;  --Undo the transaction
    END;
  END LOOP;
  SELECT COUNT(1)
  INTO V_COUNT
  FROM EMP_ERROR_DML_TEST;
  DBMS_OUTPUT.PUT_LINE('TOTAL RECORD INSERTED ->'||V_COUNT); 
EXCEPTION
  WHEN VALUE_ERROR THEN
    SELECT COUNT(1)
    INTO V_COUNT
    FROM EMP_ERROR_DML_TEST;
    DBMS_OUTPUT.PUT_LINE('TOTAL RECORD INSERTED BEFORE ERROR ->'||V_COUNT); 
    DBMS_OUTPUT.PUT_LINE('VALUE ERROR -> '||SQLERRM);
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('ERROR -> '||SQLERRM);
END;
/

SELECT * FROM EMP_ERROR_DML_TEST;

EXCEPTION TRAPPING FUNCTIONS:

When an exception occurs you can identify the error code and error message by two functions:

• SQLCODE
• SQLERRM

SQLCODE: Returns the numeric value of the error code

SQLERRM: Returns the message part of the error associated with error code.

RETRIEVING ERROR CODE AND ERROR MESSAGE:

• You can retrieve the error code by SQLCODE function
• You can retrieve the error message by SQLERRM function. The maximum size of SQLERRM is 512 bytes
• DBMS_UTILITY.FORMAT_ERROR_STACK returns the full stack upto 2000 bytes
• DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function displays the call stack at the point where the exception was raised. This does so even if the subprogram is called from an exception handler in an outer scope.

EXAMPLE OF ALL TYPES OF ERROR TRAPPING IN ONE:

CREATE OR REPLACE PROCEDURE SP_ALL_EXCEPTION_TEST(P_EMPID EMPLOYEES_EXCEPTION.EMPLOYEE_ID%TYPE)
IS
  V_EMPLOYEE_ID EMPLOYEES_EXCEPTION.EMPLOYEE_ID%TYPE;
BEGIN
  SELECT EMPLOYEE_ID
  INTO V_EMPLOYEE_ID
  FROM EMPLOYEES_EXCEPTION
  WHERE EMPLOYEE_ID = P_EMPID;
END;
/

CREATE OR REPLACE PROCEDURE SP_EXCEPTION_1
IS
BEGIN
  SP_ALL_EXCEPTION_TEST(1000);
END;
/

CREATE OR REPLACE PROCEDURE SP_EXCEPTION_2
IS
BEGIN
  SP_EXCEPTION_1;
END;
/

CREATE OR REPLACE PROCEDURE SP_EXCEPTION_3
IS
BEGIN
  SP_EXCEPTION_2;
END;
/

CREATE OR REPLACE PROCEDURE SP_EXCEPTION_4
IS
BEGIN
  SP_EXCEPTION_3;
END;
/

CREATE OR REPLACE PROCEDURE SP_EXCEPTION_5
IS
BEGIN
  SP_EXCEPTION_4;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('ERROR...-> CODE...'||SQLCODE||' -> ERROR MESSAGE...'||SQLERRM);
    DBMS_OUTPUT.PUT_LINE('ERROR STACK...'||DBMS_UTILITY.FORMAT_ERROR_STACK);
    DBMS_OUTPUT.PUT_LINE('FORMAT ERROR BACKTRACE...'||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);  
END;
/

BEGIN
  SP_EXCEPTION_5;
END;
/

OUTPUT:

TRAPPING ERROR INTO A TABLE:

CREATE TABLE EMP_ERROR_TRAP
(ERRORID       NUMBER GENERATED ALWAYS AS IDENTITY,
 ERROR_CODE    VARCHAR2(100),
 ERROR_MSG     VARCHAR2(4000),
 ERROR_DATE    DATE
);

CREATE OR REPLACE PROCEDURE SP_EMP_ERROR_DML_TEST_INSERT
IS
  CURSOR EMP_CUR
  IS
  SELECT EMPLOYEE_ID,
         FIRST_NAME,
         LAST_NAME,
         SALARY
  FROM EMPLOYEES_EXCEPTION
  WHERE DEPARTMENT_ID = 30
  ORDER BY EMPLOYEE_ID;
  V_FIRST_NAME EMPLOYEES_EXCEPTION.FIRST_NAME%TYPE;
  V_COUNT      NUMBER := 0;
  --
  PROCEDURE SP_EMP_ERROR_TRAP
  (P_ERROR_CODE   EMP_ERROR_TRAP.ERROR_CODE%TYPE,
   P_ERROR_MSG    EMP_ERROR_TRAP.ERROR_MSG%TYPE,
   P_ERROR_DATE   EMP_ERROR_TRAP.ERROR_DATE%TYPE
  )
  IS
    PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
    INSERT INTO EMP_ERROR_TRAP(ERROR_CODE, ERROR_MSG, ERROR_DATE)
    VALUES(P_ERROR_CODE, P_ERROR_MSG, P_ERROR_DATE);
    COMMIT;
  END;
BEGIN
  FOR I IN EMP_CUR
  LOOP
    BEGIN
      SAVEPOINT EMP_SAVEPOINT;
      IF UPPER(I.FIRST_NAME) IN (UPPER('Guy'), UPPER('Alexander')) THEN
        V_FIRST_NAME := I.FIRST_NAME||'LENGTH_TO_BE_INCREASED';
      ELSE
        V_FIRST_NAME := I.FIRST_NAME;
      END IF;
      --
      INSERT INTO EMP_ERROR_DML_TEST VALUES(I.EMPLOYEE_ID, V_FIRST_NAME, I.LAST_NAME, I.SALARY);
    EXCEPTION
      WHEN VALUE_ERROR THEN
        SP_EMP_ERROR_TRAP(SQLCODE, 
                          'VALUE ERROR -> '||SQLERRM||' FOR EMPLOYEE -> '||I.FIRST_NAME||' '||I.LAST_NAME||' EMPLOYEE_ID -> '||I.EMPLOYEE_ID,
                          SYSDATE
                         );
        ROLLBACK TO EMP_SAVEPOINT;
      WHEN OTHERS THEN
        SP_EMP_ERROR_TRAP(SQLCODE, 
                          'VALUE ERROR -> '||SQLERRM||' FOR EMPLOYEE -> '||I.FIRST_NAME||' '||I.LAST_NAME||' EMPLOYEE_ID -> '||I.EMPLOYEE_ID,
                          SYSDATE
                         );
        ROLLBACK TO EMP_SAVEPOINT;
    END;
  END LOOP;
  SELECT COUNT(1)
  INTO V_COUNT
  FROM EMP_ERROR_DML_TEST;
  DBMS_OUTPUT.PUT_LINE('TOTAL RECORD INSERTED ->'||V_COUNT); 
EXCEPTION
  WHEN VALUE_ERROR THEN
    SELECT COUNT(1)
    INTO V_COUNT
    FROM EMP_ERROR_DML_TEST;
    DBMS_OUTPUT.PUT_LINE('TOTAL RECORD INSERTED BEFORE ERROR ->'||V_COUNT); 
    DBMS_OUTPUT.PUT_LINE('VALUE ERROR -> '||SQLERRM);
    SP_EMP_ERROR_TRAP(SQLCODE, 
                      'VALUE ERROR -> '||SQLERRM,
                      SYSDATE
                     );
  WHEN OTHERS THEN
    SP_EMP_ERROR_TRAP(SQLCODE, 
                      'ERROR -> '||SQLERRM,
                      SYSDATE
                     );
END;
/

EXEC SP_EMP_ERROR_DML_TEST_INSERT;

SELECT * FROM EMP_ERROR_DML_TEST;

SELECT * FROM EMP_ERROR_TRAP;

RELATED TOPICS:

• DBMS_UTILITY Package In Oracle
• Working With Procedures In Oracle
• Working With Packages In Oracle
• Working With Functions In Oracle