This is a draft cheat sheet. It is a work in progress and is not finished yet.
PL SQL error handlingexception propagates outward to each successive block , until properly handled or presented to client finally | EXCEPTION | without it,error present to client | WHEN exception_name | trap an exception | THEN .... | handle an exception | RAISE | raise the current except |
Types of ExceptionNamed System Exceptions | Unnamed System Exceptions | User-defined Exceptions |
Named System ExceptionsNO_DATA_FOUND | SELECT...INTO with no returned | ZERO_DIVIDE | TOO_MANY_ROWS | fetch >1 row into a record/var | INVALID_CURSOR | cursor closed |
Unamed system exceptionsWHEN OTHERS THEN | OTHERS handler used | to handle explicitly | exception_name EXCEPTION; | PRAGMA EXCEPTION_INIT (exception_name, Err_code); |
User defined exceptionmy_exception EXCEPTION; | raise my_exception; | pragma exception_init(my_exception,-20001); | assign error code | RAISE_APPLICATION_ERROR(code,msg) | assign a user friendly message | WHEN my_exception THEN | trap and handle |
Explicitly declared in declaration ,raised in execution section and handled in Exception section
| | raise_application_error(code, msg)predefined oracle procedure for user raise error with code and messag | 1) error code range(required) | -20,000 ~ -20,999 | 2) message | varchar2(2000) | 3)boolean: default false-added to top of list of all errors | true: adds the error to the current stack | from DBMS_STANDARD, no name provided, handled with OTHERS handler. Uncommitted session tx rollback |
raise_application_error with handlerDECLARE
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
| | |