By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,857 Members | 1,793 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,857 IT Pros & Developers. It's quick & easy.

Procedure or Function terminates with failure without being handled

P: 55
Hi Guys, This is a very common interview question but I am unable to find the right answer for this. Kindly let me know.
What occurs if a procedure or function terminates with failure without being handled?
1. Any DML statements issued by the construct are still pending and can be committed or rolled back.
2. Any DML statements issued by the construct are committed
3. Unless a GOTO statement is used to continue processing within the BEGIN section, the construct terminates.
4. The construct rolls back any DML statements issued and returns the unhandled exception to the calling environment.

Regards,
Aj
Mar 10 '09 #1
Share this Question
Share on Google+
6 Replies


amitpatel66
Expert 100+
P: 2,367
What is your understanding and guess?? What do you think should happen in general for any transaction that has failed, not only related to oracle?
Mar 10 '09 #2

100+
P: 293
@eeriehunk
I think "The construct rolls back any DML statements issued and returns the unhandled exception to the calling environment." is the suitable answer to your question.
Mar 10 '09 #3

P: 55
@amitpatel66
Hi Amit,
I think if a transaction fails,the updates to the DB should not happen. It should either fail completely or pass completely. So 1 and 2 can be eliminated. But what if there is a commit before it fails or what about this GOTO statement ? How does that work.
Regards,
Aj
Mar 10 '09 #4

amitpatel66
Expert 100+
P: 2,367
Basic understanding is that a particular DML Transaction completes once you either COMMIT or ROLLBACK, so any COMMIT or ROLLBACK issued before the procedure fails, then those transactions will PERSIST depending on either you COMMITED or ROLLBACKED.

In case if you DONT use and Transactional Control Statements like COMMIT or ROLLBACK, then if the procedure FAILS, then the Changes made by any DML OPERATIONS WILL BE ROLL BACKED and NOT COMMIT.

GOTO is an UNCONDITIONAL jump that you can use in your code to tranfer control of your code to some other location from where it starts executing. It is said that its bad practice using GOTO statements becuase its UNCONDITIONAL and secondly the Sequence of your code execution is LOST.
Mar 11 '09 #5

P: 55
@amitpatel66
Dear Amit and Madankarmukta,
Thank you for your reply and I understand it well. I will be back with more topics for discussion topics.
Regards AJ
Mar 12 '09 #6

amitpatel66
Expert 100+
P: 2,367
Good to hear you got a good undersatnding of your doubt. Do post back for further queries and we are happy to help!!

Moderator
Mar 13 '09 #7

Post your reply

Sign in to post your reply or Sign up for a free account.