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)
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