PL SQL - for loop through cursor

Question

I'm having issues looping through my cursor

I have multiple location_name entries that i'm trying to display 5 at a time, and set it to look_item_cursor.

CURSOR look_item_cursor IS

  SELECT *
    FROM (SELECT DISTINCT location_name
            FROM inventory_info_v i
           WHERE i.item_code = lr_item.item_code) a
   WHERE rownum <= (ln_page + 4)
     AND rownum >= ln_page;

the original query returns the data back just fine. both ln_page and lr_item.item_code are filled by the time the cursor is called.

I attempt to retrieve each of the 5 returned location_name, one at a time, with

OPEN look_item_cursor;
      BEGIN
        FOR lv_location_name IN look_item_cursor LOOP
          pv_message_return := pv_message_return ||
                               lv_location_name.location_name;
        END LOOP;
      END;
CLOSE look_item_cursor;

with lv_location_name being assigned as a

look_item_cursor%ROWTYPE

However I'm getting a general exception when it runs through the query, and i'm not sure why. Any help would be appreciated!

If you use cursor `FOR` loop (as you are trying to do in your example) then 1. do not open a cursor explicitly (cursor `FOR` loop does it for you); 2. do not declare cursor loop variable explicitly as you said you did with `lv_location_name` variable (cursor `FOR` loop does it for you). So you either work with a cursor explicitly (Open, fetch and close explicitly), or you use cursor `FOR` loop, which does most of the work for you (opens the cursor, declares cursor variable, fetches and closes the cursor). — Nick Krasnov, Jun 09 '17 at 16:27
very helpful, thank you. Do i need to include an 'exit when' at all for this type of loop? i ask because it errors upon assigning the 5th and final entry EDIT: nevermind that was something stupid. thank you very much — M. Doe, Jun 09 '17 at 16:52

0xdb · Answer 1 · 2017-06-10T15:20:31.003

From oradoc

Cursor FOR LOOP Statement
The cursor FOR LOOP statement implicitly declares its loop index as a record variable of the row type that a specified cursor returns, and then opens a cursor. With each iteration, the cursor FOR LOOP statement fetches a row from the result set into the record. When there are no more rows to fetch, the cursor FOR LOOP statement closes the cursor. The cursor also closes if a statement inside the loop transfers control outside the loop or raises an exception.

Try something like this (replace the test data with your query inside in (...) loop):

begin
    for row_ in (
        select 1 id, 'item 1' name from dual union all      
        select 2 id, 'item 2' name from dual union all      
        select 3 id, 'item 3' name from dual 
        ) loop
        dbms_output.put_line('id:'||row_.id||' name:'||row_.name);
    end loop;
end; 

-- dbms output
id:1 name:item 1
id:2 name:item 2
id:3 name:item 3

diziaq · Answer 2 · 2017-06-13T13:28:12.870

This code is making the cursor to get opened twice:

explicitly in OPEN look_item_cursor
implicitly in initialization of FOR-LOOP

I suppose you get ORA-06511: PL/SQL Cursor already open exception. To avoid it - remove OPEN and CLOSE statements, just use cursor in loop and it will be opened and closed automatically.

It is necessary to be wary of some problem-makers I notice in the code:

Declaring loop variable is not necessary. Remove it from declaration section - just use it locally in FOR-LOOP.
Passing variable ln_page from outer contex should be fixed by strict cursor parameter passing.
Lower border for rownum (AND rownum >= ln_page) won't work as you expect. On Oracle 12c use LIMIT ROWS clause, otherwise inline view with row numbers is required. Both cases are particularly described here.

this was solved per @Nicholas Krasnov s comment. very helpful, thank you — M. Doe, Jun 13 '17 at 17:51

PL SQL - for loop through cursor

2 Answers2