473,320 Members | 1,914 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Procedure or Function terminates with failure without being handled

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
6 8047
amitpatel66
2,367 Expert 2GB
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
madankarmukta
308 256MB
@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
@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
2,367 Expert 2GB
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
@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
2,367 Expert 2GB
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

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

Similar topics

4
by: beliavsky | last post by:
If a function that normally returns N values raises an exception, what should it return? N values of None seems reasonable to me, so I would write code such as def foo(x): try: # code setting...
4
by: marc | last post by:
I've been developing a stored procedure that uses a user defined function in the query portion of the procedure. However, since the end product needs to allow for dynamic table names, the UDF will...
8
by: N.V.Dev | last post by:
Hi, I am trying to use the db2load API using C. During run-time below is the error message thrown call utils.load_table('util.temp') SQL1131N DARI (Stored Procedure) process has been...
3
by: David Carver | last post by:
We are running into a problem with a Communication Link failure when calling an External Stored procedure written in ILE Cobol from an SQL UDF. When calling the stored procedure by itself and not...
8
by: Thomasb | last post by:
With a background in MS SQL Server programming I'm used to temporary tables. Have just started to work with DB2 ver 7 on z/OS and stumbled into the concept of GLOBAL TEMPORARY TABLE. I have...
9
by: joun | last post by:
Hi all, i'm using this code to insert records into an Access table from asp.net, using a stored procedure, called qry_InsertData: PARAMETERS Long, Long, Text(20), Long, DateTime; INSERT...
7
by: Siv | last post by:
Hi, I have a stored procedure that I want to execute and then wait in a loop showing a timer whilst it completes and then carry on once I get notification that it has completed. The main reason...
7
by: Schmidty | last post by:
Okay...I have another 'newbie' question; I have a function that loads a page and the action is $_SERVER; In the form that is in a function(method?) within a class a variable is passed back to...
28
by: Bill | last post by:
Hello All, I am trying to pass a struct to a function. How would that best be accomplished? Thanks, Bill
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.