Show Menu
Cheatography
  1. Home >
  2. Cheat Sheets >

PL/SQL Exceptions Cheat Sheet (DRAFT) by

This is a draft cheat sheet. It is a work in progress and is not finished yet.

PL SQL error handling

exception propagates outward to each successive block , until properly handled or presented to client finally
EXCEPTION
without it,error present to client
WHEN except­ion­_name
trap an exception
THEN ....
handle an exception
RAISE
raise the current except

Types of Exception

Named System Exceptions
Unnamed System Exceptions
User-d­efined Exceptions

Named System Exceptions

NO_DAT­A_FOUND
SELECT...INTO with no returned
ZERO_D­IVIDE
TOO_MA­NY_ROWS
fetch >1 row into a record/var
INVALI­D_C­URSOR
cursor closed

Unamed system exceptions

WHEN OTHERS THEN
OTHERS handler used
to handle explicitly
except­ion­_name EXCEPTION;
PRAGMA EXCEPT­ION­_INIT (excep­tio­n_name, Err_code);

User defined exception

my_exc­eption EXCEPTION;
raise my_exc­eption;
pragma except­ion­_in­it(­my_­exc­ept­ion­,-2­0001);
assign error code
RAISE_­APP­LIC­ATI­ON_­ERR­OR(­cod­e,msg)
assign a user friendly message
WHEN my_exc­eption THEN
trap and handle
Explicitly declared in declar­ation ,raised in execution section and handled in Exception section
 

raise_­app­lic­ati­on_­err­or(­code, msg)

predefined oracle procedure for user raise error with code and messag
1) error code range(­req­uired)
-20,000 ~ -20,999
2) message
varcha­r2(­2000)
3)boolean: default false-­added to top of list of all errors
true: adds the error to the current stack
from DBMS_S­TAN­DARD, no name provided, handled with OTHERS handler. Uncomm­itted session tx rollback

raise_­app­lic­ati­on_­error with handler

DECLARE
  n_salary NUMBER;
  sal_high   EXCEPTION;
  pragma exception_init(sal_high,-20001);
BEGIN
  SELECT salary INTO n_salary 
  FROM employees 
  WHERE employee_id=:employee_id;

  IF n_salary >10000 THEN
     raise_application_error
           (-20001,'Salary is high');
  END IF;
EXCEPTION
   WHEN sal_high THEN
      dbms_output.put_line(SQLCODE);
      dbms_output.put_line(sqlerrm);
END;
raise; //raise current error