sys_refcursor from procedure not returning data

Question

CREATE TYPE prList AS TABLE OF pr;

create or replace
procedure qLink(ret  out sys_refcursor)
 IS
myPrList prList := prList();


  open ret for
  select * 
  from (select a.lin, a.pr, 
               b.name, sum(a.up) as u,
               sum (a.d)         as d
          from li_dy_4 a,
               p_list  b,
               TABLE(CAST(myPrList as prList)) my_list
         where a.pr=b.id 
           and b.parent_id != 0 
           and a.partitionid <= 308 
           and a.partitionid >= 302 
           and a.pr = my_list.pr
         GROUP BY a.pr, b.name, a.lin  
         order by d desc) ;


BEGIN 

 myPrList := prList ( pr(91),
                   pr(80));


END;

  variable rc refcursor;
  exec qlink( :rc );
  print rc;

[/code]

The above sample code return no data,only table fields are displayed.what have I miss here? but if didnt use the refcursor evry thing works fine even I tried passing array as a list it works with the above code,please help ...

Answer 1

I think it must be like this:

create or replace
procedure qLink(ret  out sys_refcursor)
 IS
myPrList prList := prList();

BEGIN 

 myPrList := prList ( pr(91),pr(80));

  open ret for
  select * 
  from (select a.lin, a.pr, 
               b.name, sum(a.up) as u,
               sum (a.d)         as d
          from li_dy_4 a,
               p_list  b,
               TABLE(CAST(myPrList as prList)) my_list
         where a.pr=b.id 
           and b.parent_id != 0 
           and a.partitionid <= 308 
           and a.partitionid >= 302 
           and a.pr = my_list.pr
         GROUP BY a.pr, b.name, a.lin  
         order by d desc) ;

END;

And perhaps this TABLE(CAST(myPrList as prList)) my_list can be replaced simply by this one: TABLE(prList(pr(91),pr(80))) my_list - but I did not test it!