?
Solved

How to write an Oracle exception for Update query

Posted on 2011-10-03
9
Medium Priority
?
354 Views
Last Modified: 2012-06-21
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
Comment
Question by:toooki
  • 4
  • 4
9 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36905877
What do you want this exception to do?

Would a select distinct keep the error form happening?
0
 

Author Comment

by:toooki
ID: 36905908
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36905956
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:toooki
ID: 36905970
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
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 952 total points
ID: 36905991
>> 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
 
LVL 35

Assisted Solution

by:johnsone
johnsone earned 948 total points
ID: 36906017
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
 

Author Comment

by:toooki
ID: 36906092
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36906555
>>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
 

Author Comment

by:toooki
ID: 36906912
Thanks for explaining...
Many thanks to you all.
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question