Dynamic cursor in inner loop. Multiple cursors

Question

Consider the following.

declare

v_name     person.name%TYPE;
v_surname  person.surname%TYPE;

cursor department_cursor is
    select * from department;

cursor person_cursor is 
    select * from person 
    where nvl(name, '')   = nvl(v_name, '');    

begin
  open department_cursor;  
  open person_cursor;
  for department_row in department_cursor loop

    v_name  := department_row.name;
    v_surname  := department_row.surname;

    for person_row in person_cursor loop
        DBMS_OUTPUT.PUT_LINE('I got here:'||person_row.p_id);
    end loop;   

  end loop;
  close person_cursor;
  close department_cursor;
end;
/

Don't try to understand what it does. It's just a stripped/vandalized version of the actual code. The juice though remains. What I want to do, is have two cursors. The second cursor is dynamic and it depends on the rows that the first cursor returns.

The above results in ORA-06511: PL/SQL: cursor already open .

Bob Jarvis - Слава Україні · Accepted Answer · 2014-09-24 14:25:47Z

2

The problem is that you have lines which read

open department_cursor;  
open person_cursor;

and later you have other lines which read

for department_row in department_cursor loop

and

for person_row in person_cursor loop

The later lines are attempting to open cursors which are already open.

I suggest you rewrite your code as:

declare
  v_name     person.name%TYPE;
  v_surname  person.surname%TYPE;

  cursor department_cursor is
    select * from department;

  cursor person_cursor is 
    select * from person 
    where nvl(name, '')   = nvl(v_name, '');    
begin
  for department_row in department_cursor loop
    v_name  := department_row.name;
    v_surname  := department_row.surname;

    for person_row in person_cursor loop
        DBMS_OUTPUT.PUT_LINE('I got here:'||person_row.p_id);
    end loop;   
  end loop;
end;

Share and enjoy.

answered Sep 24, 2014 at 14:25

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

50.2k10 gold badges81 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Alkis Kalogeris Over a year ago

I didn't know that this syntax opens the cursor too. Thank you. It worked. I will mark it in too mins.

Collectives™ on Stack Overflow

Dynamic cursor in inner loop. Multiple cursors

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related