If you want the file on your *client* machine, then you need to use a tool, for example, sqlcl, which will let you spool the CSV results easily, for example:
C:\oracle\sqlcl\bin>sql.exe scott/tiger@db122
SQLcl: Release 18.1 Production on Sun Apr 15 11:52:40 2018
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Last Successful login time: Sun Apr 15 2018 11:52:41 +08:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> select /*csv*/ * from scott.emp;
"EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
7369,"SMITH","CLERK",7902,17/DEC/80,800,,20
7499,"ALLEN","SALESMAN",7698,20/FEB/81,1600,300,30
7521,"WARD","SALESMAN",7698,22/FEB/81,1250,500,30
7566,"JONES","MANAGER",7839,02/APR/81,2975,,20
7654,"MARTIN","SALESMAN",7698,28/SEP/81,1250,1400,30
7698,"BLAKE","MANAGER",7839,01/MAY/81,2850,,30
7782,"CLARK","MANAGER",7839,09/JUN/81,2450,,10
7788,"SCOTT","ANALYST",7566,09/DEC/82,3000,,20
and the schedule that using a local facility (eg Task Scheduler on Windows).
If you want to generate a CSV file on the *server*, you can do the same as above, or you can use PLSQL and the scheduler to do the whole thing from the database, eg
SQL> create or replace
2 procedure csv(p_query varchar2) is
3
4 l_theCursor integer default dbms_sql.open_cursor;
5 l_columnValue varchar2(4000);
6 l_status integer;
7 l_descTbl dbms_sql.desc_tab;
8 l_colCnt number;
9 n number := 0;
10
11 f utl_file.file_type;
12 l_line varchar2(32767);
13
14 begin
15 f := utl_file.fopen('TMP','mycsv.dat','W',32767);
16
17 dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
18 dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );
19
20 for i in 1 .. l_colCnt loop
21 dbms_sql.define_column(l_theCursor, i, l_columnValue, 4000);
22 l_line := l_line || l_descTbl(i).col_name||',';
23 end loop;
24 utl_file.put_line(f,l_line);
25 l_status := dbms_sql.execute(l_theCursor);
26
27 while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
28 l_line := null;
29 for i in 1 .. l_colCnt loop
30 dbms_sql.column_value( l_theCursor, i, l_columnValue );
31 l_line := l_line || l_columnValue ||',';
32 end loop;
33 utl_file.put_line(f,l_line);
34 end loop;
35 utl_file.fclose(f);
36 end;
37 /
Procedure CSV compiled
SQL>
SQL> exec csv(p_query=>q'{select * from scott.emp}');
PL/SQL procedure successfully completed.
SQL> BEGIN
2 dbms_scheduler.create_job (
3 job_name => 'UNLOAD_CSV',
4 job_type => 'PLSQL_BLOCK',
5 job_action => q'{csv(p_query=>'select * from scott.emp');}',
6 start_date => SYSTIMESTAMP,
7 repeat_interval => 'freq=daily; byhour=10; byminute=0; bysecond=0;',
8 enabled => TRUE);
9 END;
10 /
PL/SQL procedure successfully completed.