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.