Re: Unique Filename in SQLPlus Spool

In <753640071snz_at_otbbb.demon.co.uk> Marc_at_otbbb.demon.co.uk (Marc Bowden) writes:
>> I was wondering if it is possible to make a unique filename in a spool command
>> Is there another sollution within SQLPlus??????
 

>I can't find onbe ... certainly you can do: spool &
 

>for eg: spool &
> select null from dual;
> spool off
 

>when you run that and type: bert
>for the param name the spool
>will go to bert.lst.
 

>If you have unix, I would tend to think of a shell script based around
>passing the variable: $$ (which I *think* is a csh
>thing (?)) as the parameter.

$$ is for Bourne and Korn shells an represents the current process number (I had to look that up). So even if you could get at it from sqlplus it would not guarnatee a unique filename.

>If you find a way *purely* in sqlplus I'd like to know too!! :-)

Try this one if you are willing to accept a slight chance that the filename will not be unique:

  column xx new_value yy

  select to_char(sysdate,'hhmiss') xx
  from dual
  /   

  spool &yy

  select some_columns
  from   some_tables
  where  some_condition

  spool off

This will create a file named something like 101913.lst in the current directory.

If you want to guarantee a unique filename then use a sequence.

  create sequence report_name_seq start with 250000   /

You only need to do this once. The start with is to avoid any filename clashes with other reports that use the time to name their spool files. Then change the first select in the above sql to read:

  select to_char(report_name_seq) xx
  from dual
  /

The first one worked with Oracle 6.0.30 on DEC Ultrix but I've used a similar technique on later versions. I only thought of the second after leaving work but it shouldnt be a problem.

Hope this helps

Regards

Ian

-- 
-------------------------------------------------------------------------
Ian Dixon                      |  Email : idixon_at_syntaxis.infocom.co.uk |
Independent Oracle Consultant  |  Tel   : +44 (0)734 478092             |
Reading, England               |                                        |