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