Procedure or Function terminates with failure without being handled

Originally Posted by 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

Originally Posted by 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?

Originally Posted by 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.