Whenever you create a program block (no matter it is a trigger, procedure, package, …) you have to face errors, in Oracle PL/SQL they are called “exceptions”. We distinguish two types of exceptions: system (or internal) and user-defined exceptions.
What is an exception
As mentioned above, exceptions are blocks inside PL/SQL codes that are triggered at certain occasions by “exception handlers”. In exception blocks, you can define how that particular “event” will be treated. For instance, you can stop the whole processing, log the error and continue, skip to another part of your code and so on.
How does it work
When your code ends up with an error there are two possible scenarios. You either have an exception handler implemented or you don’t 🙂 If you don’t, the program will stop and you will have no idea what happened – whether it was successful or not. Most internal/system exceptions are logical problems in your process flow (such as dividing by zero, no data found, invalid number, too many rows, …) but you can also implement some business rules as cross-checks in your code logic (such as input parameter checks, no available funds, not authorized, …).
The biggest advantage of using exceptions is the scalability, readability and convenient way of error checking 🙂 Instead of checking every single line in your program for errors (which would be the case without exceptions) you can easily handle them in the exception block (see below).
How to use it
This is the part when I will show you all types of exceptions (internal as well as user-defined) and how you can use them 🙂 Let me start with internal exceptions.
These exceptions are raised when an Oracle rule is violated or any kind of system limit is exceeded. There is a certain list of exceptions with associated error numbers. Even though you know the error number, you have to handle exceptions by their names. See the list of the most common exceptions below (for the full list, please visit Oracle Doc pages).
|Exception name||Error Code||Oracle ORA||Description|
|CURSOR_ALREADY_OPEN||-6511||ORA-06511||You cannot re-open already opened cursor; you have to close it first. Note, that FOR loop automatically opens the given cursor.|
|INVALID_CURSOR||-1||ORA-00001||You attempted to try to do an illegal operation. The most common action causing this error is closing a curser that was not opened.|
|INVALID_NUMBER||-1722||ORA-01722||You tried to store a string value as a number that has non-numeric character (i,e, 1x0203023).|
|LOGIN_DENIED||-1017||ORA-01017||You used an invalid username and/or password in your program.|
|NO_DATA_FOUND||-1403||ORA-01403||Your operation did not retrieve any data. Common operations for retrieval are SELECT INTO, refer a deleted value in nested tables or un-initialized element in an index-by table.|
|NOT_LOGGED_ON||-1012||ORA-01012||You were not logged in while trying to execute an operation.|
|ROWTYPE_MISMATCH||-6504||ORA-06504||You tried to assign a value to an incompatible data type.|
|TOO_MANY_ROWS||-1422||ORA-01422||You SELECT INTO statement returned more than one row.|
|ZERO_DIVIDE||-1476||ORA-01476||You cannot divide by zero 🙂|
For the rest that is not defined by Oracle, you can use either user-defined exceptions (see below) or simple use OTHERS. For the latter choice, there are very handy “variables” you can use SQLERRM and SQLCODE to display details about the error.
DECLARE v_dividend NUMBER := 10; v_divisor NUMBER := 0; v_quotient NUMBER; BEGIN v_quotient := v_dividend / v_divisor; dbms_output.put_line(v_dividend||'/'||v_divisor||' = '||v_quotient); EXCEPTION WHEN ZERO_DIVIDE THEN dbms_output.put_line('You cannot divide by zero!'); END;
DECLARE v_dividend NUMBER := 10; v_divisor NUMBER := 0; v_quotient NUMBER; BEGIN v_quotient := v_dividend / v_divisor; dbms_output.put_line(v_dividend||'/'||v_divisor||' = '||v_quotient); EXCEPTION WHEN OTHERS THEN dbms_output.put_line('ErrMsg: '||SQLERRM); dbms_output.put_line('ErrCde: '||SQLCODE); END;
Of course, you can define multiple exceptions handler in the EXCEPTION block (see below).
As I mentioned above, you can implement your own business logic and rules in error and exception handling to divert the process flow according to your needs. You can either define a general exception name or associated with EXCEPTION_INIT number. Please note, that if you use multiple-level blocks (block inside block) you have to stick to exceptions in each level. Whenever an exception is raised inside the sub-block you have to catch it there because it won’t be triggered in the “outer” block. See the examples below.
DECLARE v_salary NUMBER := 5000; v_emp_id NUMBER := 1001; e_salary_too_low EXCEPTION; BEGIN IF v_salary < 6000 THEN RAISE e_salary_too_low; ELSE EXECUTE IMMEDIATE 'UPDATE employee SET salary = :1 WHERE id_employee = :2' USING v_salary, v_emp_id; END IF; EXCEPTION WHEN e_salary_too_low THEN dbms_output.put_line('Poor employee, wouldn''t you consider a rise? :('); WHEN OTHERS THEN dbms_output.put_line('ErrMsg: '||SQLERRM); dbms_output.put_line('ErrCde: '||SQLCODE); END;
-- table must exist already (run twice in case you don't have it) DECLARE already_exists EXCEPTION; PRAGMA EXCEPTION_INIT(already_exists, -00955); BEGIN EXECUTE IMMEDIATE 'CREATE TABLE employee (id_emp NUMBER, name VARCHAR2(50))'; EXCEPTION WHEN already_exists THEN dbms_output.put_line('Oops, it is already there.'); WHEN OTHERS THEN dbms_output.put_line('ErrMsg: '||SQLERRM); dbms_output.put_line('ErrCde: '||SQLCODE); END;
BEGIN ... ---------- sub-block begins DECLARE cust_exception EXCEPTION; BEGIN ... IF ... THEN RAISE cust_exception; END IF; END; ------------- sub-block ends EXCEPTION WHEN OTHERS THEN ROLLBACK; END;
Multiblock exceptions – passing the exception to the outer block
DECLARE my_exception EXCEPTION; BEGIN ... ---------- sub-block begins BEGIN ... IF ... THEN RAISE my_exception; END IF; EXCEPTION WHEN my_exception THEN RAISE; -- pass the current exception into the outer block END; ------------ sub-block ends EXCEPTION WHEN my_exception THEN -- handle the exception here in the outer block ... END;
There are many ways how to use exceptions and you can get really creative with them. For all the details please visit Oracle Docs 🙂
I want to mention one more thing and it is GOTO directive. With this one, you can actually jump between the parts of your process from A to B to D back to C etc based on your exceptions or logic implemented. However, I strongly discourage you from using this. It is not a good programmer habit to use GOTO directives and you can end up in an infinite loop 🙂 Always try to rewrite your code to avoid using GOTO.
Lastly, you can do execute as many commands as you want or implement as robust logic into the EXCEPTION part as possible 🙂 Let me show you the same examples at the end of this article.
BEGIN ... EXCEPTION WHEN custom_no_input_param THEN CONTINUE; --usualy used in an inner block to avoid "escaping" due to an exception ... it will ignore it WHEN ZERO_DIVIDE THEN dbms_output.put_line('Zero division!'); ROLLBACK; WHEN OTHERS THEN ROLLBACK; -- rollback everything done in the block procedure_other_cases(); -- run procedure COMMIT; -- commit changes done in procedure END;