2 Replies Latest reply: Mar 8, 2013 3:20 AM by girishkale RSS

    Getting the constraint name with sql%bulk_exceptions

    girishkale
      Hello folks,

      Is there any way to get the constraint name that was violated using sql%bulk_exceptions.
      I've a parent table and it's two children tables. When I try to delete from parent using forall, save exceptions, I want to know what constraint violated at what iteration of a bulk operation.
      I've following script.
      set serveroutput on
      drop table chld;
      drop table chld2;
      drop table prnt;
      create table prnt(n number primary key);
      insert into prnt select level from dual connect by level<11;
      
      create table chld(cn number references prnt(n));
      insert into chld select level from dual connect by level<3;
      
      create table chld2(cn number references prnt(n));
      insert into chld2 select level*3 from dual connect by level<3;
      select * from prnt;
      select * from chld;
      select * from chld2;
      commit;
      declare
        type t_prnt_obj is table of prnt.n%type index by pls_integer;
        t_prnt_tab t_prnt_obj;
        l_exp_cnt integer;
      begin
        select n bulk collect into t_prnt_tab from prnt order by n;
        dbms_output.put_line('t_prnt_tab.count -- '||t_prnt_tab.count);
        forall i in t_prnt_tab.first .. t_prnt_tab.last save exceptions
          delete prnt where n = t_prnt_tab(i);
      exception
        when others
        then
          l_exp_cnt := sql%bulk_exceptions.count;
          dbms_output.put_line('Total exceptions:'|| l_exp_cnt);
          for k in 1 .. l_exp_cnt
          loop
            dbms_output.put_line('Error '||k|| ' - Collection Index: '||sql%bulk_exceptions(k).error_index||' - Error Code: '|| sql%bulk_exceptions(k).error_code||' - Message: '||SQLERRM(- sql%bulk_exceptions(k).error_code) );
          end loop;
      end;
      I get the output as below:
      t_prnt_tab.count -- 10
      Total exceptions:4
      Error 1 - Collection Index: 1 - Error Code: 2292 - Message: ORA-02292: integrity constraint (.) violated - child record found
      Error 2 - Collection Index: 2 - Error Code: 2292 - Message: ORA-02292: integrity constraint (.) violated - child record found
      Error 3 - Collection Index: 3 - Error Code: 2292 - Message: ORA-02292: integrity constraint (.) violated - child record found
      Error 4 - Collection Index: 6 - Error Code: 2292 - Message: ORA-02292: integrity constraint (.) violated - child record found
      Instead, I want to get in output with the constraint_name added. Like below:
      t_prnt_tab.count -- 10
      Total exceptions:4
      Error 1 - Collection Index: 1 - Error Code: 2292 - Message: ORA-02292: integrity constraint (GK.CP_FK) violated - child record found
      Error 1 - Collection Index: 1 - Error Code: 2292 - Message: ORA-02292: integrity constraint (GK.CP_FK) violated - child record found
      Error 1 - Collection Index: 1 - Error Code: 2292 - Message: ORA-02292: integrity constraint (GK.C2P_FK) violated - child record found
      Error 1 - Collection Index: 1 - Error Code: 2292 - Message: ORA-02292: integrity constraint (GK.C2P_FK) violated - child record found
      Is it possible, if so how do I get it?

      Thanks in advance for you help.

      Girish