Live ORACLE

DECLARE
 salary_too_high  EXCEPTION;
 current_salary    NUMBER := 20000;
 max_salary        NUMBER := 10000;
 erroneous_salary  NUMBER;
BEGIN
 BEGIN ---------- sub-block begins
   IF current_salary > max_salary THEN
     RAISE salary_too_high; -- raise the exception
   END IF;
 EXCEPTION
   WHEN salary_too_high THEN
     -- first step in handling the error
     dbms_output.Put_line('Salary '
                          ||erroneous_salary
                          ||' is out of range.');
     dbms_output.Put_line('Maximum salary is '
                          ||max_salary
                          ||'.');
     RAISE; -- reraise the current exception

 END; ------------ sub-block ends

EXCEPTION
 WHEN salary_too_high THEN
   -- handle the error more thoroughly
   erroneous_salary := current_salary;
   current_salary := max_salary;
   dbms_output.Put_line('Revising salary from '
                        ||erroneous_salary
                        ||' to '
                        ||current_salary
                        ||'.');
END;
/  

Salary  is out of range.
Maximum salary is 10000.
Revising salary from 20000 to 10000.

PL/SQL procedure successfully completed.

Reraising a PL/SQL Exception

BEGIN
DECLARE ---------- sub-block begins
 past_due  EXCEPTION;
 due_date     DATE := Trunc(SYSDATE)
                        - 1;
 todays_date  DATE := Trunc(SYSDATE);
BEGIN
 IF due_date < todays_date THEN
   RAISE past_due;
 END IF;
END; ------------- sub-block ends

EXCEPTION
WHEN OTHERS THEN
 ROLLBACK;
END;
/  

PL/SQL procedure successfully completed.

BEGIN
DECLARE ---------- sub-block begins
  past_due  EXCEPTION;
  due_date     DATE := Trunc(SYSDATE)
                         - 1;
  todays_date  DATE := Trunc(SYSDATE);
BEGIN
  IF due_date < todays_date THEN
    RAISE past_due;
  END IF;
END; ------------- sub-block ends

--EXCEPTION
--WHEN OTHERS THEN
--ROLLBACK;
END;
/ 

BEGIN
*
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at line 8

Scope of an Exception

DECLARE
 acct_type  INTEGER := 7;
BEGIN
 IF acct_type NOT IN (1,2,3) THEN
   RAISE invalid_number; -- raise predefined exception
 END IF;
EXCEPTION
 WHEN invalid_number THEN
   dbms_output.Put_line('HANDLING INVALID INPUT BY ROLLING BACK.');
   ROLLBACK;
END;
/  

HANDLING INVALID INPUT BY ROLLING BACK.

PL/SQL procedure successfully completed.

Using RAISE to Force a Pre-Defined Exception

DECLARE
 out_of_stock  EXCEPTION;
 number_on_hand  NUMBER := 0;
BEGIN
 IF number_on_hand < 1 THEN
   RAISE out_of_stock; -- raise an exception that we defined
 END IF;
EXCEPTION
 WHEN out_of_stock THEN
   -- handle the error
   dbms_output.Put_line('Encountered out-of-stock error.');
END;
/  

Encountered out-of-stock error.

PL/SQL procedure successfully completed.

Using RAISE to Force a User-Defined Exception

DECLARE
 num_tables  NUMBER;
BEGIN
 SELECT Count(* )
 INTO   num_tables
 FROM   user_tables;
 IF num_tables < 1000 THEN
   /* Issue your own error code (ORA-20101) with your own error message.
   Note that you do not need to qualify raise_application_error with
   DBMS_STANDARD */
   Raise_application_error(-20101,'Expecting at least 1000 tables');
 ELSE
   NULL; -- Do the rest of the processing (for the non-error case).
 END IF;
END;
/  

DECLARE
*
ERROR at line 1:
ORA-20101: Expecting at least 1000 tables
ORA-06512: at line 9

Raising an Application Error With raise_application_error

DECLARE
 past_due  EXCEPTION;
 acct_num  NUMBER;
BEGIN
 DECLARE ---------- sub-block begins
   past_due  EXCEPTION; -- this declaration prevails
   acct_num     NUMBER;
   due_date     DATE := SYSDATE
                          - 1;
   todays_date  DATE := SYSDATE;
 BEGIN
   IF due_date < todays_date THEN
     RAISE past_due; -- this is not handled
   END IF;
 END; ------------- sub-block ends

EXCEPTION
 WHEN past_due THEN -- does not handle raised exception

   dbms_output.Put_line('Handling PAST_DUE exception.');
 WHEN OTHERS THEN
   dbms_output.Put_line('Could not recognize PAST_DUE_EXCEPTION in this scope.');
END;
/  

Could not recognize PAST_DUE_EXCEPTION in this scope.

PL/SQL procedure successfully completed.

Scope of PL/SQL Exceptions

DECLARE
emp_column  VARCHAR2(30) := 'last_name';
table_name  VARCHAR2(30) := 'emp';
temp_var    VARCHAR2(30);
BEGIN
temp_var := emp_column;
SELECT column_name
INTO   temp_var
FROM   user_tab_cols
WHERE  table_name = 'EMPLOYEES'
      AND column_name = Upper(emp_column);
-- processing here
temp_var := table_name;
SELECT object_name
INTO   temp_var
FROM   user_objects
WHERE  object_name = Upper(table_name)
      AND object_type = 'TABLE';
-- processing here
EXCEPTION
WHEN no_data_found THEN -- catches all 'no data found' errors

 dbms_output.Put_line('No Data found for SELECT on '
                      ||temp_var);
END;
/

No Data found for SELECT on emp

PL/SQL procedure successfully completed.

Managing Multiple Errors With a Single Exception Handler

DECLARE
 stock_price   NUMBER := 9.73;
 net_earnings  NUMBER := 0;
 pe_ratio      NUMBER;
BEGIN
 -- Calculation might cause division-by-zero error.
 pe_ratio := stock_price
               / net_earnings;
 dbms_output.Put_line('Price/earnings ratio = '
                      ||pe_ratio);
EXCEPTION -- exception handlers begin

 -- Only one of the WHEN blocks is executed.
 WHEN zero_divide THEN -- handles 'division by zero' error

   dbms_output.Put_line('Company must have had zero earnings.');
   pe_ratio := NULL;
 WHEN OTHERS THEN -- handles all other errors

   dbms_output.Put_line('Some other kind of error occurred.');
   pe_ratio := NULL;
END; -- exception handlers and block end here
/  

Company must have had zero earnings.

PL/SQL procedure successfully completed.

Runtime Error Handling