spool command in sql plus
in [Oracle]
|
Prev: CREATE OR REPLACE FUNCTION problem
Next: SQL alteration
From: dba cjb on 30 Jun 2010 11:22 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
From: Kay Kanekowski on 30 Jun 2010 12:39 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
From: gazzag on 1 Jul 2010 05:31 On 30 June, 16:22, dba cjb <chris.br...(a)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=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 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
From: gazzag on 1 Jul 2010 11:20 On 1 July, 10:31, gazzag <gar...(a)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(a)XE> spool %LOG_FILE% SYSTEM(a)XE> select sysdate from dual; SYSDATE ------------------ 01-Jul-10 10:57:16 SYSTEM(a)XE> spool off C:\>dir *.log 01/07/2010 10:57 430 script_010710.log C:\>type script_010710.log SYSTEM(a)XE> select sysdate from dual; SYSDATE ------------------ 01-Jul-10 10:57:16 SYSTEM(a)XE> spool off HTH -g
From: Shakespeare on 1 Jul 2010 17:49
Op 1-7-2010 17:20, gazzag schreef: > On 1 July, 10:31, gazzag<gar...(a)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(a)XE> spool %LOG_FILE% > SYSTEM(a)XE> select sysdate from dual; > > SYSDATE > ------------------ > 01-Jul-10 10:57:16 > > SYSTEM(a)XE> spool off > > C:\>dir *.log > > 01/07/2010 10:57 430 script_010710.log > > C:\>type script_010710.log > SYSTEM(a)XE> select sysdate from dual; > > SYSDATE > > ------------------ > > 01-Jul-10 10:57:16 > > > SYSTEM(a)XE> spool off > > HTH > -g Positions in the date string may vary for different languages.... Shakespeare |