Home » SQL & PL/SQL » SQL & PL/SQL » Exception handling using update (oracle 9.2.0.3)
Exception handling using update [message #422359] Tue, 15 September 2009 16:15 Go to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Can we do something like this in exception handling?

when errors occurs then update the counter to -1 in error table.

CREATE OR REPLACE PROCEDURE T(in_emp_id number
)  IS 
o_return_cdenbr;
BEGIN
INS_EMP_DATA(in_emp_id,o_return_cde);
EXCEPTION
WHEN OTHERS THEN
UPDATE ERROR_T SET CNTR=-1 WHERE ID=IN_ID;
END;

Thanks
Re: Exception handling using update [message #422360 is a reply to message #422359] Tue, 15 September 2009 16:35 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Have you tried it?
Re: Exception handling using update [message #422361 is a reply to message #422360] Tue, 15 September 2009 16:42 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
yes..Just wanted to know whether its the right approach or not
Re: Exception handling using update [message #422365 is a reply to message #422359] Tue, 15 September 2009 18:25 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Just wanted to know whether its the right approach or not
Probably not right approach due to failure to compile.

>UPDATE ERROR_T SET CNTR=-1 WHERE ID=IN_ID;
From where does "IN_ID" originate?

What happens if/when no records satisfies WHERE ID=IN_ID condition?
How do you ensure that WHERE ID=IN_ID is always valid & true?
Can WHERE ID=IN_ID change more than 1 row?

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
Re: Exception handling using update [message #422371 is a reply to message #422361] Tue, 15 September 2009 22:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
prachij593 wrote on Tue, 15 September 2009 23:42
yes..Just wanted to know whether its the right approach or not

When you use WHEN OTHERS without RAISE you can be sure you are wrong in 99.999% of the cases.

Regards
Michel

Re: Exception handling using update [message #422376 is a reply to message #422359] Tue, 15 September 2009 23:50 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Quote:
o_return_cdenbr;


what is this??
SQL> 
SQL> create table test
  2  (
  3  ctr number
  4  );

Table created.
SQL> insert into test values (1);

1 row created.

SQL> commit;

Commit complete.
ed
Wrote file afiedt.buf

  1  CREATE OR REPLACE PROCEDURE test_p_del(in_emp_id number
  2  )  IS
  3  BEGIN
  4  RAISE NO_DATA_FOUND;
  5  EXCEPTION
  6  WHEN OTHERS THEN
  7  UPDATE TEST SET CTR=-1;
  8* END;
SQL> /

Procedure created.

SQL> begin
  2  test_p_del(1);
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select * from test;

       CTR
----------
        -1


This works Wink

But you must have some exception handling mechanism.Otherwise bad design can lead to costly workarounds in future.

[Updated on: Tue, 15 September 2009 23:52]

Report message to a moderator

Re: Exception handling using update [message #422377 is a reply to message #422359] Tue, 15 September 2009 23:54 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>This works
Only for very loose definition of "works"
>UPDATE TEST SET CTR=-1;
Above statement will set EVERY record in table TEST!
Re: Exception handling using update [message #422378 is a reply to message #422377] Tue, 15 September 2009 23:55 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Quote:
Above statement will set EVERY record in table TEST!

yes I know ..that is just a test
Re: Exception handling using update [message #422379 is a reply to message #422359] Wed, 16 September 2009 00:03 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>yes I know .
If you say so now.

>that is just a test
A test of what; flawed programming technique?

If your intention what to UPDATE every row; why did you require PL/SQL procedure to do so ( as a "test")?
Re: Exception handling using update [message #422381 is a reply to message #422379] Wed, 16 September 2009 00:05 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Quote:
A test of what; flawed programming technique?


No Part of my design Wink
I intended to keep only one row in test Smile

[Updated on: Wed, 16 September 2009 00:15]

Report message to a moderator

Re: Exception handling using update [message #422389 is a reply to message #422359] Wed, 16 September 2009 00:44 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>I intended to keep only one row in test
Please volunteer to be personal mentor for OP.
Re: Exception handling using update [message #422390 is a reply to message #422389] Wed, 16 September 2009 00:46 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Quote:
Please volunteer to be personal mentor for OP.

Laughing
Re: Exception handling using update [message #422393 is a reply to message #422359] Wed, 16 September 2009 01:05 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Not a joke; a serious suggestion.
Things would be better if you did.
Re: Exception handling using update [message #422403 is a reply to message #422359] Wed, 16 September 2009 02:34 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I think Michel's point needs to be re-emphasised.Quote:
When you use WHEN OTHERS without RAISE you can be sure you are wrong in 99.999% of the cases.


When Others catches any exception - including many for which you just want to let the exception pass back to the calling program.

You should put your update code in an exception handler that only catches the exceptions that you EXPECT to be raised by the procedure you call.

Re: Exception handling using update [message #422443 is a reply to message #422403] Wed, 16 September 2009 08:45 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Sir, requirement is when any error occurs update the table ERROR_T.
Thanks
Re: Exception handling using update [message #422445 is a reply to message #422443] Wed, 16 September 2009 08:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
prachij593 wrote on Wed, 16 September 2009 15:45
Sir, requirement is when any error occurs update the table ERROR_T.
Thanks

This requirement is stup... meaningless.
If the error is "database crashed" or "your session has been killed" or "unable to allocate memory", how could you update the table?

Regards
Michel

[Updated on: Wed, 16 September 2009 09:00]

Report message to a moderator

Re: Exception handling using update [message #422446 is a reply to message #422443] Wed, 16 September 2009 09:17 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Any error?

So if you've got an error caused by the tablespace being full, or an ORA-000257 Archiver error, then you think that setting this value to -1 and ignoring the error is a good idea?
Re: Exception handling using update [message #422459 is a reply to message #422446] Wed, 16 September 2009 11:57 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Sir,
Requirement is I am calling two procedure. Whichdoes some transaction.
If an error occur its being asked to update the column entry of a table by -1
Re: Exception handling using update [message #422460 is a reply to message #422446] Wed, 16 September 2009 12:01 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
'You should put your update code in an exception handler that only catches the exceptions that you EXPECT to be raised by the procedure you call.'

Requirement is call two procedure from the main say X and Y and if there is any error during the transaction then update the column by +1 for each record (say based on emp id)
Re: Exception handling using update [message #422461 is a reply to message #422359] Wed, 16 September 2009 12:02 Go to previous message
prachij593
Messages: 266
Registered: May 2009
Senior Member
"Any error?" a typo
Previous Topic: Getting BULK In-BIND error during bulk update.
Next Topic: performing transpose on table data (merged)
Goto Forum:
  


Current Time: Sun Dec 11 06:28:40 CST 2016

Total time taken to generate the page: 0.09349 seconds