Thanks for the question, Viraj.

Asked: January 21, 2016 - 8:46 am UTC

Last updated: March 14, 2016 - 11:03 am UTC

Version: 11g

Viewed 1000+ times

You Asked

Hi Tom,

Good day!

There is a procedure which returns it's result in a cursor.

I want to call above procedure inside a new procedure and populate the returned cursor's data into a global temp table for me to construct an XML later on.

So my question is, is there a straight forward way that can be done?

Thank you very much.

and Chris said...

Same way you would handle any cursor. Build a procedure to fetch the results. Then insert them!

create table t as
  select rownum x from dual connect by level <= 10;

create global temporary table gtt (
  x integer
);

create or replace procedure open_cur (
  cur out sys_refcursor
) as
begin
  open cur for
    select * from t;
end;
/
sho err

create or replace procedure ins_gtt as
  cur sys_refcursor;
  type gtt_tab is table of t%rowtype index by binary_integer;
  recs gtt_tab;
begin
  open_cur ( cur );
  
  fetch cur 
  bulk collect into recs;
  
  forall i in 1 .. recs.count 
    insert into gtt (x)
    values (recs(i).x);
    
  close cur;
end;
/
show err

select count(*) from gtt;

  COUNT(*)
----------
         0

exec ins_gtt;

select count(*) from gtt;

  COUNT(*)
----------
        10

Rating

(3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks for the very quick reply

Viraj Perera, January 21, 2016 - 10:34 am UTC

It was useful as I can assure there is no other way to do it.

The reason for asking this is, I have a procedure which returns a cursor that has quite number of columns and I supposed to form an XML using some of the columns in that result set (returned cursor).
So I wanted to check whether if I can do something like this,

--Assuming the existing procedure is bill_Pkg.get_invoice_dtl(number IN, Cursor Out);

procedure print_bill(pi_bill_id number) as 
ref_cur bill_Pkg.ref_cur;
begin
 bill_Pkg.get_invoice_dtl (pi_bill_id , ref_cur);
 <i>Here I wanted to insert only the columns which I needed from ref_cur in to the global temp table. Then later I can convert my global temp table's data in to an XML</i>
end;

Thank you.

January 21, 2016 - 10:57 am UTC

Yes, you can insert just the columns you want from the cursor. Just name the ones you want in your insert. If it has 10 columns, but you only want two you can do something like:

 
forall i in 1 .. recs.count 
  insert into gtt (col1, col2)
  values (recs(i).col1, recs(i).col2);

why is cursor required ?

A reader, January 21, 2016 - 5:00 pm UTC

In your solution ,can a direct insert from source table to gtt not be done ? I guess this is just to answer the question at hand,right ?

January 22, 2016 - 1:13 am UTC

Thats correct.

not getting the result

A reader, March 11, 2016 - 5:04 am UTC

plz elaborate i am not getting the join result in table
thx you

March 14, 2016 - 11:03 am UTC

"i am not getting the join result in table"

Join result in which table?

Please elaborate!

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Thanks for the very quick reply

why is cursor required ?

not getting the result

More to Explore

PL/SQL demos

PL/SQL docs

populate data inside an open cursor into a Globle temp table

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Thanks for the very quick reply

why is cursor required ?

not getting the result

More to Explore

PL/SQL demos

PL/SQL docs