Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Using Spool to output data to file

269153May 3 2002
I am trying to write some sql scripts to do spooling of data to files automatically on the 1st day of each month. I use the spool command to output to text file but have problems setting the filename to tag with the date stamp.

Script 1 : to get current year and month and then call script 2 to output to file
==================================================================================
set linesize 800
set pagesize 999
set heading off
Remark Get current year and month from database
Remark
variable v_yyyymm number;
declare
begin
Select to_number(to_char(to_date(to_char(sysdate,'DD/MM/YYYY'))-1,'YYYYMM')) INTO :v_yyyymm From dual;
end;
/
print v_yyyymm
@script2.sql :v_yyyymm
exit

script2.sql : spool to file
============================
spool Monthly\Audit_Trail_&1..txt
SELECT * FROM AUDIT_TRAIL WHERE TO_NUMBER(TO_CHAR(DATESTAMP,'YYYYMM')) = &1;
spool off

Upon execution in Windows environment, it says that unable to create spool file. It seem
that i can't use a bind variable in the Spool Command. Any alternative ways of achieving that ??? I need the file name to be taged with the date stamp ?
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 3 2002
Added on May 3 2002
4 comments
2,712 views