Exception in pl/sql

Exception: are some undesired situations when PL/SQL program terminated unexpectedly.

EXCEPTION HANDLING is used to handle run-time errors. All the Exception Handling code is written in the EXCEPTION section of a PL/SQL program.

DECLARE

/*this block is used for variable declaration, cursor/record definition */

BEGIN

/*this block is used to define the SQL-PL/SQL statements*/

EXCEPTION

WHEN EXCEPTION_NAME THEN

--error handling code

WHEN EXCEPTION_NAME THEN

--error handling code

WHEN OTHERS THEN

--error handling code

END;

There are three types of exceptions. These are:

1. Predefined Exceptions: these are the common errors that have given predefined names, displayed on screen.

Exception Name	Description
NO_DATA_FOUND	When select statement doesn’t return any record
TOO_MANY_ROWS	When select statement return more than one record.
ZERO_DIVIDE	When any no is to be divide by zero.
DUP_VAL_ON_INDEX	Attempt to insert duplicate value.
INVALID_CURSOR	Illegal cursor operation occurred

Write PL/SQL script to input sal amt and display the Emp Name earning same sal amt. Use NO_DATA_FOUND and TOO_MANY ROWS Exception.

DECLARE

SALARY NUMBER;

E_NAME EMP.ENAME%TYPE;

BEGIN

SALARY := &SAL;

SELECT ENAME INTO E_NAME FROM EMP WHERE SAL=SALARY;

DBMS_OUTPUT.PUT_LINE(E_NAME) ;

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE(‘INVALID VALUE’);

WHEN TOO_MANY_ROWS THEN

DBMS_OUTPUT.PUT_LINE(‘return multiple value’);

END;

2. Undefined Exceptions: these are the less common errors that have not given predefined names. For these errors ORA – error code i.e., the system compilation error messages are displayed.

3. User-defined Exceptions: errors that do not cause a run-time error, but will violate business rules. Such errors are handled through programmer created custom error messages. User-defined Exception is raised with RAISE .

Write PL/SQL script that traps ZERO_DIVIDE exception when a no is divided by other no. also raised user define exception if no2 greater than no1.

DECLARE

NO1 NUMBER;

NO2 NUMBER;

N2_GT_N1 EXCEPTION;    --user-defined exception

BEGIN

NO1 := &N1;

NO2 := &N2;

IF NO2 > NO1 THEN

RAISE N2_GT_N1;   --user-defined exception raised.

END IF;

DBMS_OUTPUT.PUT_LINE(NO1 / NO2);

EXCEPTION

WHEN ZERO_DIVIDE THEN

DBMS_OUTPUT.PUT_LINE(‘not divide by zero’);

WHEN N2_GT_N1 THEN

DBMS_OUTPUT.PUT_LINE(‘no2 greater than no1’);

END;