Skip to Main Content
  • Questions
  • FORALL with multi-table INSERT and specifying SAVE EXCEPTIONS

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Robert.

Asked: June 08, 2017 - 9:13 am UTC

Last updated: June 08, 2017 - 3:17 pm UTC

Version: 12.1.0.2

Viewed 1000+ times

You Asked

Hi Tom, you didn't cover this in your Dublin session that I attended a few years ago!

As part of an ETL process I am doing, I FORALL out an array, specifying SAVE EXCEPTIONS, and using a multi-table INSERT as my FORALL DML statement.

This all works fine, and I write my errors to a log table, gleaning information from SQL%BULK_EXCEPTIONS.

What I would like to record in my log table is the name of the table I am inserting into when an exception is raised.

Is this possible? I can work it out by analyzing the log (for example, only one of the tables in my insert has a unique constraint), but I would like to determine programmatically.

I don't see any element in SQL%BULK_EXCEPTIONS that would give me what I am looking for.

Many thanks,
Rob

and Chris said...

Not that I'm aware of, no. You can pass the sql%bulk_exceptions.error_code to sqlerrm. But unfortunately the message is stripped of useful info which would help you identify the table:

create table teddies (
  colour varchar2(10), 
  price  number
);
create table bricks (
  colour varchar2(5), 
  price  number
);

set serveroutput on
declare
  type tab_arr is table of teddies%rowtype index by pls_integer;
  rws  tab_arr;
  
  forall_err exception;
  pragma exception_init(forall_err, -24381);
begin
  rws(1).colour := 'blue';
  rws(1).price  := 10.99;
  rws(2).colour := 'yellow';
  rws(2).price  := 2.99;

  forall i in 1 .. rws.count save exceptions
    insert all
      into bricks values (rws(i).colour, rws(i).price)
      into teddies values (rws(i).colour, rws(i).price)
    select * from dual;

exception 
  when forall_err then 
    for errs in 1 .. sql%bulk_exceptions.count loop
      dbms_output.put_line(sqlerrm(-sql%bulk_exceptions(errs).error_code));
    end loop;
    raise;
end;
/

ORA-12899: value too large for column  (actual: , maximum: )

select * from bricks;

COLOUR  PRICE  
blue    10.99  

select * from teddies;

COLOUR  PRICE  
blue    10.99 


You may be better off sticking with single table inserts if this will be an issue.

Rating

  (1 rating)

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

Comments

Thanks

Robert Chalton, June 09, 2017 - 8:00 am UTC

Great, that's what I figured. In my case I did 2 FORALL's. One with a multi table insert for 4 of the tables (and I didnt mind which one was failing) and a single insert for the one I did. Works great, thanks.

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