Skip to Main Content
  • Questions
  • Fetch Ref cursor to another ref cursor

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Naresh Kumar.

Asked: January 19, 2017 - 3:10 pm UTC

Last updated: January 24, 2017 - 1:00 am UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

Hi,
First of all Thank you Gurus! for your time and effort to solve user issues.

I have a requirement where I have to consume first row of the big query result set and fetch the rest of the result into a
refcursor(for some reason I can't change out cursor type).
Below is the sample pl/sql


declare
l_ref_cursor sys_refcursor;
out_ref_cursor sys_refcursor;
l_counter number:=1;
v_col1 my_table.col1%type :=null;
v_col2 my_table.col2%type :=null;
v_col3 my_table.col3%type :=null;
begin
open l_ref_cursor for select col1,col2, col3 from my_table;
loop
if l_counter=1
then
fetch l_ref_cursor into v_col1,v_col2,v_col3;
exit when v_out_pmt_search_list%notfound ;
-- few statements for fetched variables
else
fetch l_ref_cursor into out_ref_cursor ; -- I would like fetch only col1, col2 into out ref cursor
exit when v_out_pmt_search_list%notfound ;
end if;
end loop;
end;

and Chris said...

You don't fetch one refcursor into another. You assign it:

declare
  l_ref_cursor sys_refcursor;
  out_ref_cursor sys_refcursor;
  l_counter number          :=1;
  v_col1 int;
begin
  open l_ref_cursor for select 1 col1 from dual;
  loop
    if l_counter=1 then
      fetch l_ref_cursor into v_col1;
      exit when l_ref_cursor%notfound;
    else
      out_ref_cursor := l_ref_cursor;
      exit when l_ref_cursor%notfound;
    end if;
  end loop;
end;
/


If you only need the first two columns, then only fetch those in the calling procedure.

Rating

  (6 ratings)

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

Comments

Perfect

Naresh Kumar Namburi, January 19, 2017 - 5:28 pm UTC

Thanks Chris!
I have a followup question
If I fetch only part of cursor then I was getting below error.
ORA-06504: PL/SQL: Return types of Result Set variables or query do not match
I can fetch into another variable and not use it since I don't need the second field.
But this code called from SOA where present setup is expecting one column, and don't wants to change on soa side.
Is there any way can I just send part of output fields?

Chris Saxon
January 20, 2017 - 10:35 am UTC

Why do you need to assign one cursor to the other? You could just open a new cursor:

declare
  l_ref_cursor sys_refcursor;
  out_ref_cursor sys_refcursor;
  l_counter number          :=1;
  v_col1 int;
 v_col2 int;
begin
  open l_ref_cursor for select 1 col1, 2 col2 from dual;
  loop
    if l_counter=1 then
      fetch l_ref_cursor into v_col1, v_col2;
      exit when l_ref_cursor%notfound;
    else
      open out_ref_cursor for select 1 col1 from dual;
      exit;
    end if;
  end loop;
end;
/

Trying to avoid multiple select's

Naresh Kumar Namburi, January 20, 2017 - 3:11 pm UTC

Hi Chris,

Select query is a big and complicated query and here I'm trying to gain some performance without running select twice.
For sure I need to access first few records and rest to output.
But I struck where I wanted to send less columns than selected columns from first cursor.

Chris Saxon
January 20, 2017 - 4:37 pm UTC

So why does your first cursor need more columns? This whole approach seems rather strange...

Naresh Kumar Namburi, January 20, 2017 - 5:43 pm UTC

Hi Chris,
I hope I'm not confusing you here.
Here is more elaborated requirement.
this procedure presently I'm executing a select to get total number of records and another select for sending paginated number of records ( like page 1 - 1 to 100 records, page 2 101 to 200 ..etc) as per the request
Here in this return list I need return records in order of table A , B, C.

With below new approach I would like to execute select only once and get total count from first row and same result set from second cursor

 
declare
  l_ref_cursor sys_refcursor;
  out_ref_cursor sys_refcursor;
  out_total_count number;
  l_counter number          :=1;
  v_col1 int;
   v_row_number int;  v_col2 int;  v_col3 int;  v_total_count int;
begin
open l_ref_cursor for 
select  row_number, col1, col2, col3, total_count
from
     (select col1, col2, col3, (rownum )r ,(rownum -1) row_number , (count(*) over() -1 ) as total_count
      from
          (select col1, col2, col3 
          from 
             (select null col1, null col2, null col3 from dual dummy_row ) 
              union all  
                (select col1, col2, col3 from 
                  (select 1 col1, 2 col2, 3 col3 from dual a
                  group by 1,2,3 order by 1
                  )
                  union all
                  select  4 col1, 5 col2, 6 col3 from dual  b     
                  group by 1,2,3 
                )
             union all
             select 7 col1, 8 col2, 9 col3 from dual c
          )
      ) 
where r=1 
         or r between 1 and  5 +1;   -- row_number 1- 5(page 1)
-- senario 2  where r=1 or r between 5 and 10+1  ( page 2)    -- if I dont have first dummy_row  then return rows  will be 0  and I  can't  get total_count 
  loop
    if l_counter=1 then
      fetch l_ref_cursor into  v_row_number, v_col1, v_col2, v_col3, v_total_count; 
       out_total_count:= v_total_count; -- out_total_count is output parameter 
      exit when l_ref_cursor%notfound;
    else
      out_ref_cursor := l_ref_cursor; -- Here I wanted to avoid the total_count column   before assigning to output cursor
      exit when l_ref_cursor%notfound;
    end if;
  end loop;
  end;

Chris Saxon
January 23, 2017 - 5:19 pm UTC

Why the need to get the total row count?! No-one really needs to know this, it's just a waste of resource to compute.

UI purpose

Naresh Kumar, January 23, 2017 - 7:45 pm UTC

Hi Chris,
I wish count is not necessary but we do need the counts also crucial in the services.

Right now I'm executing 2 queries, 1 for count and 2nd for data.
some times this procedure got executed for count only also.
So If we can't make it possible to send 2nd cursor with less fields then I don't have a option except executing select twice.

Example: count required to know upper limit

Naresh Kumar, January 23, 2017 - 8:08 pm UTC

Hi Chris,
Just giving you an example
http://www.valuecityfurniture.com/search/v/on-sale
In this page, above sort by
1-48 of 186 results

This is what I need from 1 query.
1)what are 1-48 items and
2)Total item count (upper limit- 186)
Connor McDonald
January 24, 2017 - 1:00 am UTC

What is critical here is an understanding of the data.

If I search for "Computer Mouse" on a *retail* website, then (as a developer) I know that the result is pretty much guaranteed to be limited to a few hundred results.

So the overhead in show "1 to 30, of 300" is not a massive cost.

But if I search for "Computer Mouse" on a *search engine*, then the result could be millions, or billions of hits. It is then impractical to give the count of results. (Its why Google, Bing etc never show you the count...the simply give you an estimate, "1 to 30 of about 10,000,000" etc.




Naresh, January 26, 2017 - 3:26 pm UTC

Thanks Connor.

But Here I fall into category 1( "Computer Mouse" on a *retail* website).
Some times even SP gets called to get the counts only.
My question was how to tweak the first cursor and send only few columns of it into out (2nd) cursor without opening 2 cursors for select. I explained my scenarios on the top.
Looks like it's not doable so I'm working on changing the requirement and caller has to consume extra column

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library