Returning errors from PL/SQL

Question

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 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.

score 3 · Accepted Answer · edited May 23 '17 at 11:55

3

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

edited May 23 '17 at 11:55

Community

1
1

answered Feb 07 '11 at 13:55

tbone

15,107
3
33
40

Thanks! I thought of that. Don't know if it's the best option though. It seems like an OVERKILL to use a TABLE just to return errors... Thanks for the suggestion anyway! – Pablo Santa Cruz Feb 07 '11 at 13:59
Never overkill to log errors, good practice ;) And you have an audit trail of what occurs over time, assuming this is something you'll run on some frequency. – tbone Feb 07 '11 at 14:25
You really should use something like dbms_utility.format_error_stack||dbms_utility.format_error_backtrace instead of just SQLERRM. – Jon Heller Feb 08 '11 at 04:10

score 3 · Answer 2 · answered Feb 07 '11 at 14:04

3

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.

answered Feb 07 '11 at 14:04

Rob van Wijk

17,555
5
39
55

Thanks a lot for the answer Rob. I will take a look at DML error logging. Thanks for you suggestions as well. But, what I posted is just a sample code. My actual code **does** need dynamic SQL. – Pablo Santa Cruz Feb 07 '11 at 14:21
It is always the best idea to use Oracle's built-in functionality rather than rolling our own. However, I'm not sure how easy it will be to use DML error logging with dynamic SQL; it depends in what way the SQL is dynamic... – APC Feb 07 '11 at 15:49
True. The table - t in this case - should not be dynamic :-) – Rob van Wijk Feb 07 '11 at 21:35

Returning errors from PL/SQL

2 Answers2