How to use "continue" in a pl/sql for-loop

I use a for-loop in a pl/sql script like:

for rec in (select a,b,c from x) loop begin <script> end; end loop;

If have catched an exeption and want to do "continue" just to skip the current record, what is the appropriate way in pl/sql to do this? I can't find a "continue" statement...

Thank you.
MichaelHuthAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
for rec in (select a,b,c from x)
loop
  begin
    <script>
 
  exception
    when <exception> then
      -- handle the exception

  end;
end loop;
0
MichaelHuthAuthor Commented:
I know how to handle an exception, but not the pl/sql equivalent to a "continue" statement as known in other languages.
0
konektorCommented:
or u can use goto statement:

for ... loop
  --SQL command
  if <some condition> then
    GOTO continue; -- skip additional work inside a loop
  end if;
  -- additional work inside a loop
  <<continue>>
  null; -- label must be followed by executable code ("end loop" is not executable, "null" is)
end loop;
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

RCorfmanCommented:
If you can put the exception at the very end of the loop as angelIII is showing, it is essentially the same thing, you jump to the exception, handle it, then continue on.  If you need exceptions in the middle too, then you can always create a new exception type and raise that when you want to go to the end of the loop.

Believe it or not, you can also use labels and goto to do what you are requesting... May seem unconventional (like people say goto is bad), but if they are used in a controlled understood manner, then they are ok and do what you need.

this is a slightly modified exacmple from the Oracle Manual...

The label end_loop in the following example is not allowed because it does not precede an executable statement:
DECLARE
   done  BOOLEAN;
BEGIN
   ...
   FOR i IN 1..50 LOOP
      IF need_to_continue THEN
         GOTO end_loop;
      END IF;
      ...
   <<end_loop>>  -- not allowed
   END LOOP;  -- not an executable statement
END;

To debug the last example, just add the NULL statement, as follows:
FOR i IN 1..50 LOOP
   IF need_to_continue THEN
      GOTO end_loop;
   END IF;
   ...
<<end_loop>>
NULL;  -- an executable statement
END LOOP;

The above will jump to the end of the loop and check the condition to continue. I believe this is what you are requesting...


0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RCorfmanCommented:
I was writing and researching as konektor  was posting....
0
RCorfmanCommented:
If you want the alternative using a self-declared exception to do this without goto's, I can provide it, but it doesn't make sense to put that together unless you are ademant about avoiding goto in code (many people are).
0
riazpkCommented:
for rec in (select a,b,c from x)
loop
  begin
    <script>
  exception
    when others then null;
  end;
end loop;
0
Mark GeerlingsDatabase AdministratorCommented:
There is no "continue" statement in PL\SQL.  You just need a local exception handler inside the loop at the level where the exception is raised (like in the example from riazpk above, or in the link that Kollu posted).  You may also have an exception-handler outside of the loop if you want to handle errors that happen outside of the loop, if any.

I use that technique in many of our interface jobs so a record with a problem is not processed, but at least all others do get processed, and the procedures finish without raising an exception.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.