Stack Exchange
While looping with dynamic CSV in sqlplus
I'm new at sqlplus and I need a help. I have the select below, and I want to add a While looping to generate a CSV file for each day of the period. 
Can someone help me?
Thanks!
set pagesize 0
set colsep '|'
set echo off
set feedback off
set linesize 1000
set trimspool on
set headsep off

define start_date = '01/01/2004'
define end_date = '02/01/2004'
define start_csv = TO_CHAR(TO_DATE('&start_date.','DD/MM/YYYY'), 'YYYY-MM-DD')
define end_csv = TO_CHAR(TO_DATE('&start_date.','DD/MM/YYYY'), 'YYYY-MM-DD')

spool 'C:\Extracted_&start_csv._&end_csv..csv'

SELECT <MY SELECT>
FROM <MY TABLES>
WHERE DATE BETWEEN '&start_date.' AND '&end_date.'

spool OFF;

SET echo ON
SET feedback ON
SET headsep ON

EDIT 1: Here my first attempt in PL/SQL version:
DECLARE
    -- I pretend to use this variable later, that's why I sent the value to ACTUAL_DATE to run the looping
    START_DATE DATE := '01/01/2004';
    END_DATE DATE := '31/12/2009';
    ACTUAL_DATE DATE := START_DATE;
    DT VARCHAR2(10);

BEGIN
    WHILE ACTUAL_DATE <= END_DATE LOOP
        DT := TO_CHAR(TO_DATE(ACTUAL_DATE, 'DD/MM/YYYY'), 'YYYY-MM-DD');

        --sqlplus only
        --SPOOL 'C:\EXTRACTED_&DT..CSV'

        SELECT <MY SELECT>
        FROM <MY TABLES>
        WHERE DATE = '&ACTUAL_DATE.'

        --SPOOL OFF;
        ACTUAL_DATE := ACTUAL_DATE + 1;
    END LOOP;
END;
/


Looping is a procedural thing. So you need to use PL/SQL instead, which entails a bunch of changes. Or write a shell script.

Thanks for the answer! I already created a version for PL/SQL, but in this way, my problem changed to generate the spool as the SPOOL command is not recognized by PL/SQL

Yes, that's the "bunch of changes" and that's why I suggested a shell script. There is no way to do this in pure SQL / SQL*Plus except by running the script for each day.

And how this will work in shell script? Do you have a link to study?

As an aside, let me point out that your 'dates' aren't dates, but character strings, and comparing them will be  string comparison, not a date comparison.   As such, '01/02/2020' comes before  '12/01/1975'.  Not what you want.  Your "DATE" column should be of type DATE, and all your data handling should be done as DATEs not VARCHARs.

Back on the original question ... where do the two input dates come from and how do they find their way into the script?

@EdStevens sorry, I don't get your question. I have the two variables start_date and end_date filled up with the period I need to extract from database and at WHERE statement I use these values to generate this CSV.
Contextualizing: I need to extract 5 years of data and don't want to babysitting changing the data and running the script every month, so I want extract the data per day (this way if I lost the connection I won't lose much data) and after I'll use all these data into a Data Analytics software.

You could use SQL code to generate the script.  For example, the following SQL statement will generate a SQL*Plus script for spooling DBA_OBJECTS created on a day by day basis to separate .csv files:
with head as (select 0 ord, 'set echo off linesize 200 pagesize 2000 heading off feedback off' cmd from dual)
, main as
(
select rownum ord, cmd 
from 
(
select 'spool c:\temp\csv_'||to_char(trunc(created),'YYYY_MM_DD')||'.csv'
|| chr(13)||chr(10) ||
'select owner || '','' || object_type|| '','' || object_name from dba_objects where trunc(created) = to_date(''' || to_char(trunc(created),'YYYY_MM_DD') || ''', ''YYYY_MM_DD''' || ') order by owner, object_type, object_name ; '
|| chr(13)||chr(10) ||
'spool off ' 
|| chr(13)||chr(10) 
|| chr(13)||chr(10) as cmd
from DBA_objects
where created between sysdate-15 and sysdate
group by trunc(created)
order by trunc(created)
)
)
, script as (select * from head union select * from main)
select cmd from script order by ord
;

