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...@providentinsurance.co.uk>
wrote:
> 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=3Dname || =A0|| 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

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=3Dscript_name_`date +"%d%m%y"`

Then, in SQL*Plus:

SQL>  SPOOL $LOG_FILE

etc...


HTH
-g

On 1 July, 10:31, gazzag <gar...@jamms.org> wrote:
>
> The SPOOL command is a SQL*Plus command, not a SQL one. =A0I would set
> an environment variable at OS level and the shell will expand this
> correctly within your SQL script. =A0For example, on a UNIX system, =A0on=
e
> could do:
>
> $ export LOG_FILE=3Dscript_name_`date +"%d%m%y"`
>
> Then, in SQL*Plus:
>
> SQL> =A0SPOOL $LOG_FILE
>
> etc...
>
> HTH
> -g-

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=3Dscript_%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:
> On 1 July, 10:31, gazzag<gar...@jamms.org>  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-
>
> 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

Positions in the date string may vary for different languages....

Shakespeare

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

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

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.



> 
> 
> My aim is to timestamp the name of my reports
> 
> regards
> Chris B

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...@xs4all.nl> wrote:
>
> Positions in the date string may vary for different languages....
>
> Shakespeare- Hide quoted text -
>

Sure.  And my cat's breath smells of catfood  :P