oracle FOR LOOP does not iterate in SYS_REFCURSOR

Question

Here is the Procedure:

1. Opening a cursor and then fetching the output of select query through bulk collect.
2. Issue is all the ID's are getting stored in bulk collect but I am unable to loop through the second select query by using the bulk collect variable as input, It only takes first ID into consideration instead of all.
3. OUTPUT should be a SYS_REFCURSOR, please shed light on what am I missing here

test data for table1:

ID	CURRENCY	T_ID
10	GBP	PB1
15	GBP	RB
20	GBP	CC
25	AUD	DC

Based on the t_id I am fetching the corresponding ID's and then using those ID's in further select for loop statements.

CURRENT OUPUT OF THE PROC THROUGH SYS_REFCURSOR:

ID	COUNTRY	ACCOUNT
10	UK	PB1

EXPECTED OUTPUT:

ID	COUNTRY	ACCOUNT
10	UK	PB1
15	Wales	RB
20	SH	CC

create or replace procedure myproc (i_id in varchar2, rc out sys_refcursor)
as
    cursor names_cur is
        select id from table1 where currency='GBP' and t_id=i_id;
    names_t names_cur%ROWTYPE;
    type names_ntt is table of names_t%TYPE;
    l_names names_ntt;
begin
    open names_cur;
    fetch names_cur bulk collect into l_names ; --Inside l_names (10,15 & 20) would be stored 
    close names_cur;
--iSSUE IS WITH BELOW FOR LOOP
    for cur in l_names.first..l_names.last loop
        open rc for --For the below select I want to iterate l_names so for the above scenario it should iterate thrice

        select s.id,s.country,s.account from table2 s where s.id=l_names(cur).id;
    end loop;

end myproc;

Answer 1

Note following extended comments:

Perhaps at the centre of the question is a misunderstanding of what a cursor is. It's not a container full of records, it's a specification for a result set, as at a point in time, based on a single SQL query. So if you

open rc for select id from table1;

and pass rc back to the caller, you are not passing any data, you are passing a pointer to a private memory area containing a prepared query. You don't push the results, the caller pulls them. It's like a program that the caller will execute to fetch the rows. You can't open it a bit more to add another row, which I think is what you were hoping to do.

---

To use a collection in a cursor within a procedure, the collection type has to be created as a separate schema object (though of course you can reuse collection types in other procedures, so it's not as restrictive as it sounds).

If you can't create a type, see what types already exist that you can use:

select owner, type_name
from   all_coll_types t
where  t.coll_type = 'TABLE'
and    t.elem_type_name = 'NUMBER';

For example:

create or replace type number_tt as table of number;

create table table1 (id primary key, currency, t_id) as
    select 10, 'GBP', 'PB1' from dual union all
    select 15, 'GBP', 'RB' from dual union all
    select 20, 'GBP', 'CC' from dual union all
    select 25, 'AUD', 'DC' from dual;

create table table2 (id,country,account) as
    select 10, 'UK', 'PB1' from dual union all
    select 15, 'Wales', 'RB' from dual union all
    select 20, 'SH', 'CC' from dual;

Now the procedure can be:

create or replace procedure myproc
    ( rc out sys_refcursor)
as
    l_names number_tt;
begin
    select id bulk collect into l_names
    from   table1
    where  currency = 'GBP';

    open rc for
        select t.id,t.country,t.account from table2 t
        where  t.id member of l_names;
end myproc;

Cursor output:

        ID COUNT ACC
---------- ----- ---
        10 UK    PB1
        15 Wales RB
        20 SH    CC

(I removed the i_id parameter in your procedure as I wasn't clear how you wanted to use it.)

Presumably this is a simplified version of the actual issue, because as it stands you could use the first query as a subquery and you wouldn't need the collection:

create or replace procedure myproc
    ( rc out sys_refcursor)
as
begin
    open rc for
        select t.id,t.country,t.account from table2 t
        where  t.id in
               ( select id 
                 from   table1
                 where  currency = 'GBP' );
end myproc;

or just join it, as Littlefoot suggested:

create or replace procedure myproc
    ( rc out sys_refcursor)
as
begin
    open rc for
        select t2.id, t2.country, t2.account
        from   table1 t1
               join table2 t2 on t2.id = t1.id
        where  t1.currency = 'GBP';
end myproc;

However, you commented on that answer that you couldn't do that because your requirement seemed to be to do it via a collection, a loop, some duct tape, two cats and a fusion generator.

Answer 2

What do you need the cursor for? As well as procedure's IN parameter (as you never used it)?

Anyway:

create or replace procedure myproc (i_id in varchar2, rc out sys_refcursor)
  as
begin
  open rc
    select t.id,
           t.country,
           t.account 
    from table2 t join table1 a on a.id = t.id
    where a.currency = 'GBP';
end;