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
– Nick Krasnov, Commented Jun 9, 2017 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
– M. Doe, Commented Jun 9, 2017 at 16:52

0xdb · Accepted Answer · 2017-06-10 15:20:31Z

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 · Accepted Answer · 2017-06-13 13:28:12Z

0

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.

edited Jun 13, 2017 at 13:28

answered Jun 13, 2017 at 13:22

diziaq

7,90518 gold badges74 silver badges130 bronze badges

1 Comment

M. Doe Over a year ago

this was solved per @Nicholas Krasnov s comment. very helpful, thank you

Collectives™ on Stack Overflow

PL SQL - for loop through cursor

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related