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:
- PREDEFINE Exception
- USERDEFINE Exception
- 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:
- NO_DATA_FOUND => ORA-1403 => NO DATA FOUND
- TOO_MANY_ROWS => ORA-1402 => Exact fetch return more than requested number of rows
- ZERO_DIVIDE => ORA-1476 =>Divisor is equal to zero
- CURSOR_ALREADY_OPEN => ORA-6511 => Cursor already Open
- INVALID_CURSOR => ORA-1001 => Invalid cursor
- INVALID_NUMBER => ORA-1722 => Invalid Number
- 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;