Skip to Main Content
  • Questions
  • How to return header and data using sys_refcursor in oracle pl sql

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Purva.

Asked: October 19, 2020 - 7:48 am UTC

Answered by: Chris Saxon - Last updated: October 20, 2020 - 10:28 am UTC

Category: PL/SQL - Version: 12c

Viewed 100+ times

You Asked

I want to return headers of column along with data while returning result using refcursor.


create table fetch_header_rows
(
company_name varchar2(500),
Company_id number,
ammount number(20,8),
data_commnets varchar2(500)
);

insert into fetch_header_rows values('company1' , 1, 128.80,'test data1');

insert into fetch_header_rows values('company2' , 2, 129.80,'test data1');

insert into fetch_header_rows values('company3' , 3, 128,'test data1');

insert into fetch_header_rows values('company4' , 4, 100.80,'test data1');


create or replace procedure SP_fetch_header_rows(data_fetch  out sys_refcursor )
as

begin

open data_fetch for 
select * from fetch_header_rows;

end;



Here we are fetching cursor result in file. hence we required header as a first row in data.
( in current scenario we have more than 150 columns)

and we said...

You can convert it to a dbms_sql cursor with dbms_sql.to_cursor_number, then use describe_columns to get the column names:

declare
  cur        sys_refcursor;
  cur_handle number;
  rec_tab    dbms_sql.desc_tab;
  col_cnt    integer;
begin
  SP_fetch_header_rows ( cur );
  cur_handle := dbms_sql.to_cursor_number ( cur ) ;
  
  dbms_sql.describe_columns ( cur_handle, col_cnt, rec_tab );
  
  for i in 1 .. rec_tab.count loop
    dbms_output.put_line ( rec_tab(i).col_name );
  end loop;
  
  dbms_sql.close_cursor ( cur_handle );
end;
/

COMPANY_NAME
COMPANY_ID
AMMOUNT
DATA_COMMNETS

More to Explore

PL/SQL

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