Re: SqlPlus: dynamic name for a spool file

From: Kay Kanekowski <kay.kanekowski_at_web.de>
Date: Thu, 19 Apr 2012 23:02:47 -0700 (PDT)
Message-ID: <a669a55b-a5e0-4c6a-a888-ff0b6087c5f6_at_d20g2000vbh.googlegroups.com>

On 19 Apr., 18:24, Mladen Gogala <gogala.mla..._at_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 Received on Fri Apr 20 2012 - 01:02:47 CDT