Oracle PLSQL exception handling

For table structure follows my another blog  for oracle trigger example.

• TOO_MANY_ROWS: When your select statement return multiple rows and that situation need to handle then you can use the TOO_MANY_ROWS exception.
• OTHERS :  Others is a generic exception which will occur at bottom.

You can handle Exception within exception. If you knows JAVA exception handling then it is very easy to understand. It is almost work like JAVA exception handling.


Example

 CREATE OR REPLACE PROCEDURE EXCEPTION_TEST(test in VARCHAR2) AS
  EMP_NAME VARCHAR2(200);  
BEGIN
SELECT NAME  INTO EMP_NAME FROM TBL_EMPLOYEE;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
BEGIN
SELECT NAME  INTO EMP_NAME FROM TBL_EMPLOYEE WHERE ID=1;
EXCEPTION
WHEN OTHERS THEN
EMP_NAME := 'NOT FOUND';
END;
WHEN OTHERS THEN
  EMP_NAME := 'NOT FOUND';
END EXCEPTION_TEST;