Thanks for the question, Neeraj .

Asked: September 04, 2004 - 8:10 pm UTC

Last updated: November 23, 2004 - 9:57 am UTC

Version: 9.2.0.4

Viewed 1000+ times

You Asked

Is there a way to track at the database level, when a foreign key constraint is violated by any users in the database. In other words say for example I have a table A and table B, B is having a foreign key constraint which references parent A. There are hunderds of programs which touch table B, I want find out which program (and it's OSUSER) is getting the error "PARENT KEY NOT FOUND' ??

Thanks,
Neeraj

and Tom said...

ops$tkyte@ORA9IR2> create table p ( x int primary key );

Table created.

ops$tkyte@ORA9IR2> create table c ( x references p );

Table created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace trigger t_trigger
2 after servererror on database
3 declare
4 l_text ora_name_list_t;
5 l_n number;
6 begin
7 if ( is_servererror(2291) )
8 then
9 dbms_output.put_line( '--------------------' );
10 dbms_output.put_line( 'statment causing error: ' );
11 l_n := ora_sql_txt( l_text );
12 for i in 1 .. nvl(l_text.count,0)
13 loop
14 dbms_output.put_line( l_text(i) );
15 end loop;
16
17 dbms_output.put_line( 'error text: ' );
18 for i in 1 .. ora_server_error_depth
19 loop
20 dbms_output.put_line( ora_server_error_msg(i) );
21 end loop;
22 dbms_output.put_line( '--------------------' );
23 end if;
24 end;
25 /

Trigger created.

ops$tkyte@ORA9IR2> insert into c values ( 1 );
insert into c values ( 1 )
*
ERROR at line 1:
ORA-02291: integrity constraint (OPS$TKYTE.SYS_C006478) violated - parent key
not found

ops$tkyte@ORA9IR2> exec null;
--------------------
statment causing error:
insert into c values ( 1 )
error text:
ORA-02291: integrity constraint (OPS$TKYTE.SYS_C006478) violated - parent key
not found

--------------------

PL/SQL procedure successfully completed.

see:

</code> http://docs.oracle.com/cd/B10501_01/appdev.920/a96590/adg14evt.htm#998000 <code>

for a list of all functions you have available to you. And of course, you can get access to the v$ tables for more details about the session as well (the program name, etc...)

Rating

(6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

amol, September 06, 2004 - 12:43 am UTC

Great Tom..

Neeraj Nagpal, September 07, 2004 - 1:01 pm UTC

Thanks You So much TOM..I knew somtehing could be done with the database triggers but yes ...this will makes life much simpler ...Neeraj

Tracking Errors using Database Trigger

Magesh, November 19, 2004 - 2:36 pm UTC

Tom,
I am trying to track dml errors raised in the database using the database trigger with after servererror. If I want to check for multiple errors like ora-942, ora-925 etc, do I have to use multiple if statements like
if is_servererror(942) then
.....
elsif is_servererror(925) then
....
The problem with this is that the code size becomes too big and maintenance will be a issue. Is there a way that I can write something like
if is_servererror in (942,925) then
....
?

November 19, 2004 - 7:24 pm UTC

if is_servererror(942) or is_servererror(925) or .....

(it is a boolean function...)


or, build your own "is in"

ops$tkyte@ORA9IR2> create or replace package body util
  2  as
  3
  4  function is_in_servererror( l_codes in errorcodes ) return boolean
  5  is
  6  begin
  7          for i in 1 .. l_codes.count
  8          loop
  9                  if (is_servererror(l_codes(i)))
 10                  then
 11                          return TRUE;
 12                  end if;
 13          end loop;
 14          return FALSE;
 15  end;
 16
 17  end;
 18  /
 
Package body created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace trigger dml_error
  2  after servererror on database
  3  begin
  4      if ( util.is_in_servererror( util.errorcodes( 1, 60 ) ) )
  5      then
  6                  dbms_output.put_line( 'yup, it was in 1, 60' );
  7      end if;
  8  end;
  9  /
 
Trigger created.
 
ops$tkyte@ORA9IR2> drop table t;
 
Table dropped.
 
ops$tkyte@ORA9IR2> create table t ( x int primary key check (x > 0) );
 
Table created.
 
ops$tkyte@ORA9IR2> insert into t values ( 1);
 
1 row created.
 
ops$tkyte@ORA9IR2> insert into t values ( 1);
insert into t values ( 1)
*
ERROR at line 1:
ORA-00001: unique constraint (OPS$TKYTE.SYS_C007838) violated
 
 
ops$tkyte@ORA9IR2> exec null
yup, it was in 1, 60
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> declare
  2          pragma autonomous_transaction;
  3  begin
  4          insert into t values ( 1);
  5  end;
  6  /
yup, it was in 1, 60
declare
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 4
 
 
ops$tkyte@ORA9IR2> insert into t values ( 0);
insert into t values ( 0)
*
ERROR at line 1:
ORA-02290: check constraint (OPS$TKYTE.SYS_C007837) violated
 
 
ops$tkyte@ORA9IR2> exec null
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>

is_servererror

Magesh, November 22, 2004 - 11:55 am UTC

Wonderful! I tried your code, but the trigger is failing! I am not sure what I am doing wrong!! Please help!!

  1  create or replace package util as
     2  type  errorcodes is table of number index by binary_integer;
     3  function is_in_servererror(l_codes in errorcodes) return boolean;
     4  end;
     5  /
     6  sho err
     7
     8  create or replace package body util
     9    as
    10     function is_in_servererror( l_codes in errorcodes ) return boolean
    11     is
    12     begin
    13             for i in 1 .. l_codes.count
    14             loop
    15                     if (is_servererror(l_codes(i)))
    16                     then
    17                             return TRUE;
    18                     end if;
    19             end loop;
    20             return FALSE;
    21     end;
    22     end;
    23  /
    24  sho err
    25  create or replace trigger dml_error
    26    after servererror on database
    27    begin
    28        if ( util.is_in_servererror( util.errorcodes( 1, 60 ) ) )
    29        then
    30                    dbms_output.put_line( 'yup, it was in 1, 60' );
    31        --if is_servererror(942) then
    32       insert into system.log_errors_tab values (id, sysdate, ora_login_user,userenv('TERMINAL'),ora_client_ip_address
,
    33       ora_server_error(n), ora_server_error_msg(n));
    34       --End if;
    35       end if;
    36    end;
    37  /
    38  sho err
 
SQL> @dml_err_trig
 
Package created.
 
No errors.
 
Package body created.
 
No errors.
 
Warning: Trigger created with compilation errors.
 
Errors for TRIGGER DML_ERROR:
 
LINE/COL ERROR
-------- -----------------------------------------------------------------
2/7      PL/SQL: Statement ignored
2/36     PLS-00222: no function with name 'ERRORCODES' exists in this
         scope

November 22, 2004 - 3:30 pm UTC

doh, i see i lost the package spec there, didn't i....

I used a collection type, not an index by table.


ops$tkyte@ORA9IR2> create or replace package util
  2  as
  3     type errorcodes is table of number;
  4
  5     function is_in_servererror( l_codes in errorcodes ) return boolean;
  6  end;
  7  /
 
Package created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace package body util
  2  as
  3
  4  function is_in_servererror( l_codes in errorcodes ) return boolean
  5  is
  6  begin
  7          for i in 1 .. l_codes.count
  8          loop
  9                  if (is_servererror(l_codes(i)))
 10                  then
 11                          return TRUE;
 12                  end if;
 13          end loop;
 14          return FALSE;
 15  end;
 16
 17  end;
 18  /
 
Package body created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace trigger dml_error
  2  after servererror on database
  3  begin
  4      if ( util.is_in_servererror( util.errorcodes( 1, 60 ) ) )
  5      then
  6                  dbms_output.put_line( 'yup, it was in 1, 60' );
  7      end if;
  8  end;
  9  /
 
Trigger created.


<b>sorry about that -- i missed the spec on the cut</b>

Servererror Trigger

Magesh, November 22, 2004 - 4:12 pm UTC

Great! Now I have another question, when I track the error messages, is it also possible which statement caused the error? Like, a user tries to select from a table which does not exist or he has no privileges, I want to also know which table he tried to access or what ended in error. Also, can you tell me or give me a link where I can find what are the possible parameters used with USERENV or is there any USERENV('SQLTEXT') like USERENV('SESSIONID')?

Sorry for bugging you a lot on this, but as to many, you are like Sweet Uncle Tom who teaches everything to a curious kid!! :>:>

November 22, 2004 - 5:30 pm UTC

look up at the original answer for ora_sql_txt....

Works Great!!

Magesh, November 23, 2004 - 9:57 am UTC

Tom,
Thanks a ton for sharing, it works great!!

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Great Tom..

Tracking Errors using Database Trigger

is_servererror

Servererror Trigger

Works Great!!

More to Explore

PL/SQL demos

PL/SQL docs

Notify When a constraint is violated

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Great Tom..

Tracking Errors using Database Trigger

is_servererror

Servererror Trigger

Works Great!!

More to Explore

PL/SQL demos

PL/SQL docs