Is it possible to CONTINUE a loop from an exception

Question

I have a fetch being executed inside of a loop   If this fetch fails  no data  I would like to CONTINUE the loop to the next record from within the EXCEPTION   Is this possible   I m getting a ORA-06550  amp  PLS-00201 identifer CONTINUE must be declared  DECLARE    v attr char 88   CURSOR  SELECT USERS IS SELECT id FROM USER TABLE WHERE USERTYPE    X   BEGIN     FOR user rec IN SELECT USERS LOOP             BEGIN             SELECT attr INTO v attr              FROM ATTRIBUTE TABLE             WHERE user id   user rec id                       EXCEPTION             WHEN NO DATA FOUND THEN                -- user does not have attribute  continue loop to next record                 CONTINUE           END               END LOOP  END

User · Answer

How about the ole goto statement  i know  i know  but it works just fine here     DECLARE    v attr char 88   CURSOR  SELECT USERS IS SELECT id FROM USER TABLE WHERE USERTYPE    X   BEGIN     FOR user rec IN SELECT USERS LOOP             BEGIN             SELECT attr INTO v attr              FROM ATTRIBUTE TABLE             WHERE user id   user rec id                       EXCEPTION             WHEN NO DATA FOUND THEN                -- user does not have attribute  continue loop to next record                 goto end loop           END            lt  lt end loop gt  gt          null               END LOOP  END    Just put end loop at very end of loop of course   The null can be substituted with a commit maybe or a counter increment maybe  up to you

User · Answer

For this example you really should just use an outer join   declare begin   FOR attr rec IN        select attr      from USER TABLE u      left outer join attribute table a      on   u USERTYPE    X  and a user id   u id        LOOP      lt process records gt        lt if primary key of attribute table is null       then the attribute does not exist for this user  gt    END LOOP  END

User · Answer

The CONTINUE statement is a new feature in 11g   Here is a related question    39 CONTINUE  39  keyword in Oracle 10g PL SQL

User · Answer

Notice you can use WHEN exception THEN NULL the same way as you would use WHEN exception THEN continue  Example       DECLARE         extension already exists  EXCEPTION          PRAGMA EXCEPTION INIT extension already exists  -20007           l hidden col name  varchar2 32       BEGIN         FOR t IN    SELECT table name  cast extension as varchar2 200   ext                     FROM all stat extensions                     WHERE owner     prev schema                           and droppable  YES                      ORDER BY 1                            LOOP             BEGIN                 l hidden col name    dbms stats create extended stats     schema      t table name  t ext               EXCEPTION                 WHEN extension already exists THEN NULL    -- ignore exception and go to next loop iteration             END          END LOOP      END

User · Answer

In the construct you have provided  you don t need a CONTINUE  Once the exception is handled  the statement after the END is performed  assuming your EXCEPTION block doesn t terminate the procedure  In other words  it will continue on to the next iteration of the user rec loop   You also need to SELECT INTO a variable inside your BEGIN block   SELECT attr INTO v attr FROM attribute table      Obviously you must declare v attr as well

[oracle] Is it possible to CONTINUE a loop from an exception?

Examples related to oracle

Examples related to plsql