Re: spool command in sql plus

From: gazzag <gareth_at_jamms.org>
Date: Thu, 1 Jul 2010 08:20:36 -0700 (PDT)
Message-ID: <471e8eff-0edf-4897-ad21-c942b3141341_at_j4g2000yqh.googlegroups.com>

On 1 July, 10:31, gazzag <gar..._at_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_at_XE> spool %LOG_FILE%
SYSTEM_at_XE> select sysdate from dual;

SYSDATE

---

01-Jul-10 10:57:16

SYSTEM_at_XE> spool off

C:\>dir *.log

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

C:\>type script_010710.log
SYSTEM_at_XE> select sysdate from dual;

SYSDATE

---

01-Jul-10 10:57:16

SYSTEM_at_XE> spool off

HTH
-g Received on Thu Jul 01 2010 - 10:20:36 CDT