Oracle 19c PL/SQL

  2. Home
  4. Docs
  6. Oracle 19c PL/SQL
  8. Exceptions
  10. Predefined Exceptions

Predefined Exceptions

Exceptions always wait for the situation. Exception doesn’t fire unless and until that situation is encountered.

Below is the list of some commonly occurred Oracle pre-defined exceptions.

  1. NO_DATA_FOUND: A SELECT INTO statement returns no rows.
  2. DUP_VAL_ON_INDEX: Your program attempts to store duplicate values in a database column that is constrained by a unique index.
  3. TOO_MANY_ROWS: A SELECT INTO statement returns more than one row.
  4. VALUE_ERROR: Raised if the conversion of a character string into a number fails. (In SQL statements, INVALID_NUMBER is raised.)
  5. INVALID_NUMBER: In a SQL statement, the conversion of a character string into a number fails because the string does not represent a valid number. (In procedural statements, VALUE_ERROR is raised.)
  6. CASE_NOT_FOUND: None of the choices in the WHEN clauses of a CASE statement is met, and there is no ELSE clause.
  7. CURSOR_ALREADY_OPEN: Your program attempts to open an already open cursor. 
  8. INVALID_CURSOR: Your program attempts an illegal cursor operation such as closing an unopened cursor.
  9. ZERO_DIVIDE: Your program attempts to divide a number by zero.

--Program without exception handler
DECLARE
  V_EName Emp.Ename%TYPE;
  V_Sal   Emp.Sal%TYPE;
BEGIN
    SELECT Ename, Sal
      INTO V_Ename, V_Sal
      FROM Emp
     WHERE Empno = &enterEmpno;
    DBMS_OUTPUT.PUT_LINE(V_Ename||' , '||V_Sal);
END;
/

Enter value for enterempno: 7839
KING , 5000                                                                     

PL/SQL procedure successfully completed.


SQL> /
Enter value for enterempno: 1234
DECLARE
*
ERROR at line 1:
ORA-01403: no data found 
ORA-06512: at line 5

So, you saw, the above code works fine for the employee numbers those already exist in the database. But if an employee number which doesn’t exist in the database is supplied, then the Oracle SQL engine returns no rows selected message to the PL/SQL engine. The PL/SQL engine treats that as an abnormal situation and raises an exception. When the exception handler is not provided in the block, the program terminates abruptly.

NO_DATA_FOUND Exception
--NO_DATA_FOUND exception handler

DECLARE
  V_EName Emp.Ename%TYPE;
  V_Sal   Emp.Sal%TYPE;
BEGIN
    SELECT Ename, Sal
      INTO V_Ename, V_Sal
      FROM Emp
     WHERE Empno = &enterEmpno;
    DBMS_OUTPUT.PUT_LINE(V_Ename||' , '||V_Sal);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Sorry... Employee number doesn''t exists.');
END;
/

Enter value for enterempno: 7839
KING , 5000                                                                     

PL/SQL procedure successfully completed.


SQL> /
Enter value for enterempno: 1234
Sorry... Employee number doesn't exists.
INVALID_NUMBER Exception
--INVALID_NUMBER exception handler

DECLARE
  V_EName Emp.Ename%TYPE;
  V_Sal   Emp.Sal%TYPE;
BEGIN
  SELECT Ename, Sal
   INTO V_Ename, V_Sal
   FROM Emp
   WHERE Empno = &enterEmpno;
  DBMS_OUTPUT.PUT_LINE(V_Ename||' , '||V_Sal);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Sorry... Employee number doesn''t exists.');
  WHEN INVALID_NUMBER THEN
    DBMS_OUTPUT.PUT_LINE('Employee Number entered is not a Number.');
    DBMS_OUTPUT.PUT_LINE('Try Again.....');
END;
/

Enter value for enterempno: 7654
MARTIN , 1250                                                                   

PL/SQL procedure successfully completed.

SQL> /
Enter value for enterempno: 1234
Sorry... Employee number doesn't exists.                                        

PL/SQL procedure successfully completed.

SQL> /
Enter value for enterempno: 765a
Employee Number entered is not a Number.                                        
Try Again.....                                                                  

PL/SQL procedure successfully completed.
TOO_MANY_ROWS Exception

A SELECT statement can’t select more than one record at a time in PL/SQL. If more than one record is found then ORA-01422 is fired which is handled by TOO_MANY_ROWS.

--INVALID_NUMBER exception handler

DECLARE
  V_EName Emp.EName%TYPE;
  V_Job Emp.Job%TYPE;
BEGIN
  SELECT EName, Job
   INTO V_EName, V_Job
   FROM Emp
   WHERE Sal = &Salary;
  DBMS_OUTPUT.PUT_LINE(V_EName||' is a '||V_Job);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Sorry... No Data Found.');
    DBMS_OUTPUT.PUT_LINE('Salary doesn''t match.');
  WHEN INVALID_NUMBER THEN
    DBMS_OUTPUT.PUT_LINE('Sorry... Unable to Proceed with Invalid Numbers.');
    DBMS_OUTPUT.PUT_LINE('Try Again.....');
  WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE('Only one Record can be selected from database at a time.');
    DBMS_OUTPUT.PUT_LINE('Multiple records found with matching Salary.');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error in Input. Check for further Progress...');
END;
/

Enter value for salary: 800
SMITH is a CLERK

PL/SQL procedure successfully completed.

SQL> /
Enter value for salary: 1000
Sorry... No Data Found.                                                         
Salary doesn't match.                                                           

PL/SQL procedure successfully completed.

SQL> /
Enter value for salary: '500A'
Sorry... Unable to Proceed with Invalid Numbers.                                
Try Again.....                                                                  

PL/SQL procedure successfully completed.

SQL> /
Enter value for salary: 3000
Only one Record can be selected from database at a time.                        
Multiple records found with matching Salary.                                    

PL/SQL procedure successfully completed.
VALUE_ERROR Exception
--INVALID_NUMBER exception handler

DECLARE
  V_Empno NUMBER;
  V_Ename VARCHAR2(10) :='&GEName';
  V_Deptno NUMBER;
BEGIN
  V_Empno :='&GEmpno';
  V_Deptno :='&GDeptno';
  INSERT INTO Emp(Empno, Ename, Deptno)
  VALUES(V_Empno, V_Ename, V_Deptno);
  DBMS_OUTPUT.PUT_LINE('1 row created.');
EXCEPTION
  WHEN INVALID_NUMBER THEN
    DBMS_OUTPUT.PUT_LINE('Sorry... Unable to Proceed with Invalid Numbers.');
    DBMS_OUTPUT.PUT_LINE('Deptno or Empno is Invalid.');
    DBMS_OUTPUT.PUT_LINE('Try Again.....');
  WHEN VALUE_ERROR THEN
    DBMS_OUTPUT.PUT_LINE('Value Error ! Program Abruptly Stopped..');
    DBMS_OUTPUT.PUT_LINE('Try Again.....');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error in Input. Check for further Progress...');
END;
/

Enter value for gename: SAMPLE
Enter value for gempno: 1234
Enter value for gdeptno: 20
1 row created.

PL/SQL procedure successfully completed.

SQL> /
Enter value for gename: SAMPLE
Enter value for gempno: 123A
Enter value for gdeptno: 30
Value Error ! Program Abruptly Stopped..
Try Again.....

PL/SQL procedure successfully completed.
DUP_VAL_ON_INDEX Exception
--DUP_VAL_ON_INDEX exception handler

DECLARE
  V_Empno Emp.Empno%TYPE   := &enterEmpno;
  V_Ename Emp.Ename%TYPE   := '&enterEname';
  V_Deptno Emp.Deptno%TYPE := &enterDeptno;
BEGIN
  INSERT INTO Emp(Empno, Ename, Deptno)
  VALUES(V_Empno, V_Ename, V_Deptno);
  DBMS_OUTPUT.PUT_LINE('Employee Successfully Inserted.');
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    DBMS_OUTPUT.PUT_LINE('Insertion Failed !'); 
    DBMS_OUTPUT.PUT_LINE('EmployeeID already exists.');
END;
/

Enter value for gempno: 1234
Enter value for gename: Sample
Enter value for gdeptno: 40
Employee Successfully Inserted.                                                 

PL/SQL procedure successfully completed.

SQL> /
Enter value for gempno: 1234
Enter value for gename: Sample01
Enter value for gdeptno: 40
Insertion Failed !
EmployeeID already exists.                                                      

PL/SQL procedure successfully completed.
CURSOR_ALREADY_OPEN Exception
--CURSOR_ALREADY_OPEN exception handler

DECLARE
  V_EName Emp.EName%TYPE;
  V_Sal Emp.Sal%TYPE;
  V_RowCount PLS_INTEGER :=0;
  CURSOR EmpRowCount
   IS
   SELECT EName, Sal
   FROM Emp
   ORDER BY EName;
BEGIN
  OPEN EmpRowCount;
  LOOP
    FETCH EmpRowCount INTO V_EName, V_sal;
    EXIT WHEN EmpRowCount%NOTFOUND OR EmpRowCount%ROWCOUNT> 5;
    V_RowCount :=V_RowCount+1;
    DBMS_OUTPUT.PUT_LINE(V_EName||' gets Salary $'||V_Sal);
  END LOOP;
  OPEN EmpRowCount;
  DBMS_OUTPUT.PUT_LINE(V_RowCount||' Rows Processed so far.....');
  CLOSE EmpRowCount;
EXCEPTION
  WHEN CURSOR_ALREADY_OPEN THEN
    DBMS_OUTPUT.PUT_LINE('Cursor is opened more that once without closing it.');
END;
/

ADAMS gets Salary $1100                                                         
ALLEN gets Salary $1600                                                         
BLAKE gets Salary $2850                                                         
CLARK gets Salary $2450                                                         
FORD gets Salary $3000                                                          
Cursor is opened more that once without closing it.                             

PL/SQL procedure successfully completed.
INVALID_CURSOR Exception

INVALID_CURSOR is raised in a PL/SQL program in following 3 cases. Each case is explained in detail through an example.

  • INVALID_CURSOR is raised if cursor is closed more than once.
  • INVALID_CURSOR is raised if cursor is fetched without opening the cursor.
  • INVALID_CURSOR is raised if cursor is fetched after closing the cursor.

Case 1: When cursor is closed more than once

--INVALID_CURSOR exception handler

DECLARE
  V_EName Emp.EName%TYPE;
  V_Sal Emp.Sal%TYPE;
  V_RowCount PLS_INTEGER :=0;
  CURSOR EmpRowCount
   IS
   SELECT EName, Sal
   FROM Emp
   ORDER BY EName;
BEGIN
  OPEN EmpRowCount;
  LOOP
    FETCH EmpRowCount INTO V_EName, V_sal;
    EXIT WHEN EmpRowCount%NOTFOUND OR EmpRowCount%ROWCOUNT> 5;
    V_RowCount :=V_RowCount+1;
    DBMS_OUTPUT.PUT_LINE(V_EName||' gets Salary $'||V_Sal);
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(V_RowCount||' Rows Processed so far.....');
  CLOSE EmpRowCount;
  CLOSE EmpRowCount;
EXCEPTION
  WHEN INVALID_CURSOR THEN
    DBMS_OUTPUT.PUT_LINE('Invalid Cursor Error !');
    DBMS_OUTPUT.PUT_LINE('Same Cursor is closed more than once.');
END;
/

ADAMS gets Salary $1100                                                         
ALLEN gets Salary $1600                                                         
BLAKE gets Salary $2850                                                         
CLARK gets Salary $2450                                                         
FORD gets Salary $3000                                                          
5 Rows Processed so far.....                                                    
Invalid Cursor Error !                                                          
Same Cursor is closed more than once.                                           

PL/SQL procedure successfully completed.

Case 2: When cursor is fetched without opening the cursor

DECLARE
  V_EName Emp.EName%TYPE;
  V_Sal Emp.Sal%TYPE;
  V_RowCount PLS_INTEGER :=0;
  CURSOR EmpRowCount
   IS
   SELECT EName, Sal
   FROM Emp
   ORDER BY EName;
BEGIN
  --OPEN EmpRowCount;
  LOOP
    FETCH EmpRowCount INTO V_EName, V_sal;
    EXIT WHEN EmpRowCount%NOTFOUND OR EmpRowCount%ROWCOUNT> 5;
    V_RowCount :=V_RowCount+1;
    DBMS_OUTPUT.PUT_LINE(V_EName||' gets Salary $'||V_Sal);
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(V_RowCount||' Rows Processed so far.....');
  CLOSE EmpRowCount;
EXCEPTION
  WHEN INVALID_CURSOR THEN
    DBMS_OUTPUT.PUT_LINE('Invalid Cursor Error !');
    DBMS_OUTPUT.PUT_LINE('Cursor is Fetched without opening it.');
END;
/

Invalid Cursor Error !                                                          
Cursor is Fetched without opening it.

PL/SQL procedure successfully completed.

Case 3: When cursor is fetched after closing the cursor.

DECLARE
  V_EName Emp.EName%TYPE;
  V_Sal Emp.Sal%TYPE;
  V_RowCount PLS_INTEGER :=0;
  CURSOR EmpRowCount
   IS
   SELECT EName, Sal
   FROM Emp
   ORDER BY EName;
BEGIN
  OPEN EmpRowCount;
  LOOP
    FETCH EmpRowCount INTO V_EName, V_sal;
    EXIT WHEN EmpRowCount%NOTFOUND OR EmpRowCount%ROWCOUNT> 5;
    V_RowCount :=V_RowCount+1;
    DBMS_OUTPUT.PUT_LINE(V_EName||' gets Salary $'||V_Sal);
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(V_RowCount||' Rows Processed so far.....');
  CLOSE EmpRowCount;
  FETCH EmpRowCount INTO V_EName, V_sal;
EXCEPTION
  WHEN INVALID_CURSOR THEN
    DBMS_OUTPUT.PUT_LINE('Invalid Cursor Error !');
    DBMS_OUTPUT.PUT_LINE('Cursor is in use without opening it or...');
    DBMS_OUTPUT.PUT_LINE('Fetched After Closing it.');
END;
/

ADAMS gets Salary $1100                                                         
ALLEN gets Salary $1600                                                         
BLAKE gets Salary $2850                                                         
CLARK gets Salary $2450                                                         
FORD gets Salary $3000                                                          
5 Rows Processed so far.....                                                    
Invalid Cursor Error !                                                          
Cursor is in use without opening it or...                                       
Fetched After Closing it.

PL/SQL procedure successfully completed.
CASE_NOT_FOUND Exception

ELSE is optional in CASE declaration. If the values those are supplied exactly matches with the CASE expression values then it’s fine. But if a value supplied that does not match with the CASE expression value then CASE_NOT_FOUND exception will arise.

DECLARE
  V_Grade CHAR :=UPPER('&EnterGrade');
BEGIN
  CASE V_Grade
    WHEN 'A' THEN
      DBMS_OUTPUT.PUT_LINE('You are awarded with Excellent Grade.');
    WHEN 'B' THEN
      DBMS_OUTPUT.PUT_LINE('You are awarded with Very Good Grade.');
    WHEN 'C' THEN
      DBMS_OUTPUT.PUT_LINE('You are awarded with Good Grade.');
    WHEN 'D' THEN
      DBMS_OUTPUT.PUT_LINE('You are awarded with Fair Grade.');
    WHEN 'E' THEN
      DBMS_OUTPUT.PUT_LINE('You are awarded with Poor Grade.');
    WHEN 'F' THEN
      DBMS_OUTPUT.PUT_LINE('Sorry ! You have Failed.');
  END CASE;
EXCEPTION
  WHEN CASE_NOT_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('The supplied Grade '||V_Grade||' not found.');
    DBMS_OUTPUT.PUT_LINE('Try Once Again.....');
END;
/

/
Enter value for entergrade: d
You are awarded with Fair Grade.                                                

PL/SQL procedure successfully completed.

SQL> /
Enter value for entergrade: r
The supplied Grade R not found.                                                 
Try Once Again.....                                                             

PL/SQL procedure successfully completed.
ZERO_DIVIDE Exception
DECLARE
  V_Num1 NUMBER :=&GiveNumber1;
  V_Num2 NUMBER :=&GiveNumber2;
  V_Result NUMBER;
BEGIN
  V_Result :=V_Num1/V_Num2;
  DBMS_OUTPUT.PUT_LINE('The result is : '||V_Result);
EXCEPTION
  WHEN ZERO_DIVIDE THEN
    DBMS_OUTPUT.PUT_LINE('Fatal Error ! Division by Zero occured. ');
END;
/

/
Enter value for givenumber1: 22
Enter value for givenumber2: 5
The result is : 4.4                                                             

PL/SQL procedure successfully completed.

SQL> /
Enter value for givenumber1: 6
Enter value for givenumber2: 0
Fatal Error ! Division by Zero occured.                                         

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

How can we help?