Re: Unique Filename in SQLPlus Spool

From: <rzx2122_at_mcvax2.d48.lilly.com>
Date: 19 Nov 93 08:21:08 EST
Message-ID: <1993Nov19.082108.1_at_mcvax2.d48.lilly.com>

Jeroen Hermans, Nikhef-H, Amsterdam (the Netherlands) writes:

> select name, adres
> from person
> where name like upper('&NAME%');
>
> When I start this script the output is send to the file 'namelist'.
> When I start the script again the file 'namelist' will be deleted
> and exchanged by a new list. Can I make a unique name 'namelist'
> by putting a timestamp behind it or another way.
> Now I have to make a list leave SQLPlus move the file to another
> name start SQLPlus again and start the script again.
> Is there another sollution within SQLPlus??????

Sorry I can't send this directly Jeroen. I'm sending this from a POST-ONLY account on Internet.

I'd be glad to present this solution at the European Oracle Users Group meeting in the Netherlands. Does anyone have a plane ticket to send to me?

Tom

----------------------------- cut here -----------------------------

/*
DYNASPOOL.SQL This is an example of creating dynamically named SPOOL files.

Since SQL*Plus does not allow runtime creation of spool filenames, a SQL*Plus script is used to generate the dynamic filename inside a second SQL*Plus script. The second SQL*Plus script is started and generates and captures the results in the new spool file.

NOTE: The last line of the second SQL*Plus script is for VAX/VMS

      and purges all previous versions of the second SQL*Plus script.

Released to the public domain without restriction.

Author: Thomas L. Harleman (Paradigm Consulting, Inc.) Date: Nov. 19, 1993

*/
SET ECHO OFF
SET VERIFY OFF
SET TERMOUT OFF
SET PAGESIZE 0
COL LINE1 FOLD_AFTER
SPOOL TRYME.SQL
SELECT

       'SET PAGESIZE 60'                                   LINE1,
       'SET ECHO ON'                                       LINE1,
       'SET VERIFY ON'                                     LINE1,
       'SET TERMOUT ON'                                    LINE1,
       'UNDEFINE NAME'                                     LINE1,
       'ACCEPT NAME'                                       LINE1,
       'spool namelist'||TO_CHAR(SYSDATE,'SSMIHH')||'.LIS' LINE1,
       'select  NAME, DEPARTMENT'                          LINE1,
       'from    person'                                    LINE1,
       'where   name like upper(''&&NAME%'')'              LINE1,
       '/'                                                 LINE1,
       'SPOOL OFF'                                         LINE1,
       'HOST PURGE/NOCONFIRM TRYME.SQL'                    LINE1

FROM DUAL
/
SPOOL OFF
START TRYME
/*

---

                                            chicago ,--------+  detroit
Tom Harleman                                        |        |
INOUG Committee Member                              |Indiana |
Paradigm Consulting, Inc.                 st.       | Oracle |
11080 Willowmere Drive                    louis     |  Users |
Indianapolis, IN  46280                             |   Group|
USA                                                 |        |
                                                    |      _,+cincinnati
INTERNET:   72072.2122_at_compuserve.com               /   _,'
Compuserve: 72072,2122                             /_,-'louisville
                                                   `

EXECUTE_TRIGGER('DISCLAIMER');

---

*/ Received on Fri Nov 19 1993 - 14:21:08 CET