1. Home
  2. Docs
  3. Oracle 19c PL/SQL
  4. Exceptions
  5. Non-predefined Exceptions

Non-predefined Exceptions

Non-predefined exceptions are the Oracle “ORA-n” errors which do not have exception handlers in PL/SQL.

If you know that your database operations might raise specific internally defined errors, then you can handle such errors by declaring a name for those errors so that you can write exception handlers specifically for them. Otherwise, you can handle them only with OTHERS exception handlers.

DECLARE
  E_EmpRemaining EXCEPTION;
  PRAGMA EXCEPTION_INIT(E_EmpRemaining, -2292);
  V_Deptno Emp.Deptno%TYPE :=&GDeptno;
BEGIN
  DELETE FROM Dept
  WHERE Deptno =V_Deptno;
  IF SQL%NOTFOUND THEN
    DBMS_OUTPUT.PUT_LINE('The given information is missing in the Database. Check for Proper values.');
  END IF;
  ROLLBACK;
EXCEPTION
  WHEN E_EmpRemaining THEN
    DBMS_OUTPUT.PUT_LINE('Unable to Delete the Department Number '||V_Deptno||' as the Employees are Existing.');
    DBMS_OUTPUT.PUT_LINE('Validate your relations and then Try Again.....');
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('The given information is missing.');
END;
/
Enter value for gdeptno: 40

PL/SQL procedure successfully completed.

SQL> /
Enter value for gdeptno: 50
The given information is missing in the Database. Check for Proper values.      

PL/SQL procedure successfully completed.

/
Enter value for gdeptno: 10
Unable to Delete the Department Number 10 as the Employees are Existing.        
Validate your relations and then Try Again.....                                 

PL/SQL procedure successfully completed.

Q : Difference between SQL%NOTFOUND and NO_DATA_FOUND?

Ans: NO_DATA_FOUND is fired only in SELECT statements. But SQL%NOTFOUND is fired in DELETE or UPDATE.


You can convert predefined expectations to non-predefined ones.

ORA-1422 is a Predefined Exception in PL/SQL which is TOO_MANY_VALUES. But let us handle ORA-1422 using pragma.

DECLARE
  V_EName Emp.EName%TYPE;
  V_Empno Emp.Empno%TYPE;
  V_Sal Emp.Sal%TYPE;
  E_TooManyValues EXCEPTION;
  PRAGMA EXCEPTION_INIT(E_TooManyValues, -1422);
BEGIN
  V_Sal :=&GiveSal;
  SELECT Empno, EName
   INTO V_Empno, V_EName
   FROM Emp
   WHERE Sal =V_Sal;
  IF SQL%NOTFOUND THEN
    DBMS_OUTPUT.PUT_LINE('Not Found !');
  END IF;
  DBMS_OUTPUT.PUT_LINE(V_Empno||' , '||V_EName);
EXCEPTION
  WHEN E_TooManyValues THEN
    DBMS_OUTPUT.PUT_LINE('Error ! Too Many Values.');
END;
/

Enter value for givesal: 1250
Error ! Too Many Values.                                                        

PL/SQL procedure successfully completed.

ORA-1403 is the only error which can’t be converted into non-predefined with the same error number. ORA-1403 is internally returned by SQL engine as +100. So, ORA-1403 is the only error that is handled by +100 error number.

DECLARE
  V_EName Emp.EName%TYPE;
  V_Empno Emp.Empno%TYPE;
  V_Sal Emp.Sal%TYPE;
  E_TooManyValues EXCEPTION;
  PRAGMA EXCEPTION_INIT(E_TooManyValues, -1422);
  E_NoDataFound EXCEPTION;
  PRAGMA EXCEPTION_INIT(E_NoDataFound, +100);
  E_ValueError EXCEPTION;
  PRAGMA EXCEPTION_INIT(E_ValueError, -6502);
BEGIN
  V_Sal :=&GiveSal;
  SELECT Empno, EName
   INTO V_Empno, V_EName
   FROM Emp
   WHERE Sal =V_Sal;
  IF SQL%NOTFOUND THEN
    DBMS_OUTPUT.PUT_LINE('Not Found !');
  END IF;
  DBMS_OUTPUT.PUT_LINE(V_Empno||' , '||V_EName);
EXCEPTION
  WHEN E_TooManyValues THEN
    DBMS_OUTPUT.PUT_LINE('Error ! Too Many Values.');
  WHEN E_NoDataFound THEN
    DBMS_OUTPUT.PUT_LINE('Error ! No Data Found.');
  WHEN E_ValueError THEN
    DBMS_OUTPUT.PUT_LINE('Error ! Value Error.');
END;
/

SQL> /
Enter value for givesal: 1600
7499 , ALLEN                                                                    

PL/SQL procedure successfully completed.

SQL> /
Enter value for givesal: 3000
Error ! Too Many Values.                                                        

PL/SQL procedure successfully completed.

SQL> /
Enter value for givesal: 1111
Error ! No Data Found.                                                          

PL/SQL procedure successfully completed.

SQL> /
Enter value for givesal: '123A'
Error ! Value Error.                                                            

PL/SQL procedure successfully completed.

Using OTHERS exception handler

PL/SQL predefined exceptions are easy to be implemented as they are straight forward and you only need to remember the exception handler name. In non-predefined exceptions, you must know the error number and then associate a name with the error number using PRAGMA declaration.

But if you do not the error numbers then how can you handle the exception? For this reason PL/SQL provides you the OTHERS exception handler which can catch any exception raised. You can use exception trapping functions SQLCODE and SQLERRM in exception block to identify what the error code and error message is.

DECLARE
  V_Empno Emp.Empno%TYPE;
  V_EName Emp.EName%TYPE;
  V_Sal Emp.Sal%TYPE;
  V_ErrorCode NUMBER(6);
  V_ErrorMessage VARCHAR2(100);
BEGIN
  V_Sal :=&GiveSal;
  SELECT Empno, EName
   INTO
   V_Empno, V_EName
   FROM Emp
   WHERE Sal= V_Sal;
  DBMS_OUTPUT.PUT_LINE(V_Empno||' , '||V_EName);
EXCEPTION
  WHEN OTHERS THEN
    V_ErrorCode :=SQLCODE;
    V_ErrorMessage :=SUBSTR(SQLERRM, 1, 100);
    DBMS_OUTPUT.PUT_LINE('The Error Code Traced is :'||V_ErrorCode);
    DBMS_OUTPUT.PUT_LINE('The Error Message is :'||V_ErrorMessage);
END;
/

Enter value for givesal: 1500
7844 , TURNER                                                                   

PL/SQL procedure successfully completed.

SQL> /
Enter value for givesal: 100
The Error Code Traced is :100                                                   
The Error Message is :ORA-01403: no data found                                  

PL/SQL procedure successfully completed.

SQL> /
Enter value for givesal: 3000
The Error Code Traced is :-1422                                                 
The Error Message is :ORA-01422: exact fetch returns more than requested number of rows                                                                         

PL/SQL procedure successfully completed.

SQL> /
Enter value for givesal: '123A'
The Error Code Traced is :-6502                                                 
The Error Message is :ORA-06502: PL/SQL: numeric or value error: character to number conversion error                                                         

PL/SQL procedure successfully completed.
Was this article helpful to you? Yes 1 No

How can we help?