PLSQL: Exception Types

Knowing how and when to handle exceptions is crutial when writing PLSQL (as in most other programming languges). This does not mean that you always should handle your exceptions in your PLSQL code. Most probably you shouldn’t, or at least not all of it. Handling exceptions in PLSQL is more about “securing a consistent state of the data and database” and/or logging the error that just happend, before propagating (re-RAISE the same exception or RAISE another exception) the exception to the calling client.

In this post I’ll show the use of the three different types of exceptions:

  • Predefined Oracle server exceptions
  • User-defined exceptions
  • Non-predefined Oracle server exceptions

Predefined Oracle server exceptions:

PL/SQL predefines some common Oracle errors as exceptions. For example – some of the most common predifined exceptions are the NO_DATA_FOUND, INVALID_NUMBER, ZERO_DIVIDE and DUP_VAL_ON_INDEX (You find the full list in the Oracle documentation). Every Oracle error has an unike error number, the equivalent error code in PLSQL (called SQLCODE) is usually the negative value of the error code. The error code for duplicate value on unique index is 1, the equivalent SQLCODE is -1. Only a limited number of the Oracle errors has a predefined Oracle server exception. But by using a user-defined excepton and tying this to an Oracle error (or actually an error number), you can write exception handles for about every Oracle error.

The following code snippet shows the use of some pre-defined oracle server exceptons:

declare
    ...
begin
    ... code ...
exception
    when no_data_found then
        dbms_output.put_line('No data was found!');

    when dup_val_on_index then
        dbms_output.put_line('Can''t have duplicate value on index column!');

    when others then
        dbms_output.put_line(SQLCODE || ', ' || SQLERRM)
end;
/

The main thing to notice in this example is that there is no code conserning error handling in the main code. Everything is handled below in the exception section. Following is a short example of some error code running:

SQL> create table t (id number primary key, data varchar2(10));

Table created.

SQL>
SQL> insert into t values (1,'X');

1 row created.

SQL> insert into t values (2,'Y');

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> set serveroutput on
SQL> declare
  2      l_data t.data%type;
  3  begin
  4      begin
  5          select data into l_data from t where id=100;
  6      exception
  7          when no_data_found then
  8              dbms_output.put_line('No data was found!');
  9          when others then
 10              dbms_output.put_line('Unknown error in SELECT from table T!');
 11              raise;
 12      end;
 13
 14      begin
 15          insert into t values (1,'Z');
 16
 17      exception
 18          when dup_val_on_index then
 19              dbms_output.put_line('Can''t have duplicate value on index column!');
 20          when others then
 21              dbms_output.put_line('Unknown error in INSERT into table T!');
 22              raise;
 23      end;
 24
 25      begin
 26          l_data := '12345678901';
 27
 28      exception
 29          when dup_val_on_index then
 30              dbms_output.put_line('Can''t have duplicate value on index column!');
 31          when others then
 32              dbms_output.put_line('Unknown error in setting value for L_DATA!');
 33              raise;
 34      end;
 35  exception
 36      when others then
 37          dbms_output.put_line('Exception caught in the OUTER exception handling section ');
 38          dbms_output.put_line(SQLCODE || ', ' || SQLERRM);
 39  end;
 40  /
No data was found!
Can't have duplicate value on index column!
Unknown error in setting value for L_DATA!
Exception caught in the OUTER exception handling section
-6502, ORA-06502: PL/SQL: numeric or value error: character string buffer too
small

PL/SQL procedure successfully completed.

SQL>

User defined exceptions

The programmer can also define his own exceptions. In it’s basic form these are not connected to Oracle errors, but are mostly used for handling errors or situations connected to the business logic.

The following code snippet shows the use of a user-defined exception:

SQL> declare
  2      l_data t.data%type;
  3      invalid_data_exception EXCEPTION;
  4  begin
  5      select data into l_data from t where rownum=1;
  6
  7      if l_data<>'A' then
  8          raise invalid_data_exception;
  9      end if;
 10      
 11      dbms_output.put_line('Some code that never gets to run.');
 12
 13  exception
 14      when invalid_data_exception then
 15          dbms_output.put_line('Registered data is INAPPLICABLE for further processing!');
 16  end;
 17  /
Registered data is INAPPLICABLE for further processing!

PL/SQL procedure successfully completed.

As explained above we see that the user-defined exception has nothing to do with an Oracle error or pre-defined oracle server exception. This brings us to the next type where we do connect an user-defined exception to an Oracle error number (and therefore also to an pre-defined Oracle server error).

Non-predefined Oracle server exceptions

As we saw earlier there is not a predefined Oracle server exception for every Oracle error (or error number). To handle these other Oracle errors in our PLSQL code we can use a user-defined exception and tie it to an Oracle error number (and therefore to an predefined Oracle error) – or actually we tie it to the SQLCODE for the Oracle error. Again you’ll see we’re back to where we don’t need any error handling code in the main body of our PL/SQL code:

SQL> declare
  2      l_table_name user_tables.table_name%type:='T10';
  3      name_in_use_exception EXCEPTION;
  4      PRAGMA EXCEPTION_INIT(name_in_use_exception, -955);
  5  begin
  6
  7      execute immediate 'create table ' || l_table_name ||' (id number primary key, data varchar2(10))';
  8
  9      dbms_output.put_line('Table '|| l_table_name || ' created successfully!');
 10
 11  exception
 12      when name_in_use_exception then
 13          dbms_output.put_line('Creating table ' || l_table_name || ' failed!');
 14      when others then
 15          dbms_output.put_line(SQLCODE || ', ' || SQLERRM);
 16  end;
 17  /
Table T10 created successfully!

PL/SQL procedure successfully completed.

SQL> declare
  2      l_table_name user_tables.table_name%type:='T10';
  3      name_in_use_exception EXCEPTION;
  4      PRAGMA EXCEPTION_INIT(name_in_use_exception, -955);
  5  begin
  6
  7      execute immediate 'create table ' || l_table_name ||' (id number primary key, data varchar2(10))';
  8
  9      dbms_output.put_line('Table '|| l_table_name || ' created successfully!');
 10
 11  exception
 12      when name_in_use_exception then
 13          dbms_output.put_line('Creating table ' || l_table_name || ' failed!');
 14      when others then
 15          dbms_output.put_line(SQLCODE || ', ' || SQLERRM);
 16  end;
 17  /
Creating table T10 failed!

PL/SQL procedure successfully completed.

SQL>

Post a Comment

Your email is never published nor shared. Required fields are marked *