CODEWITHSUNDEEP
oracle-databaseplsql
NewbUser
NewbUser

Reputation: 303

How do I handle the exception in this pl/sql for loop correctly?

First off, I'm a relative newbie to PL/SQL so I might be missing something trivial.

Here is a snippet of code that I'm having issues with running - 

FOR indx IN 1 .. arr.COUNT
    LOOP
        SELECT COUNT(*), ca.cities
        INTO tmp_count, affected_cities
        FROM PDB.utilities ca
        WHERE (ca.app_city_id           = cityid
        AND ca.app_plumbing_id = arr(indx))
        AND( BITAND(options1,2)        = 2
            OR BITAND(options1,1)          = 1)
        GROUP BY ca.cities;

        IF tmp_count                  >=0 THEN
             -- We have an affected app so collect metrics
            IF plumbings(indx_mv)  ='0Ci30000000GsBN' THEN
                count_wrigley:= count_wrigley+tmp_count;
            END IF;
            counter:= counter+tmp_count; --overall count. 
            tmp_count:=0;
            affected_cities:=null;
        END IF;

        EXCEPTION -- error thrown here !
            WHEN NO_DATA_FOUND THEN
                CONTINUE;
        END;  
    END LOOP; -- Error thrown here too.

And here is my error trace -

Error report:
ORA-06550: line 64, column 13:
PLS-00103: Encountered the symbol "EXCEPTION" when expecting one of the following:

   ( begin case declare end exit for goto if loop mod null
   pragma raise return select update while with <an identifier>
   <a double-quoted delimited-identifier> <a bind variable> <<
   continue close current delete fetch lock insert open rollback
   savepoint set sql execute commit forall merge pipe purge
ORA-06550: line 68, column 11:
PLS-00103: Encountered the symbol "LOOP" when expecting one of the following:

   ;
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

It's worth noting that the block fails only with the exception handling and compiles successfully otherwise. So my guess is I'm doing something wrong there?

Any help would be greatly appreciated! Thanks

Upvotes: 1

Views: 3672

Answers (1)

APC
APC

Reputation: 146209

EXCEPTION aligns with BEGIN ... END blocks. There is no BEGIN inside your loop, so there should be no exception either.

It seems the purpose of the exception is to suppress NO_DATA_FOUND errors inside the loop. So to fix this error you need to put a BEGIN / END block in the loop too. (Ah, you have an END just no BEGIN - your code would hurl with the EXCEPTION block).

FOR indx IN 1 .. arr.COUNT
LOOP
    BEGIN 
        SELECT COUNT(*), ca.cities
        INTO tmp_count, affected_cities
        FROM PDB.utilities ca
        ....
    EXCEPTION 
          WHEN NO_DATA_FOUND THEN
             CONTINUE;
    END;  
END LOOP;

Upvotes: 6

Related Questions