PL/SQl nested If loops inner if loop not exiting

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?

Is there any chance that you are throwing an error and exiting silently? (i.e. your procedure has exception when others then null;) — Allan
– Allan, Commented Dec 21, 2011 at 20:27
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. — Eosphorus
– Eosphorus, Commented Dec 21, 2011 at 21:58
@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. — Eosphorus
– Eosphorus, Commented Dec 21, 2011 at 22:07

Dave Costa · Accepted Answer · 2011-12-21 21:44:43Z

3

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.

answered Dec 21, 2011 at 21:44

Dave Costa

48.3k8 gold badges61 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Eosphorus Over a year ago

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 :(

Florin Ghita · Accepted Answer · 2011-12-21 22:16:38Z

1

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.

edited Dec 21, 2011 at 22:16

answered Dec 21, 2011 at 20:25

Florin Ghita

17.7k6 gold badges60 silver badges78 bronze badges

1 Comment

Eosphorus Over a year ago

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

Yuck · Accepted Answer · 2011-12-21 20:19:53Z

0

Check to see if your CURSOR definition includes or excludes the table status_log... That could be the problem.

edited Dec 21, 2011 at 20:19

Yuck

51.1k13 gold badges108 silver badges136 bronze badges

answered Dec 21, 2011 at 20:01

user1107052

911 silver badge3 bronze badges

1 Comment

Frank Schmitt Over a year ago

Should not be the cause - Oracle guarantees that the cursor "sees" a consistent set of data once it's opened.

Collectives™ on Stack Overflow

PL/SQl nested If loops inner if loop not exiting

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related