Re: SqlPlus: dynamic name for a spool file

From: Noons <wizofoz2k_at_gmail.com>
Date: Wed, 18 Apr 2012 16:07:25 -0700 (PDT)
Message-ID: <daf35fbb-b29f-493a-91f7-6a79a026e19c_at_ot8g2000pbb.googlegroups.com>

On Apr 18, 11:28 pm, Maikku <kangasm..._at_netti.fi> wrote:
> 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.
>

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. Received on Wed Apr 18 2012 - 18:07:25 CDT