Procedure or Function terminates with failure without being handled

[eeriehunk]

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

[amitpatel66]

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?

[madankarmukta]

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

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.

[eeriehunk]

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?

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

[amitpatel66]

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.

[eeriehunk]

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.

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

[amitpatel66]

Good to hear you got a good undersatnding of your doubt. Do post back for further queries and we are happy to help!!

Moderator