Re: How2 Generate current date in spool-filename in SQL+

From: Martin Burbridge <pobox002_at_bebub.com>
Date: 15 Jan 2003 08:29:30 -0800
Message-ID: <45a06b65.0301150829.2d878188@posting.google.com>

mlouwes_at_yahoo.com wrote in message news:<41434413.0301150351.1640818e_at_posting.google.com>...
> I use sql+ and I want the results of my query in a spool file.
> I need to run this query everyday from the Scheduler and want a new
> file everyday, so I need to generate a unique filename in sql+
> preferable with the current date in the name of the file.
> How can I do this ??

You can use column new_value and a column alias to build a formatted string and store in a substitution variable. Here's an example -

SQL> col output new_value output noprint SQL> select 'myfile_' || to_char(sysdate,'yyyymmdd') || '.txt' output 2 from dual;

SQL> spool &output
SQL> select 'here''s the file' from dual;

'HERE''STHEFILE

here's the file

SQL> spool off
SQL> host type myfile_20030115.txt
SQL> select 'here''s the file' from dual;

'HERE''STHEFILE

here's the file

SQL> spool off

SQL> See the SQLPlus docs at http://tahiti.oracle.com for further info.

Martin Received on Wed Jan 15 2003 - 10:29:30 CST