I would like to pass a variable into a spool command so that a
different report is created depending on the time of the create

The pseudo code would be

@1=name || || to_char ( date etc )

spool @1

Does anyone know how I can achieve this using pl/sql & or substition
variables


My aim is to timestamp the name of my reports

regards
Chris B

Hi Chris,

try something like this
def spoolfile

col currentsysdate NEW_VALUE spool_file_timestamp

select '<your_spoolfile_prefix>' || to_char(sysdate,
'yyyy_dd_mm_hh24miss' ) currentsysdate
from dual;

def spoolfile

hth
Kay

On 30 June, 16:22, dba cjb <chris.br... (AT) providentinsurance (DOT) co.uk>
wrote:
The SPOOL command is a SQL*Plus command, not a SQL one. I would set
an environment variable at OS level and the shell will expand this
correctly within your SQL script. For example, on a UNIX system, one
could do:

$ export LOG_FILE=script_name_`date +"%d%m%y"`

Then, in SQL*Plus:

SQL> SPOOL $LOG_FILE

etc...


HTH
-g

On 1 July, 10:31, gazzag <gar... (AT) jamms (DOT) org> wrote:
Additionally, in a Windows environment, you can do the following:

Windows now provides an enhancement to the DATE functionality which is
handy for time-stamping log files.

For example:

C:\>echo %DATE%
01/07/2010

Windows provides the following functionality to reformat the output:

echo %DATE:~<start_position> , <number_of_characters>% (Note:
<start_position> starts counting from zero)

E.g.

To display just the year portion of the output:

C:\>echo %DATE:~6,4%
2010

To display the month:

C:\>echo %DATE:~3,2%
07

And the day of the month:

C:\>echo %DATE:~0,2%
01

So, to generate a log file with the format <script_name>_ddmmyy.log,
set an environment variable as follows:

C:\>set LOG_FILE=script_%date:~0,2%%date:~3,2%%date:~8,2%. log

C:\>echo %LOG_FILE%
script_010710.log

This environment variable will be expanded correctly within a SQL*Plus
session:

SYSTEM@XE> spool %LOG_FILE%
SYSTEM@XE> select sysdate from dual;

SYSDATE
------------------
01-Jul-10 10:57:16

SYSTEM@XE> spool off

C:\>dir *.log

01/07/2010 10:57 430 script_010710.log

C:\>type script_010710.log
SYSTEM@XE> select sysdate from dual;

SYSDATE

------------------

01-Jul-10 10:57:16


SYSTEM@XE> spool off

HTH
-g

Op 1-7-2010 17:20, gazzag schreef:
Positions in the date string may vary for different languages....

Shakespeare

On Wed, 30 Jun 2010 08:22:48 -0700, dba cjb wrote:

Yes, of course! You should start your script with the following lines:

#!/usr/bin/perl -w
use strict;
use DBI;

That will enable you to do almost any kind of report you want, titles
included. Oracle's sqlplus is not a reporting tool and it shouldn't be
used as such.



Scripting languages are used for the reporting purposes. Perl or Python
will both do the trick. For reporting purposes, I prefer Perl because it
already contains a small report writer built into the language itself and
there is an excellent template toolkit which is ideal for generating XML
and various other report types.
Python, on the other hand, is easier to learn and is gaining momentum
these days, mainly because Python is a truly object oriented language,
while Perl object model is a byproduct of the Blair witch project and
will make your hair stand and turn grey.
If you have Java duhvelopers around you and want to have someone who
could maintain your scripts while you are on vacation (that is sometimes
a concern, too) then Python is the language for you. When presented with
the choice, damagement usually selects Python.



--
http://mgogala.byethost5.com

On 1 July, 22:49, Shakespeare <what... (AT) xs4all (DOT) nl> wrote:
Sure. And my cat's breath smells of catfood :P