PL/SQL Exception

Exception is an error occurred during runtime whenever runtime error occurred use an appropriate exception name in exception handler under exception section.

There are three types of exceptions supported by oracle:

  1. PREDEFINE Exception
  2. USERDEFINE Exception
  3. UNAMED Exception

PREDEFINE Exception:

Oracle defined 20 predefined exception names for regularly occurred runtime error whenever runtime error occurred, use an appropriate predefined exception name in exception handler under exception section, Below is the syntax:

EXCEPTION WHEN PREDEFINE EXCEPTION THEN

Executable Statements;

When Predefined Exception Name2 then

Executable Statements;

…………………………………

…………………………………

When OTHERS THEN

EXECUTABLE STATEMENTS;

PRE DEFINE EXCEPTIONS:

  1. NO_DATA_FOUND         => ORA-1403 => NO DATA FOUND
  2. TOO_MANY_ROWS        => ORA-1402 => Exact fetch return more than requested number of rows
  3. ZERO_DIVIDE                     => ORA-1476 =>Divisor is equal to zero
  4. CURSOR_ALREADY_OPEN            => ORA-6511 => Cursor already Open
  5. INVALID_CURSOR           => ORA-1001 => Invalid cursor
  6. INVALID_NUMBER          => ORA-1722 => Invalid Number
  7. VALUR_ERROR                  => ORA-6502 =>Number or value error character to number conversion error

OTHERS

‘WHEN OTHERS’ Clause is used to trap all the remaining exceptions that have not been handled by your named exception.

NO_DATA_FOUND

‘NO_DATA_FOUND’ is a clause used in exception section when a PL/SQL block contain ‘SELECT INTO CLAUSE’ and also if requested data is not available in a table then oracle server returns an error ‘ORA-1403: NO DATA FOUND’ Exception Name.

DECLARE

V_ENAME VARCHAR2(10);

V_SAL NUMBER;

BEGIN

Select ename, sal INTO v_ename, v_sal from emp

Where empno  = &empno;

Dbms_output.put_line(v_ename||’ ‘||v_sal);

EXCEPTION

WHEN NOT_DATA_FOUND THEN

Dbms_output.put_line(‘Empno Does not Exist’);

END;

Run: Enter vaue for empno  = 7839

O/P: KING 5000

Run: Enter value for empno = 1234

O/P: Empno does not Exists

TOO_MANY_ROWS

‘TOO_MANY_ROWS’ is a clause used in exception section when a ‘SELECT INTO CLAUSE’ try to return more than one record or more than one value at a time, then Oracle server returns an error ‘ORA-1422: Exact fetch returns more than requested number of rows’, to handle this error oracle provided ‘TOO_MANY_ROWS’ exception name.

DECLARE

V_SAL NUMBER(10);

BEGIN

SELECT SAL INTO V_SAL FROM EMP;

Dbms_output.put_line(v_sal);

EXCEPTION

When TOO_MANY_ROWS THEN

Dbms_output.put_line(‘Not to return more than one value’);

END;

O/P: Not to return more than one values

ZERO_DIVIDE

ZERO_DIVIDE’ is a clause used in exception section whenever we are performing any mathematical calculation and also if denominator value is equal to zero, then oracle server returns an error ‘ORA-1476: Divisor is equal to zero’, to handle this error Oracle provided ‘ZERO_DIVIDE’ exception name.

DECLARE

A NUMBER(10);

B NUMBER(10)

C NUMBER(10);

BEGIN

A := 100;

B := 0;

C := A/B;

Dbms_output.put_line(C);

EXCEPTION

When ZERO_DIVIDE THEN

Dbms_output.put_line(‘Value of B cannot be zero’);

END;

O/P=> Value of B cannot be zero

This means that denominator value should not be equal t zero.

CURSOR_ALREADY_OPEN

Whenever we are trying to reopen the cursor without closing the cursor or before we are reopening the cursor we must close the cursor properly, otherwise oracle server returns an error ‘ORA-6511: Cursor already open’, to handle this error oracle provided ‘CURSOR_ALREADY_OPEN’ Exception name.

DECLARE

CURSOR C1 IS SELECT * FROM EMP WHERE ROWNUM<=10;

I EMP%ROWTYPE;

BEGIN

OPEN C1;

LOOP

FETCH C1 INTO I;

EXIT WHEN C1%NOTFOUND;

Dbms_output.put_line(i.ename);

END LOOP;

OPEN C1;

EXCEPTION

WHEN CURSOR_ALREADY_OPEN THEN

Dbms_output.put_line(‘We must close the cursor before reopen the cursor’);

END;

INVALID_CURSOR

In all database system whenever we are performing invalid operations on the cursor, then database server returns error, In oracle when we are not opening the cursor but we are trying to perform operations on the cursor, then oracle server returns an error ‘ORA-1001 Invalid Cursor’, to handle this error Oracle provided ‘Invalid_Cursor’ exception name.

DECLARE

CURSOR C1 IS select * from emp where sal>2000;

I emp%rowtype;

BEGIN

LOOP

FETCH C1 INTO I;

EXIT WHEN C1%NOTFOUND

Dbms_output.put_line(i.ename||’ ‘||i.sal);

END LOOP;

CLOSE C1;

EXCEPTION

WHEN INVALID_CURSOR THEN

Dbms_output.put_line(‘First we must open the cursor’);

END;

INVALID_NUMBER (or) VALUE_ERROR

In Oracle if you are trying to convert string type to number type or string type to date type, then oracle server returns two types of errors, these are INVALID NUMBER, VALUE ERROR.

When a PL/SQL block contains SQL statement and also if you are trying to convert string type to number type then oracle server returns error ‘ORA-1722 INVALID_NUMBER’ Exception Name.

BEGIN

Insert into emp(empno, ename,sal) values(1,’SAM’,’ABC’);

Exception

WHEN INVALID_NUMBER THEN

Dbms_output.put_line(‘Insert proper data only’);

END;

O/P=> Insert Proper data only

VALUE_ERROR

When a PL/SQL block contains procedural statements and also these statements try to convert string type to number type. Then Oracle server returns an error ‘ORA-6502: Numeric or value error: charcter to Number conversion Error’, to handle this error oracle provided  ‘VALUE_ERROR’ Exception name.

DECLARE

Z NUMBER(10);

BEGIN

Z:=&X+&Y;

DBMS_OUTPUT.PUT_LINE(Z);

EXCEPTION

WHEN VALUE_ERROR THEN

Dbms_output.put_line(‘Enter proper data only’);

END;

Note: Whenever we are try to store large amount of data than the specified datatype size in character datatype in variable declaration, then oracle server returns an error ‘ORA-6502: Numeric or value error: character string buffer too small’, to handle this error oracle provided ‘VALUE_ERROR’ Exception name.

DECLARE

Z VARCHAR2(3);

BEGIN

Z := ‘ABCD’

Dbms_output.put_line(Z);

EXCEPTION

WHEN VALUE_ERROR THEN

Dbms_output.put_line(‘Invalid String Length’);

END;

Whenever we are trying to store more data than the datatype size specified in Number datatype then oracle server returns an error ‘ORA-6502: Numeric or value error: Number precision too large’, to handle this error we are using ‘VALUE_ERROR’ Exception Name.

DECLARE

Z NUMBER(2);

BEGIN

Z :=9999;

Dbms_output.put_line(Z);

EXCEPTION

WHEN VALUE_ERROR THEN

Dbms_output.put_line(‘Not to store data more than datatype size’);

END;