Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2985
  • Last Modified:

Spool file with timestamp

Does anyone know how I can append the date onto the end of a spool file? I need to run a script once a week that produces a file that someone else is going to pick up. They want the date appended onto the end of the file name i.e. filename08May00.
0
efarquha
Asked:
efarquha
1 Solution
 
jkstillCommented:

When creating a spool file, just grab sysdate, store
into a SQL*Plus variable, and attach it to the file name.

Here's how.

col c_my_file_name noprint new_value u_my_file_name

select
   'my_spool_file_'
   || to_char(sysdate,'mm-dd-yyyy-hh24:mi:ss')
   || '.txt'  c_my_file_name
from dual;

spool &u_my_spool_file

select object_name, object_type
from all_objects;

spool off


In this example, the 'COL' command is used to create a variable
'u_my_spool_file' that will be set by the value of the column that
the C_MY_SPOOL_FILE column is use on.

In this case we selected the literal text 'my_spool_file_', the current
date down to the second, and a suffix of '.txt' from dual.

This will create a variable like 'my_spool_file_05-08-2000-10:10:45.txt',
which is then used to hold the output from the example query on
all_objects.


0
 
mgokmanCommented:
Include this at the beginning of your script:

COL dt NEW_VALUE filedate
SELECT TO_CHAR(sysdate, 'DDMonYY') dt FROM dual;
SPOOL filename&&filedate


0
 
efarquhaAuthor Commented:
You both answered my question but jkstill got there first. Thanks
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now