I have created a Procedure which uses a View to PRINT (into CSV ) , all the records of that view. Any user can drop and recreate that view , so its column structure can change
Is there any way to FETCH all records of VIEW into a collection and PRINT elements of that collection without knowing ALL COLUMN NAMES/ without any dependency on columns.
If there is any way, then it can be used when our table has hundreds of columns too. THIS QUESTION HAS BEEN ASKED TO ME MANY TIMES but I cannot find the answer to it and now i have to edit my procedure every time i need to spool a different table into csv file
The code I use normally for spooling takes all the column names:-
cursor c1 is
select *
from table1 nvr;
TYPE alldata_typ IS table of c1%rowtype;
v_dta alldata_typ;
BEGIN
v_fname := 'swe_WRF_.csv';
v_file := UTL_FILE.FOPEN('CDMP_OUT_DIR', v_fname, 'W');
v_header := 'FSA_CODE,FSA_NAME,PROVINCE,MAKE,SERIES,REPORT_YEAR,VEHICLE_COUNT';
UTL_FILE.PUT_LINE(v_file, v_header);
OPEN c1;
loop
FETCH c1 BULK COLLECT
INTO v_dta LIMIT v_fetch_limit;
exit when v_dta.count=0 ;
FOR i IN 1..v_dta.count
LOOP
UTL_FILE.PUT_LINE(v_file,
v_dta(i).fsa_code ||', '||
v_dta(i).fsa_name ||', '||
v_dta(i).province ||', '||
v_dta(i).make ||', '||
v_dta(i).series ||', '||
v_dta(i).report_year ||','||
v_dta(i).vehicle_count
);
v_count := v_count + 1;
end loop;
;
You need use dynamic SQL to generate all column to csv. I rewrited your code as below. Hope it useful for you.
declare
v_cmd varchar(4000);
c1 SYS_REFCURSOR;
v_tab_name varchar2(100) := upper('TABLE_NAME');
v_owner_name varchar2(100) := upper('OWNER_NAME');
type record_type is record(
line_data varchar2(4000));
TYPE alldata_typ IS table of record_type;
v_dta alldata_typ;
v_fname varchar2(20);
V_HEADER varchar2(20);
V_FETCH_LIMIT integer := 100;
V_COUNT integer;
BEGIN
v_fname := 'swe_WRF_.csv';
v_file := UTL_FILE.FOPEN('CDMP_OUT_DIR', v_fname, 'W');
select listagg(COLUMN_NAME, ',') within group(order by COLUMN_ID)
into v_header --create header with all column
from all_tab_columns
where table_name = v_tab_name
and OWNER = v_owner_name;
UTL_FILE.PUT_LINE(v_file, v_header);
select 'select ' || listagg(COLUMN_NAME, '||'', ''||') within group(order by COLUMN_ID) || ' as line_data from ' || v_tab_name
into v_cmd --generate select statement
from all_tab_columns
where table_name = v_tab_name
and OWNER = v_owner_name;
OPEN c1 for v_cmd;
loop
FETCH c1 BULK COLLECT
INTO v_dta LIMIT v_fetch_limit;
exit when v_dta.count = 0;
FOR i IN 1 .. v_dta.count LOOP
UTL_FILE.PUT_LINE(v_file,
v_dta(i).line_data
);
--dbms_output.put_line(v_dta(i).line_data);
v_count := v_count + 1;
end loop;
END LOOP;
end;