EXCEPTION HANDLING IN PLSQL

EXCEPTION HANDLING IN PLSQL

Errors in pl/sql block can be handled...error handling refers to the way we handle the errors in pl/sql block so that no crashing stuff of code takes place...This is exactly the same as we do in C++ or java..right!!
There are two type:
===> predefined exceptions
===> user defined exceptions
The above 2 terms are self explanatory

predefined exceptions:

No-data-found == when no rows are returned
Cursor-already-open == when a cursor is opened in advance
Dup-val-On-index == for duplicate entry of index..
Storage-error == if memory is damaged
Program-error == internal problem in pl/sql
Zero-divide == divide by zero
invalid-cursor == if a cursor is not open and u r trying to close it
Login-denied == invalid user name or password
Invalid-number == if u r inserting a string datatype for a number datatype which is already declared
Too-many-rows == if more rows r returned by select statement

SYNTAX

begin
sequence of statements;
exception
when --exception name then
sequence of statements;
end;

EXAMPLES

--When there is no data returned by row
declare
price item.actualprice%type;
begin
Select actual price into price from item where qty=888;
when no-data-found then
dbms_output.put_line('item missing');
end;

--EXAMPLE OF USER DEFINED EXCEPTION

DECLARE
e_rec emp%ROWTYPE;
e1 EXCEPTION;
sal1 emp.sal%TYPE;
BEGIN
SELECT sal INTO sal1 FROM emp WHERE deptno = 30 AND ename = 'John';
IF sal1 < 5000 THEN
RAISE e1;
sal1 := 8500;
UPDATE emp SET sal = sal1 WHERE deptno = 30 AND ename = 'John';
END IF;
EXCEPTION
WHEN no_data_found THEN
RAISE_APPLICATION_ERROR (-20001, 'John is not there.');
WHEN e1 THEN
RAISE_APPLICATION_ERROR (-20002, 'Less Salary.');
END;

--EXAMPLE OF RAISE-APPLICATION-ERROR... THIS IS YOUR OWN ERROR STATEMENT...YOU RAISE YOUR OWN ERROR

Declare
s1 emp.sal %type;
begin
select sal into s1 from emp where ename='SOMDUTT';
if(no-data-found) then
raise_application_error(20001, 'somdutt is not there');
end if;
if(s1 > 10000) then
raise_application_error(20002, 'somdutt is earing a lot');
end if;
update emp set sal=sal+500 where ename='SOMDUTT';
end;

--INTERESTING EG OF USER DEFINED EXCEPTIONS

Declare
zero-price exception;
price number(8);
begin
select actualprice into price from item where ordid =400;
if price=0 or price is null then
raise zero-price;
end if;
exception
when zero-price then
dbms_output.put_line('raised xero-price exception');
end;