Re: SqlPlus: dynamic name for a spool file

From: Kay Kanekowski <kay.kanekowski_at_web.de>
Date: Thu, 19 Apr 2012 18:00:15 +0200
Message-ID: <jmpcqi$gl9$1_at_news.albasani.net>

Am 18.04.2012 21:46, schrieb Mladen Gogala:
> On Wed, 18 Apr 2012 17:06:53 +0200, Michel Cadot wrote:
>
>> "Maikku"<kangasmaki_at_netti.fi> a écrit dans le message de news:
>> 2144735.1356.1334755734839.JavaMail.geo-discussion-forums_at_vbuo5...
>> | Why is this working:
>> | ACCEPT TESTNUMER NUMBER PROMPT "GIVE A NUMBER> "
>> |
>> | SPOOL&TESTNUMBER._LOGNAME.LOG |
>> | but this isn't:
>> | SPOOL LOGNAME_&TESTNUMBER..LOG |
>> | It seems, that I can use a number in logname if it's in the beginning
>> of the logname, but otherwise not. If I use char it works both ways.
>> |
>> | I'm using SqlPlus 10.2.0.2.
>> |
>> | TIA |
>> | Maija-Leena
>>
>> Just execute "DEF TESTNUMBER" and you will understand.
>> If you don't then execute "PROMPT LOGNAME_&TESTNUMBER..LOG" and I hope
>> this time you will.
>>
>> Regards Michel
>
>
> Sorry, no dynamic spool names:
>
> SQL> column mydt new_value mynum
> SQL> select to_number(to_char(sysdate,'J')) as mydt from dual;
>
> MYDT
> ----------
> 2456036
>
> Elapsed: 00:00:00.02
> SQL> select&&mynum+1 from dual;
> old 1: select&&mynum+1 from dual
> new 1: select 2456036+1 from dual
>
> 2456036+1
> ----------
> 2456037
>
> Elapsed: 00:00:00.01
> SQL> spool /tmp/test_&&mynum
> SP2-0768: Illegal SPOOL command
> Usage: SPOOL {<file> | OFF | OUT }
> where<file> is file_name[.ext] [CRE[ATE]|REP[LACE]|APP[END]]
> SQL>
>
> Oracle doesn't allow that. Of course, there are other scripting languages
> that can be used to that end.
>
>

Hi Mladen,
why not ?
I use dynamic file every day from my sql-scripts.

A little example with sysdate:

set termout off
set feedback off

col char_date new_value file_date noprint

• this or whatever you need in your filename. select to_char(sysdate, 'YYYY_MM_DD_hh24miss') char_date from dual ;

set termout on
set feedback on

• check if there a leading or trailing spaces def file_time

spool c:\temp\test_sysdate_&file_date..log

Ok, it's written without testing, no database at home.

An issue may the formatting of the variables with leading spaces.

regards
Kay Received on Thu Apr 19 2012 - 11:00:15 CDT