Exceptions :: Oracle World

Post Views: 579

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.

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.

Examples:

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).

User-defined Exceptions

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.

General exception

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;

EXCEPTION_INIT

-- 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;

Multiblock exceptions

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;