CODEWITHSUNDEEP
oracle-databaseplsql
jane Doe
jane Doe

Reputation: 157

handle exception pl/sql : get value where exception occured

I'm using this code 

BEGIN
EXECUTE IMMEDIATE 'INSERT INTO MY_TABLE (A, B , C)
                  SELECT TABLE_2.A, TABLE_2.B, TABLE_2.C
                  FROM TABLE_2
                  WHERE TABLE_2.A in (val1,val2,val3) ';

EXCEPTION WHEN OTHERS then 
                PROC_EXCEPTION('ERROR',TABLE_2.B,SQLCODE,SQLERRM);
END;

And I'm getting the following compilation error:

TABLE_2.B must be declared

how can I get the value of TABLE_2.B where the exception occurred and pass it to the procedure handling the exception?

Upvotes: 1

Views: 280

Answers (2)

J. Chomel
J. Chomel

Reputation: 8395

You cannot reference TABLE.A outside the EXECUTE IMMEDIATE block...

Try:

BEGIN
EXECUTE IMMEDIATE 'INSERT INTO MY_TABLE (A, B , C)
              SELECT TABLE_2.A, TABLE_2.B, TABLE_2.C
              FROM TABLE_2
              WHERE TABLE_2.A in (val1,val2,val3) ';

EXCEPTION WHEN OTHERS then 
            PROC_EXCEPTION('ERROR with TABLE_2.B',SQLCODE,SQLERRM);
END;

To have value for TABLE_2.B do it in a loop:

BEGIN
  for x in (SELECT TABLE_2.A, TABLE_2.B, TABLE_2.C
              FROM TABLE_2
              WHERE TABLE_2.A in (val1,val2,val3))
  loop
    begin
      execute immediate 'INSERT INTO MY_TABLE (A, B , C) values ('||x.A||','||x.B||','||x.C||')';
        EXCEPTION WHEN OTHERS then 
           PROC_EXCEPTION('ERROR with'|| x.B,SQLCODE,SQLERRM);
    end;
  end loop;

END;

Upvotes: 2

Kacper
Kacper

Reputation: 4818

You need to use clause LOG ERRORS INTO https://oracle-base.com/articles/10g/dml-error-logging-10gr2

To create error log table please use https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_errlog.htm#CEGBBABI

Then after insert finishes you can process records from error table.

Upvotes: 2

Related Questions