oracle FOR LOOP does not iterate in SYS_REFCURSOR

Question

Here is the Procedure:

Opening a cursor and then fetching the output of select query through bulk collect.
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.
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;

Some test data would be very helpful. I'm not clear what you need the procedure to do. For example, what is l_names for, if you want to return a cursor? You can only produce a cursor with a query, and you can only open it once. — William Robertson
– William Robertson, Commented Mar 12, 2021 at 19:58
In your procedure you pass an ID and only select rows from table1 with that ID, but the expected output includes multiple IDs, so I am still not clear what you need it to do. — William Robertson
– William Robertson, Commented Mar 13, 2021 at 9:47
@William When I am passing i_id (from Proc) to t_id (as input) in table1, based on the input there would be multiple matching id's fetched based on join conditions that is the reason you see 3 ID's. 1. So basically fetch all the ID's matching based on the t_id passed to procedure. 2. Store the ID's in some variable /collections 3. Iterate the ID'd in next select queries as input 4. Generate the output as sys_refcursor or arrays — Dev1
– Dev1, Commented Mar 13, 2021 at 9:55

William Robertson · Accepted Answer · 2021-03-13 14:27:32Z

3

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.

edited Mar 13, 2021 at 14:27

answered Mar 13, 2021 at 9:57

William Robertson

16.1k4 gold badges40 silver badges49 bronze badges

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

10 Comments

William Robertson Over a year ago

Works for me: dbfiddle.uk/…

Dev1 Over a year ago

Apologies William had added a extra ; in my code. However I do not have privilege's to run create or replace type number_tt as table of number; seems some restrictions in place by DBA. Would be mind checking why the for loop with the SYS_REFCURSOR does not iterates ? If that is solved then my question would be answered

Justin Cave Over a year ago

@Linnea - The loop does iterate. In each iteration, you're opening a new cursor which implicitly discards the previously opened cursor. So when the procedure returns, only the most recently opened cursor is returned.

William Robertson Over a year ago

That's really the whole point of my answer. Don't open the cursor for each value in the collection, one value at a time. Open it only once, passing the whole collection. I used member of but other syntax variations will also work (join, in (subquery)).

William Robertson Over a year ago

Output what though? rc is a ref cursor. It represents a set of zero or more rows. You are returning one ref cursor, rc. You have to open it from a query, you can't built it incrementally. What has iteration got to do with it?

|

Littlefoot · Accepted Answer · 2021-03-12 18:12:42Z

1

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;

answered Mar 12, 2021 at 18:12

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

5 Comments

Littlefoot Over a year ago

OK, but - as you saw - it just won't work. RC returned the first ID returned in your L_NAMES and that's it. It doesn't matter how many tables you have to join, Oracle is capable of doing it.

Littlefoot Over a year ago

If you have to do it that way, then store returning values into a collection - add piece by piece as the code goes on - and return it (the collection) instead of refcursor.

Littlefoot Over a year ago

But, you've already done that, L_NAMES is a collection. Create one (at SQL level) which will hold the result you want to be returned.

Littlefoot Over a year ago

I meant, CREATE TYPE at SQL level, not within the procedure, otherwise you won't be able to use the result out of this procedure, and that's what you do plan to do, right? Why? Because you're RETURNING the result out of the procedure!

Dev1 Over a year ago

Provided solution does not answer my question, if you please lookin to the provided code it would be helpful

Collectives™ on Stack Overflow

oracle FOR LOOP does not iterate in SYS_REFCURSOR

2 Answers 2

10 Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related