'CONTINUE' keyword in Oracle 10g PL/SQL

Hi everyone,

I'm migrating a TSQL stored procedure to PL/SQL and have encountered a problem - the lack of a CONTINUE keyword in Oracle 10g.

I've read that Oracle 11g has this as a new feature, but upgrading is not an option unfortunately.

Is there any alternative to CONTINUE in 10g? I don't believe it's practical to restructure the logic of the SP as a work-around, because I have an outer loop, an IF, then a nested IF, then the CONTINUE at the end of a statement block within that IF.

Any help would be greatly appreciated, cheers.

From stackoverflow

• Can you refactor the IFs into a function, returning at the appropriate point (early if necessary). Then the control flow will pick up in the loop at the right place.

  Does that make sense?

• You can simulate a continue using goto and labels.

  DECLARE
     done  BOOLEAN;
  BEGIN
     FOR i IN 1..50 LOOP
        IF done THEN
           GOTO end_loop;
        END IF;
     <<end_loop>>  -- not allowed unless an executable statement follows
     NULL; -- add NULL statement to avoid error
     END LOOP;  -- raises an error without the previous NULL
  END;
  

  Nick Pierpoint : +1 for an ugly workaround to handle an ugly statement :)

  jop : Sometimes, we just have to type it in and cover our noses afterwards. :)

• Jop, thanks. That's more or less what I had done myself, but didn't know the trick with the NULL after the label to stop the compilation error - good one.

  Just as a point of interest, is there any way to do this that doesn't involve GOTO labels? I only ask because I'm not totally familiar with them and any potential side-effects they might have. I know they get a bad rep, but I think that they're probably appropriate in this situation, would that be correct?

  CagCowboy, that's a neat idea, but unfortunately the logic of this SP is pretty complex and it would be more of a pain than anything to extract the IFs out like that.

  jop : I think in your case, the use of GOTO is acceptable. GOTOs get bad rep when people abuse it - resulting in spaghetti code. It is always better to refactor so you don't need to use it.

• In Oracle there is a similar statement called EXIT that either exits a loop or a function/procedure (if there is no loop to exit from). You can add a WHEN to check for some condition.

  You could rewrite the above example as follows:

  DECLARE
     done  BOOLEAN;
  BEGIN
      FOR i IN 1..50 LOOP
       EXIT WHEN done;
     END LOOP;
  END;
  

  This may not be enough if you want to exit from deep down some nested loops and logic, but is a lot clearer than a couple of GOTOs and NULLs.

• The EXIT solution wouldn't really work in this case, because it would terminate the entire loop, as opposed to just start at the next iteration as CONTINUE would do.

  I think I'll have to go with the GOTO solution, cheers everyone for your suggestions!

• It's not available in 10g, however it's a new feature in 11G

• Thanks Jop...

  Yogesh N. Patil

• Hi Guys ! Though it's a bit complex and just a fake, you can use exception this way :

  DECLARE
    i NUMBER :=0;
    my_ex exception;
  BEGIN
    FOR i IN 1..10
    LOOP
        BEGIN
           IF i = 5 THEN
              raise my_ex;
           END IF;
           DBMS_OUTPUT.PUT_LINE (i);
        EXCEPTION WHEN my_ex THEN
           NULL;
        END;
    END LOOP;
  
  END;
  

• Not exactly elegant, but simple:

  DECLARE
     done  BOOLEAN;
  BEGIN
     FOR i IN 1..50 LOOP
        IF done THEN
           NULL;
        ELSE
           <do loop stuff>;
        END IF;
     END LOOP; 
  END;