We help IT Professionals succeed at work.

Trapping foreign key exceptions in PL/SQL

smswart
smswart asked
on
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?
Comment
Watch Question

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

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

Commented:
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.

Example:

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

BEGIN
  OPEN c_1 ('key1');
  FETCH c_1 INTO r_1;
  IF c_1%FOUND
  THEN
    -- Handle Found Case
  ELSE
    -- Handle Not Found Case
  END IF;
  CLOSE c_1;
END;
Commented:
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,
            SUBSTR(B.TABLE_NAME ||'.'|| LOWER(B.COLUMN_NAME), 1,40) REF_TAB_COL_OR_CONDITION
from dba_constraints A, DBA_IND_COLUMNS B, DBA_CONS_COLUMNS C
where a.CONSTRAINT_TYPE = 'R'
    and A.OWNER||'.'||a.constraint_name =
                          substr( SQLERRM,
                                        instr(SQLERRM ,'(',1 )+1 ,  ( instr(SQLERRM ,')',1) -1 ) - (instr(SQLERRM,'(',1 ) )   )
    and A.R_CONSTRAINT_NAME = B.INDEX_NAME
    and A.CONSTRAINT_NAME = C.CONSTRAINT_NAME(+)

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

Author

Commented:
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!