Hello Everyone
I will be grateful for your advice on how to accomplish the above. I have a table that contain multiple dates in a column. So select distinct date on this column looks like this
27/05/2020
28/05/2020
29/05/2020
I want to loop round this table and create a record as .csv attachment for every date, so in this case it will be 3 attachments.
I attach my code here. The problem am having is that it continue to print all date records in one attachment as opposed to creating different attachments.
Many thanks for your advice.
--extract CSV files
--Create File 1
------------------------------------------------------------
set verify off
set feedback off
set newpage none
alter session
set nls_numeric_characters = ',.';
set heading off
set pause off
set serveroutput on size 1000000
set linesize 9999
set pagesize 0
set trim on
set trims on
spool /users/&&1/file1_&&2..csv;
- &&1 --user
-- &&2 --file name
-
DECLARE
CURSOR c_get_dates IS
SELECT distinct delrdd delrdd
from table1;
CURSOR c_extract(p_delrdd in date) IS
SELECT col1,
col2,
col3,
col4,
col5
FROM table1
WHERE trunc(delrdd) = p_delrdd;
BEGIN
FOR date_rec IN c_get_dates LOOP
-------------------------------------------------------------------
--text_io.put_line(file_id,
dbms_output.put_line('hdr1' || ',' || 'hdr2 || ',' || 'hdr3' || ',' ||'hdr4' || ',' ||'hdr5');
FOR r_d IN c_extract(date_rec.delrdd) LOOP
BEGIN
--text_io.put_line(file_id,
dbms_output.put_line(r_d.col1 || ',' || r_d.col2 || ',' ||r_d.col3 || ',' || r_d.col4 || ',' ||r_d.col5);
EXCEPTION
--output data line
WHEN OTHERS THEN
null;
END; --output data line
END LOOP;
--spool off;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/
spool off;
exit;