• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 370
  • Last Modified:

How to write an Oracle exception for Update query

How can I write an Oracle (11gR2) EXCEPTION block in my procedure
(into my log table myLog) when the below query fails in the procedure?
(mainly when the select command returns more than one value).    
UPDATE myTab1 t1
         SET t1.f1 = (SELECT t2.f1
                                   FROM myTab2 t2
                                  WHERE t1.f2=t2.f2);

Open in new window

0
toooki
Asked:
toooki
  • 4
  • 4
2 Solutions
 
slightwv (䄆 Netminder) Commented:
What do you want this exception to do?

Would a select distinct keep the error form happening?
0
 
toookiAuthor Commented:
Thank you.
Ok almost never the exception occurs. I am writing for the sake of loggin if the error happens.

Just if that happens I want the procedure to exit and within exception block rollback and write an insert into mylog table (insert into mylog (SQLCODE, SQLERRM);)

0
 
slightwv (䄆 Netminder) Commented:
I forget the exact error that is returned but if it is one of the pre-defined you can write a specific handler for that.  Of course you would need to add additional values to your table.

I'm going from memory on the 'exit' to stop processing.  It might be return.

The 'catch all' would go something like:

...
exception when others then
   insert into error_table(SQLCODE, substr(SQLERRM,1,255);
   exit;
....
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
toookiAuthor Commented:
Thank you.

If I have multiple Update (unrelated) statements in the same procedure I tried to write the Excception for each of the update.
Not sure if Oracle does let me write only one Exception block.  Can I rite multiple exceptions after each update statements, so that the in the insert query I could pass the table name associated with that specific update?
0
 
slightwv (䄆 Netminder) Commented:
>> let me write only one Exception block.

You get one exception per 'block'.

You can nest Pl/SQL blocks and have as many as you want.

create or replace procedure myproc
is
begin
... do some stuff

    begin
       -- your update
       exception when others then
          ...
    end;


 ... do more stuff

    begin
        ...
    end;


end;
/
0
 
johnsoneSenior Oracle DBACommented:
The pre-defined exception for what you are looking for is TOO_MANY_ROWS.  However, since you probably want to log all the errors, OTHERS is good.  I would use TOO_MANY_ROWS if you wanted to have different processing for that.
0
 
toookiAuthor Commented:
Thank you all.

Have the question then --

I can write multiple begin end and put individual exception block there.

If I write
  EXCEPTION
WHEN TOO_MANY_ROWS THEN
---log to table
WHEN OTHERS THEN
---log to table

What is the use of writing pre-defined exception (like the above)...
0
 
slightwv (䄆 Netminder) Commented:
>>What is the use of writing pre-defined exception

If you want to take specific actions for specific errors.  If you know what to do for a TOO_MANY_ROWS you can write code to handle it and not error out but for any other exception, log and exit.
0
 
toookiAuthor Commented:
Thanks for explaining...
Many thanks to you all.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now