Is it possible to CONTINUE a loop from an exception?

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 & 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开发者_JAVA技巧 THEN
               -- user does not have attribute, continue loop to next record.
               CONTINUE;
         END;         
    END LOOP;
END;

The CONTINUE statement is a new feature in 11g.

Here is a related question: 'CONTINUE' keyword in Oracle 10g PL/SQL

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...

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;

        <<end_loop>>
        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.

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
    <process records> 
    <if primary key of attribute_table is null 
     then the attribute does not exist for this user.>
  END LOOP;
END;

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;