Home » SQL & PL/SQL » SQL & PL/SQL » Issueing COMMIT in Exception (merged 3)
Issueing COMMIT in Exception (merged 3) [message #385220] Sat, 07 February 2009 23:58 Go to next message
catchme_lenin
Messages: 35
Registered: January 2008
Location: Dubai
Member
Hi Guruji,
There is an application will be used by many users and many DML operation will be done. I put COMMIT in the end of Body.
I am capturing error logs(SQLERRM,CODE) and inserting into a table in exceptions. Can we put the COMMIT following the error log in exception? If don't put, will it not raise any lock (even dead lock)?

yours truly,
Lenin.
Re: Issueing COMMIT in Exception [message #385222 is a reply to message #385220] Sun, 08 February 2009 00:07 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

post the test case///
Re: Issueing COMMIT in Exception [message #385224 is a reply to message #385222] Sun, 08 February 2009 00:14 Go to previous messageGo to next message
catchme_lenin
Messages: 35
Registered: January 2008
Location: Dubai
Member
Begin
------
-----

EXCEPTION
WHEN OTHERS THEN
DPROC_DMIS_EXCEPTIONS ('SBS', 'SBS', SYSDATE, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'Last Update '||SQLERRM);

COMMIT;
END;
Re: Issueing COMMIT in Exception (merged 3) [message #385225 is a reply to message #385220] Sun, 08 February 2009 00:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do not commit (or rollback) inside the procedure.
This is the caller that knows if it wants to commit or roll back.

And do not multipost your question.

Regards
Michel

[Updated on: Sun, 08 February 2009 00:21]

Report message to a moderator

Re: Issueing COMMIT in Exception (merged 3) [message #385226 is a reply to message #385225] Sun, 08 February 2009 00:32 Go to previous messageGo to next message
catchme_lenin
Messages: 35
Registered: January 2008
Location: Dubai
Member
Dear Michel,
Do you mean that should not commit inside the 'DPROC_DMIS_EXCEPTIONS' procedure, which is called in exception? Yes. I did not call commit inside that. But, can I call the COMMIT after the procedure like,
...
WHEN OTHERS THEN
DPROC_DMIS_EXCEPTIONS ('SBS', 'SBS', SYSDATE, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'Last Update '||SQLERRM);
COMMIT;END;

Thanx,
Lenin.
Re: Issueing COMMIT in Exception (merged 3) [message #385229 is a reply to message #385226] Sun, 08 February 2009 01:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, I mean you don't commit in any procedure (but autonomous log one).
Only the application knows when to commit or roll back not one part (procedure).
The transaction is defined at application level, it is a functional need not a technical one, so ending a transaction must be done by the one that knows the high level feature not by one (the procedure) that knows only a small part of it.

Regards
Michel
Re: Issueing COMMIT in Exception (merged 3) [message #385232 is a reply to message #385229] Sun, 08 February 2009 02:33 Go to previous messageGo to next message
catchme_lenin
Messages: 35
Registered: January 2008
Location: Dubai
Member
thank you Micel.
One more clarification required.
If any record is locked by 'FOR UPDATE OF' statement in the procedure and if the updation fails at 270th record, the oracle will rollback all it's previous updation (all 270 records). Meantime, if any other user issues updation on 40th record, he has to wait till all the records are rolledback. It's taking huge time. (Sometime, oracle get hangs!!! so we have killed the session!!! That is the reason I opted for calling COMMIT at exception. It will rollback only the failed record, instead of rollbacking all 270 records.). How to handle the situation?
Once again thank you for you advice.
Regds,
Lenin.

Re: Issueing COMMIT in Exception (merged 3) [message #385235 is a reply to message #385232] Sun, 08 February 2009 02:54 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
If you make your looging procedure an AUTONOMOUS TRANSACTION, then it can commit without interfering with the main transaction. i.e. A commit in an AUTONOMOUS TRANSACTION does not commit the the transaction in procedure from which it was called.

Another thing to consider is that when you use WHEN OTHERS, you are masking errors - it is very difficult to tell exactly which statement errored.

If it is a batch process and you want it to ignore the error and move on to another row, fair enough, but if you are going to exit the procedure, it can be better to let the error pass unhandled back to whatever called the P/SQL in the first place. That way, the calling procedure knows the precise line number on which the error occurred.

Ross Leishman
Re: Issueing COMMIT in Exception (merged 3) [message #385245 is a reply to message #385232] Sun, 08 February 2009 04:20 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Lenin, I agree with Ross.

Regards
Michel
Previous Topic: Dynaic Procedure
Next Topic: Need a simple(?) logic
Goto Forum:
  


Current Time: Sat Dec 03 10:15:23 CST 2016

Total time taken to generate the page: 0.09631 seconds