I am preparing a script that will run every morning at 1am on my ora db.
However i have a spool statement for which i want the text file to be created with the naming format of August-16-2003. In order words whatever day that the script is executed, the spooled file must reflect that based upon the name. Does anyone here have any suggestion?
This is a script that is run every night at 1 of my client's site.
column whichdate new_value whichday noprint
select to_char(trunc(sysdate),'dd-mon-yy') whichdate from dual;
set verify on
set echo on
set termout on
set linesize 120
set space 0
set pagesize 0
set feedback off
set verify off
set termout off
set heading off
set serveroutput off
set echo off
set show off
set trim off
set concat on
set linesize 60
column DAY_NAME new_value file_day noprint
select trunc(SYSDATE) DAY_NAME FROM DUAL;
host del c:\1\x_file*.txt
to_char(REPORTING_DATE,'YYYYMMDD')||','||LOCATION_ IDENTIFIER||','||PART_ID||','||UNIT_OF_MEASURE||', '||QTY_ON_HAND||','
where reporting_date = upper('&WHICHDAY') and qty_on_hand > 0
order by location_identifier, catalog_id;
I'm trying to run a sql script every month, with the output file containing the date e.g. file_02-29-2004.bas. I saw that you have done something similar to this. I tried your script but I was getting an error, that this line is invalid "column whichdate new_value whichday noprint".
You can use the following:
1. First use the set command to set the NLS_DATE_FORMAT that you want to use.
2. column whichdate new_value whichday noprint
3. select trunc(sysdate) whichdate from dual;
4. spool c:\Filename_&&whichday..bas;
5. [Insert your script here]
6. spool off;