I am running the following code in SQL developer (oracle) to run the query and export a csv file into a folder. I would like to add the system date to the filename as well. I am using the following code. Although it does the job, it asks the user to input the date in the pop up window. I am looking to get rid of the pop up window and rather have the code use the system date instead. Is there any way i can eliminate the user input window?
Second issue is that this also brings in the SQL code to the output file along with the query results, is there any way to avoid bringing in the SQL as well?
set VERIFY off
set FEEDBACK off
set echo off
set heading off
col date_stp new_value date_stp
Select to_char(sysdate,'yyyymmdd') date_stp from dual;
Spool 'I:\Folder\ExportData&date_stp..csv';
SET sqlformat csv;
Select Customer, ID, etc -- the content of the query
Spool off;
Here's how: in order to avoid displaying the select statement within the spooled file, you need to
set term off
set feed off
but not directly in SQL*Plus (or SQL Developer); you'll have to save the following contents into a .SQL file and run it using @.
So: p.sql
set term off
set feed off
col sd new_value x
select to_char(sysdate,'YYYYMMDD') sd from dual;
spool dept&x..txt
select * From dept;
spool off
Testing:
SQL> @p --> call the .sql file
SQL> $dir *.txt
Volume in drive C is OSDisk
Volume Serial Number is 7635-F892
Directory of C:\Users\lf
12.06.2020. 21:13 494 dept20200612.txt --> file name is OK
1 File(s) 494 bytes
0 Dir(s) 260.040.732.672 bytes free
SQL> $type dept20200612.txt
--> no SELECT statement here
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL>
In order to stop SQL*Plus or SQL Developer asking for value of a substitution variable (that's what &something represents), run set define off.