Re: spool command in sql plus

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Thu, 01 Jul 2010 23:49:07 +0200
Message-ID: <4c2d0d58$0$22944$e4fe514c_at_news.xs4all.nl>

Op 1-7-2010 17:20, gazzag schreef:
> 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

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

Shakespeare Received on Thu Jul 01 2010 - 16:49:07 CDT