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.

Community · Accepted Answer · 2017-05-23 11:55:43Z

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, 2017 at 11:55

CommunityBot

11 silver badge

answered Feb 7, 2011 at 13:55

tbone

15.5k3 gold badges37 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Pablo Santa Cruz Over a year ago

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!

tbone Over a year ago

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.

Jon Heller Over a year ago

You really should use something like dbms_utility.format_error_stack||dbms_utility.format_error_backtrace instead of just SQLERRM.

Rob van Wijk · Accepted Answer · 2011-02-07 14:04:01Z

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 7, 2011 at 14:04

Rob van Wijk

17.7k5 gold badges41 silver badges56 bronze badges

3 Comments

Pablo Santa Cruz Over a year ago

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.

APC Over a year ago

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

Rob van Wijk Over a year ago

True. The table - t in this case - should not be dynamic :-)

Collectives™ on Stack Overflow

Returning errors from PL/SQL

2 Answers 2

3 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related