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

"Maikku" <kangasm...@netti.fi> a écrit dans le message de news: 2144735.1356.1334755734839.JavaMail.geo-discussion-forums@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.

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.

If you change the default numformat - or change the variable format -
it'll work:

SQL> set numformat 'TM'
SQL> define
.
.
.
DEFINE _O_RELEASE      = "1102000300" (CHAR)
DEFINE TESTNUM         =
1
(NUMBER)
DEFINE _RC             = "0" (CHAR)
SQL> spool lgf_&testnum
SQL> spool off
SQL> !ls -lat|head
total 1752
-rw-r--r--    1 oracle   dba              15 Apr 19 09:00 lgf_1.lst

Ie, I simply changed the default way NUMBER is displayed to the
minimal string ('TM'). Look it up in the doco on COLUMN in the SQLPlus
guide.  Works on both 10 and 11g, AFAIK.

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.

But if you use the define variables only with CHAR type it will
generate the desired filenames.

column mydt new_value mynum
select to_number(to_char(sysdate,'J')) as mydt from dual;

select &mynum+1 from dual;

-- to define mynum as number doesn't help, it can't calculate anything
def mynum
prompt #&mynum#
prompt &mynum+1

column mydt new_value mynum
select to_char(sysdate,'J') as mydt from dual;

-- but within a select it can calculate with the char-variable
select &mynum+1 from dual;

def mynum
prompt #&mynum#

spool d:\temp\&mynum._testfile.txt
prompt this is output-file with leading number &mynum

spool d:\temp\testfile_&mynum..txt
prompt this is output-file with number &mynum at the end

spool off

ACCEPT TESTNUMBER number PROMPT "GIVE A NUMBER> "
prompt #&TESTNUMBER#

-- and now a default CHAR variable
ACCEPT TESTNUMBER PROMPT "GIVE A NUMBER> "
prompt #&TESTNUMBER#

SPOOL &TESTNUMBER._LOGNAME.LOG
prompt this is output-file with leading number &TESTNUMBER

SPOOL LOGNAME_&TESTNUMBER..LOG
prompt this is output-file with number &TESTNUMBER at the end

spool off