CODEWITHSUNDEEP
oracle-databaseplsql
F.donoso
F.donoso

Reputation: 13

Nested implicit cursor not clearin data

I have an oracle pl/sql anonymous block with an implicit cursor in a for loop that is nested on a for loop in this way:

FOR secuence IN 1..3 LOOP
  FOR registro_notificacion IN (
             SELECT 'data' FROM my_table WHERE my_table.column1=secuence)
      LOOP
         --work with data
  END LOOP; END LOOP;

The problems occurs when I have values returned on secuence=1 and empty on the others because for some reason the implicit cursor doesn´t clean itself. So, on the secuence=2 im suposed to don't do nothing because there is no data, but for some reason it still have the data returned on the first loop (secuence=1).

Should I declare and explicit cursor to close at the end of every iteration? Although I consider to use 3 for loops, one for every secuence value but that its not the idea I guess.

Upvotes: 1

Views: 147

Answers (1)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

I use cursor for-loops all the time and have never seen the situation you describe. The only thing I can think is that you have a variable named secuence declared elsewhere in your program that happens to be set to 1. In this case, the PL/SQL compiler may be choosing to use the global variable secuence rather than the loop-control variable secuence when generating the SQL statement. A good rule of thumb is to use a unique name for each loop-control variable.

However, you can get rid of the FOR secuence... loop entirely by using:

FOR registro_notificacion IN (SELECT 'data'
                                FROM my_table
                                WHERE my_table.column1 BETWEEN 1 AND 3
                                ORDER BY my_table.column1)
LOOP
     --work with data
END LOOP;

Opening one cursor is generally less costly than opening three cursors.

Best of luck.

Upvotes: 1

Related Questions