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;