EXCEPTIONS IN PL-SQL

TYPES OF EXCEPTIONS
Exceptions in PL/SQL are classified into predefined exceptions and user defined exceptions. The exceptions that are already defined by Oracle are called as Predefined exceptions and the exceptions that are created by the user are called as User Defiend exceptions.
Predefiend Exceptions : Predefiend exceptions are the exceptions that are already defiend by Oracle, and can be used directly with out any coresponding declaration statement. Predefiend exceptions and their purpose are as follows.

1.DUP_VAL_ON_INDEX : This exception is raised when duplicate values are inserted into primary key or unique key columns.

DECLARE
DNO NUMBER(10):=&DN;
DNA VARCHAR2(10):=‘&DNA’;
L VARCHAR2(10):= ‘&L’;
BEGIN
INSERT INTO DEPT VALUES ( DNO, DNA, L);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
DBMS_OUTPUT.PUT_LINE( ‘Duplicate value given to primary key column’ );
End;
2.NO_DATA_FOUND : This exception is raised when a select statement doen’t return any value.

DECLARE
J EMP.JOB%TYPE;
ENO EMP.EMPNO%TYPE := ‘&ENO’;
BEGIN
SELECT JOB INTO J FROM EMP WHERE EMPNO=ENO;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘EMPNO NOT FOUND.’);
END;

3.TOO_MANY_ROWS : This exception is raised when more than one rows are returned by a select statement.
DECLARE
J EMP.JOB%TYPE;
EN EMP.ENAME%TYPE := ‘&ENAME’;
BEGIN
SELECT JOB INTO J FROM EMP WHERE EMPNO=ENO;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE( ‘MORE THAN ONE RECORD RETURNED’ );
END;
4.INVALID_ NUMBER : This excepton is raised when a non numeric is assigned to a numeric column.
DECLARE
BEGIN
INSERT INTO EMP VALUES ( ‘AA’, ‘DNAME’, ‘LOC’ );
EXCEPTION
WHEN INVALID_NUMBER THEN
DBMS_OUTPUT.PUT_LINE(‘NON NUMERIC VALUE IS GIVEN TO COLUMN’);
END;
5.VALUE_ERROR : This exception is raised when a value larger than width of a variable s assigned to a variable.
DECLARE
C VARCHAR2(3);
BEGIN
C:= ‘ABCD’;
EXCEPTION
WHEN VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE(‘Value larger than specified width’);
END;
6.INVALID_CURSOR : This exception will be raised when an invalid cursor operation is performed.
DECLARE
CURSOR CDEPT IS SELECT * FROM DEPT;
DNO DEPT.DNO%TYPE;
DN DEPT.DN%TYPE;
L DEPT.LOC%TYPE;
BEGIN
FETCH CDEPT INTO DNO, DN, L;
EXCEPTION
WHEN INVALID_CURSOR THEN
DBMS_OUTPUT.PUT_LINE(‘INVALID OPERATION ON CURSOR’ );
END;

7.CURSOR_ALREADY_OPEN : This exception will be raised when an User tries to open a cursor that is already open.
DECLARE
CURSOR CDEPT IS SELECT * FROM DEPT;
BEGIN
OPEN CDEPT;
OPEN CDEPT;
EXCEPTION
WHEN CURSOR_ALREADY_OPEN THEN
DBMS_OUTPUT.PUT_LINE(‘CURSOR IS ALREADY OPENED’ );
END;
8.ZERO_DIVIDE : This exception will be raised when a division is made by using Zero.
9.STORAGE ERROR : This exception will be raised when PL/SQL runs out of memory.
10.NOT_LOGGED_ON : This exception will be raised when you tried to execute an SQL statement against the database and database was not ready.
11.LOGON_DENIED : This exception will be raised when an invalid username and password are given.

USER DEFINED EXCEPTIONS
Oracle also provides the ability to create users own exceptions. For this user has to declare an exception variable in Declaration section. After declaring an exception user has to raise it manually depending on a condition by using raised statement.
DECLARATION : Exception_Variable Exception;
RAISING : Raise Exception_Variable;
The following PL/SQL block created an user defined exception for handling the error raised when user inserts a foreign key violating value into deptno column of EMP table.
DECLARE
ENO EMP.ENO%TYPE := &ENO;
EN EMP.ENAME%TYPE := ‘&EN’;
J EMP.JOB%TYPE := ‘&J’;
M EMP.MGR%TYPE := ‘&M’;
HD EMP.HIREDATE%TYPE := ‘&HD’;
S EMP.SAL%TYPE := &S;
C EMP.COMM%TYPE := &C;
DNO EMP.DEPTNO%TYPE := &DNO;
DNOFK EXCEPTION;
BEGIN
IF DNO > 40 THEN
RAISE DNOFK;
END IF;
INSERT INTO EMP VALUES ( ENO, EN, J, M, HD, S, C, DNO );
EXCEPTION
WHEN DNOFK THEN
DBMS_OUTPUT.PUT_LINE(‘DEPTNO VALUES MUST BE 20, 30 OR 40’ );
END;
“OTHERS” EXCEPTION HANDLER :The “OTHERS” handler will execute for all raised exceptions it should always be the last handler in the block. It is good programming practice that an others handler is placed at the top level of your program to ensure that no errors go and detected.
SQL CODE AND SQL-ERRORS : Inside an “OTHERS” handler, it is often useful to know which Oracle error raised the exception. One reason would be to log which error occurred rather than the fact that an error happened. PL/SQL provides this information via to built in function, SQL code and SQLERRM. SQL code returns the current error code and SQL-Errm returns the current error message text.
DECLARE
ENO EMP.ENO%TYPE := &ENO;
EN EMP.ENAME%TYPE := ‘&EN’;
J EMP.JOB%TYPE := ‘&J’;
M EMP.MGR%TYPE := ‘&M’;
HD EMP.HIREDATE%TYPE := ‘&HD’;
S EMP.SAL%TYPE := &S;
C EMP.COMM%TYPE := &C;
DNO EMP.DEPTNO%TYPE := &DNO;
DNOFK EXCEPTION;
BEGIN
IF DNO > 40 THEN
RAISE DNOFK;
END IF;
INSERT INTO EMP VALUES ( ENO, EN, J, M, HD, S, C, DNO );
EXCEPTION
WHEN DNOFK THEN
DBMS_OUTPUT.PUT_LINE(‘DEPTNO VALUES MUST BE 20, 30 OR 40’ );
WHEN DUP_VAL_ON_INDEX THEN
DBMS_OUTPUT.PUT_LINE( ‘DUPLICATE VALUES GIVEN TO EMP’ );
WHEN VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE( ‘VALUE LONGER THAN SIZE OF COLUMN’ );
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE( SQL CODE ’ : ’ SQLERRM );
END;