CODEWITHSUNDEEP
oracle-databasefor-loopplsqlcursor
M. Doe
M. Doe

Reputation: 199

PL SQL - for loop through cursor

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!

Upvotes: 0

Views: 3690

Answers (2)

diziaq
diziaq

Reputation: 7795

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:

  1. Declaring loop variable is not necessary. Remove it from declaration section - just use it locally in FOR-LOOP.

  2. Passing variable ln_page from outer contex should be fixed by strict cursor parameter passing.

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

Upvotes: 0

0xdb
0xdb

Reputation: 3697

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

Upvotes: 1

Related Questions