SqlPlus: dynamic name for a spool file

[Maikku]

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

[Michel Cadot]

"Maikku" <kanga...@netti.fi> a écrit dans le message de news: 2144735.1356.1334755734839.JavaMail.geo-discussion-forums@vbuo5...

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

[Mladen Gogala]

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.


--
http://mgogala.byethost5.com

[Noons]

The problem is you are using the default formatting of NUMBER in a
character string (the name of the file). That won't work because it
left-pads the number with blanks.

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.

Mladen alluded to it when he asked you to do a "DEF TESTNUMBER": there
you can see the default formatting which is right-justified numwidth.

[Mladen Gogala]

On Wed, 18 Apr 2012 16:07:25 -0700, Noons wrote:

> Mladen alluded to it when he asked you to do a "DEF TESTNUMBER": there
> you can see the default formatting which is right-justified numwidth.

That was Michael, not me. I don't do such tricks in SQL*Plus.



--
http://mgogala.byethost5.com

[Noons]

On Apr 19, 11:40 am, Mladen Gogala <gogala.mla...@gmail.com> wrote:
> On Wed, 18 Apr 2012 16:07:25 -0700, Noons wrote:
> > Mladen alluded to it when he asked you to do a "DEF TESTNUMBER": there
> > you can see the default formatting which is right-justified numwidth.
>
> That was Michael, not me. I don't do such tricks in SQL*Plus.

Quite right. My apologies to Michael.

[Kay Kanekowski]

> 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

[Mladen Gogala]

On Thu, 19 Apr 2012 18:00:15 +0200, Kay Kanekowski wrote:

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

SQL*Plus is not a scripting tool, at least not a good one, not even a
usable one. There used to be a good reporting tool called RPT/RPF, but
the programmer got called to better things and the tool was canceled.
When I need scripting tool, I use the available scripting languages.
Better, faster and less convoluted.



--
http://mgogala.byethost5.com

[Kay Kanekowski]

On 19 Apr., 18:24, Mladen Gogala <gogala.mla...@gmail.com> wrote:
> SQL*Plus is not a scripting tool, at least not a good one, not even a
> usable one. There used to be a good reporting tool called RPT/RPF, but

Hi Mladen,
i think no one will say that sql*plus is a scripting language.

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

regards
Kay