Re: Is there a way to append spool files?

I'm using the following short script since 10 years ago on Unix.

The named pipe is removed automatically when a "spool off" is issued.

The script assumes an existing define vaiable __PID which is filled with the os pid of the sqlplus process itself (not the server process) during startup of sqlplus in my environment. It is just used to get unique filename for the named pipe and may be substituted by any other mechanism to get such a unique name.

kf

--
-- spool-append.sql -
--
--    appends spooling output from SQL*Plus
--    to an already existing file; if it doesn't
--    exist it is created
--    via shell escape first a fifo is created,
--    then a process is started in the backgroud
--    reading from this fifo and appending to
--    the file given as parameter
--    when 'spool off' is entered from SQL*Plus
--    the fifo is closed and the reading process
--    gets an EOF and finishes; after finishing
--    the trap handler removes the fifo
--

define file = &1

define bg = '&'

define fifo = 'spool-append.fifo.&__PID'

-- create the fifo synchronous to avoid problems
!rm -f &fifo ; umask 077 ; mkfifo &fifo

-- start an appending copy asynchronous
!(trap "rm -f &fifo" 0 1 2 3 13 14 15; cat &fifo >> &file) &bg

spool &fifo

undefine fifo
undefine bg
undefine file

===============================




> -----Ursprüngliche Nachricht-----

> Von: "Pete Finnigan" <pete_at_petefinnigan.com>

> Gesendet: 28.11.08 11:49:40

> An: nigel.cl.thomas_at_googlemail.com

> CC: ORACLE-L <oracle-l_at_freelists.org>

> Betreff: Re: Is there a way to append spool files?





> Thanks everyone,

> 

> so from 10gR2 we can use "append" for earlier, 9iR2 or 10gR1 we would

> have to use a pipe or use copy/cat as I first thought. The guy who asked

> me the question is on 9iR2, so whilst the append sounds great, its not

> going to work for him (yet).

> 

> cheers

> 

> Pete

> 

> Nigel Thomas wrote:

> > If you for any reason you need to do this on an earlier version - and you're

> > running a *nix, you can always set up a pipe.

> > 

> > I don't have a handy server to test on (Windows environment on current

> > site), but its something like:

> > 

> > mknod p mypipe

> > cat mypipe >mylogfile &

> > 

> > sqlplus u/p

> > 

> > spool mypipe

> > logged stuff

> > spool off

> > non-logged stuff

> > spool mypipe

> > more logged stuff

> > spool off

> > 

> > exit

> > 

> > The pipeline will stay open - but you can kill the cat once you are done.

> > (No, it's a piece of software in the background, not a cuddly furry animal).

> > 

> > Regards Nigel

> > 

> 

> -- 

> 

> Pete Finnigan

> Director

> PeteFinnigan.com Limited

> 

... deleted ...


--
http://www.freelists.org/webpage/oracle-l