Title: PL/SQl nested If loops inner if loop not exiting
Tags: oracle;plsql;nested-if
Question: Hi everyone I am a bit new to PL/SQL so am a bit confused. I have a Cursor called rec and while looping through it I have two nested IF statements.
CURSOR Cur IS
SELECT Mil.Id,
Mil.Record_Num,
Mil.Status,
Mil.file_processed,
Mil.Updated_By
FROM status_log mil
WHERE Mil.file_processed != 'Processed'
For Update of Mil.file_processed;
FOR Rec IN Cur LOOP
IF (Rec.status = 'Ready' OR Rec.status = 'Go') THEN
IF Length(Rec.Zip) = 5 AND
(Substr(Rec.Zip, 1, 3) = '303' OR
Substr(Rec.Zip, 1, 3) = '304' ) THEN
l_state:= 'ATL';
END IF;
UPDATE status_log mil
SET file_processed = 'Processed'
WHERE current of cur
END IF;
COMMIT;
END LOOP;
Now whenever the cursor has one record that satisfies the second IF(i.e Zip is 5 in length and the code starts with 303 or 304) it doesnt hit the update statement anymore for  that records and all record after that. I even tried using the EXIT statement after the logic in the IF loop but to no avail. What am I doing wrong?
Comment: Is there any chance that you are throwing an error and exiting silently? (i.e. your procedure has `exception when others then null;`)

Comment: Hi the code to do something i just a setting a variable to a particular value. That code is not responsible since I commented that out and tried running it with a blank block and yet the same problem. Edited the code a bit check update.

Comment: @Allan I am not throwing and exiting silently. The Only thing in the `exception when others` block is a notification that the block has been hit but the exception is not being hit.

Comment: please post the cursor, and "the code to do something" :)

Comment: @Eosphorus I've updated my answer.

Here is the accepted answer: IFs aren't loops.  This may actually be an important point since you say you've tried using an EXIT, and the purpose of that statement is to exit from the immediately enclosing loop.  In this case, that means the loop over the cursor (unless possibly your "code to do something" includes other loops).  So any EXIT you put in there would have caused the entire loop to terminate.
If it doesn't execute the update, then the "code to do something" is either (a) using an explicit command that affects the flow control, such as EXIT, CONTINUE, or GOTO, or (b) encountering an error that is causing control to switch to some exception handler somewhere.
If the execute is updating but failing, then you should be seeing an error -- unless, again, there is an exception handler somewhere that is hiding it from you.
Comment for this answer: The do something was just setting a value to a variable. I tried using a blank loop by commenting that part out but no luck :(

Here is another answer: it is possible that status_log rows to update are locked by another session.
You can query the blocking_session column of v$session view.
the "code to do something" is doing something.
You update the column file_processed but lock the row for the column status. I've not tested, but that could be an issue.
Comment for this answer: Actually sorry that was my typo I was locking file_processed not status. Edited the post. I think I am just hitting an exception somewhere and the package that we use to log errors isnt working. SO am writing some dbms output lines to see whats going on

Here is another answer: Check to see if your CURSOR definition includes or excludes the table status_log... That could be the problem.
Comment for this answer: Should not be the cause - Oracle guarantees that the cursor "sees" a consistent set of data once it's opened.

{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 3, 'CommentCount': 5, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2011:00:12/21/11', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<oracle><plsql><nested-if>', 'Title': 'PL/SQl nested If loops inner if loop not exiting', 'ViewCount': 3627, 'id': 8595531, 'user_AboutMe': '', 'user_AccountId': 1076396.0, 'user_DisplayName': 'Eosphorus', 'user_Reputation': 312.0, 'user_Views': 41.0, 'user_id': 1074440.0}}