We help IT Professionals succeed at work.

Trapping foreign key exceptions in PL/SQL

smswart asked
I'm putting together a series of PL/SQL packages to allow client software to maintain tables without a lot of client-side SQL.  As part of this, in many cases I want to be able to trap foreign key violations and report them back with a table-specific error message instead of the generic Oracle message.  This becomes interesting with tables that contain multiple foreign keys.  I can embed the DML within its own BEGIN/END and trap error codes like ORA-02291 (no parent) and ORA-02292 (child exists) using PRAGMA EXCEPTION_INIT, but when those exceptions are caught, is there any way to determine exactly which foreign key is being violated?  

If it's not possible to do with exception handling, I can of course check for violations with separate queries prior to issuing the DML statement.  In case I have to do this, my question is "Which way is most efficient?"  I can think of the following ways to check for the existence of a key value.  These assume that I have a table "master" that has a primary key "key_col", and this table is the one I need to check to determine if an insert operation is valid.  I want to make sure the key_col value exists before the insert.  The methods I've come up with are:

1) select count(*) into v_count
     from master
    where key_col = <value>;

   if v_count = 0 then <error> end if;

2) select count(*) into v_count
     from master
    where key_col = <value>
      and rownum = 1;

   if v_count = 0 then <error> end if;

3) select count(*) into v_count
     from dual
    where exists (select key_col from master
                   where key_col = <value>);

   if v_count = 0 then <error> end if;

I don't have a ton of data in my tables right now, so explain plan doesn't help much.  I'm concerned about the performance of the queries when the volume grows to millions of rows.  I think method 2 should be better than 1, but I'm not certain about 3 relative to the others.  Perhaps there's no significant difference.

Any performance gurus out there that can provide input?
Watch Question

The best way is using error message (function SQLERRM) to identify the foreign key name. Something like

 ... /*your dml*/
exception when others then
 if sqlcode = -2291 then
   ... /*parse SQLERRM to identify the key name */
  raise_application_error(-20101,'Your own message');
 end if;

I don't know of any way to determine which Foreign Key was violated directly; however, you should be able to parse the SQLERRM to find out which key was violated.  It isn't the most pretty method, but I think it is workable.

If you just want to see if a row exists beforehand, the best performing method is normally to open a cursor, fetch a single row, then close the cursor.


  CURSOR c_1 (p_key_col  master.key_col%TYPE) IS
    SELECT *
    FROM master
    WHERE key_col = p_key_col;
  r_1  c_1%ROWTYPE;

  OPEN c_1 ('key1');
  FETCH c_1 INTO r_1;
  IF c_1%FOUND
    -- Handle Found Case
    -- Handle Not Found Case
  CLOSE c_1;
The SQLERRM has the name of the foreign key that was violated
It's a matter of stripping out the name and looking for the table and column
for foreign key  and the table and column it references; with the following query

select SUBSTR(A.table_name||'.'||LOWER(C.COLUMN_NAME) , 1,40) TAB_COL,
from dba_constraints A, DBA_IND_COLUMNS B, DBA_CONS_COLUMNS C
    and A.OWNER||'.'||a.constraint_name =
                          substr( SQLERRM,
                                        instr(SQLERRM ,'(',1 )+1 ,  ( instr(SQLERRM ,')',1) -1 ) - (instr(SQLERRM,'(',1 ) )   )

If you want the exact value causing the error you could use row level triggers to trap key violations


I had thought of parsing sqlerrm, but I did not think of going into the data dictionary to pull out the table and column name.  I ran your query against some sqlerrm values, and it worked perfectly.  I'm thinking of turning the query into a parsing function that would retrieve the parent and child of the offended relationship and use that to generate my message.  Thanks to everyone for their efforts on this issue!