Returning errors from PL/SQL

Quick and straightforward question:

I am writing a PL/SQL stored procedure. It contains some execute immediate calls that might fail. I don't want to raise an error. I would like the stored procedure to finish its execution cleanly and 开发者_Go百科return a list of errors. Something like:

for vRecord in vCursor
loop
    begin
        execute immediate 'insert into t(a) values (' || vRecord.val || ')';
    when others then
        -- store the error somewhere to return it!!
    end;
end loop;

So my question is: what's the recommended way of returning those errors? A Table? An out parameter?

Thanks a lot.

Wow, i just answered similar question in another thread, strange day so far. Use a LOG table and use autonomous transactions if you simply want to log errors (or messages):

See here

From version 10g Release 2 onwards, you can use DML Error Logging to store all errors in a special table. You can read about it here:

http://download.oracle.com/docs/cd/E11882_01/appdev.112/e16760/d_errlog.htm#ARPLS680

Some other advice based on the code you posted:

1) It's not necessary to use dynamic SQL here. Just use this instead:

insert into t(a) values (vRecord.val);

2) It's not necessary to use a loop here. Just use an INSERT/SELECT instead:

insert into t(a) select [the query from your vCursor here]

Regards,
Rob.