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

toookiAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
What do you want this exception to do?

Would a select distinct keep the error form happening?
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);)

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;
....
SolarWinds® IP Control Bundle (IPCB)

Combines SolarWinds IP Address Manager and User Device Tracker to help detect IP conflicts, quickly identify affected systems, and help your team take near instantaneous action. Help improve visibility and enhance reliability with SolarWinds IP Control Bundle.

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?
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;
/

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
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)...
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.
toookiAuthor Commented:
Thanks for explaining...
Many thanks to you all.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.