Re: Is there a way to append spool files?

From: Pete Finnigan <pete_at_petefinnigan.com>
Date: Fri, 28 Nov 2008 10:45:35 +0000
Message-ID: <492FCBCF.2030604@petefinnigan.com>

Thanks Anthony,

I was not aware of that feature of spooling. That should be useful. I have tested in 11gR1:

SQL> spool file.a
SQL> select sysdate from dual;

SYSDATE

---

28-NOV-08

SQL> spool off
SQL> spool file.b
SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME

--------------- ----------------

HOST_NAME

---

VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT

----------------- --------- ------------ --- ---------- -------
---------------
LOGINS     SHU DATABASE_STATUS   INSTANCE_ROLE      ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
              1 orcl
vostok
11.1.0.6.0        28-NOV-08 OPEN         NO           1 STOPPED
ALLOWED    NO  ACTIVE            PRIMARY_INSTANCE   NORMAL    NO


SQL> spool off

SQL> spool file.a append
SQL> select username from dba_users where username='SYSTEM';

USERNAME

---

SYSTEM SQL> spool off
SQL> edit file.a

SQL> host more file.a
SQL> select sysdate from dual;

SYSDATE

---

28-NOV-08 SQL> spool off
SQL> select username from dba_users where username='SYSTEM';

USERNAME

---

SYSTEM SQL> spool off

SQL> host more file.b
SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME

--------------- ----------------

HOST_NAME

---

VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT

----------------- --------- ------------ --- ---------- -------
---------------
LOGINS     SHU DATABASE_STATUS   INSTANCE_ROLE      ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
              1 orcl
vostok
11.1.0.6.0        28-NOV-08 OPEN         NO           1 STOPPED
ALLOWED    NO  ACTIVE            PRIMARY_INSTANCE   NORMAL    NO

SQL> spool off

SQL> and yes it works. BUT does it work in 9i, 10g? I dont have the oledr versions here to test, only 11gR1 today.

Thanks, great answer.

cheers

Pete

Anthony Wilson wrote:
> I believe "spool <filename> APPEND" will do it.
>
> cheers,
> Anthony
>
> On Fri Nov 28 16:25 , Pete Finnigan sent:
>

>> Hi All,
>>
>> I have been asked by someone whether its possible to append to a spool
>> file in sql*plus. i.e. do something like:
>>
>> spool file.a
>> do some sql
>> do some more sql
>> spool off
>>
>> spool file.b
>> do some sql
>> do some sql
>> spool off
>>
>> spool file.a -- but do not lose the original content BUT add to it
>> do some sql
>> do some sql
>> spool off
>>
>> The only thing I can think of is to use os commands to save the file
>> before the second spool and use cat afterwards to build the file but it
>> would be nice to be able to do it in sqlplus only?
>>
>> cheers
>>
>> Pete
>>
>> -- 
>>
>> Pete Finnigan
>> Director
>> PeteFinnigan.com Limited
>>
>> Specialists in database security.
>>
>> If you need help to audit or secure an Oracle database, please ask for
>> details of our courses and consulting services
>>
>> Phone: +44 (0)1904 791188
>> Fax  : +44 (0)1904 791188
>> Mob  : +44 (0)7742 114223
>> email: pete_at_petefinnigan.com
>> site : http://www.petefinnigan.com
>>
>> Registered Office: 9 Beech Grove, Acomb, York, YO26 5LD, United Kingdom
>> Company No       : 4664901
>> VAT No.          : 940 6681 14
>>
>> Please note that this email communication is intended only for the
>> addressee and may contain confidential or privileged information. The
>> contents of this email may be circulated internally within your
>> organisation only and may not be communicated to third parties without
>> the prior written permission of PeteFinnigan.com Limited.  This email is
>> not intended nor should it be taken to create any legal relations,
>> contractual or otherwise.
>>
>> --
>> http://www.freelists.org/webpage/oracle-l
>>
>>
>> )

>
>
>
>

-- 

Pete Finnigan
Director
PeteFinnigan.com Limited

Specialists in database security.

If you need help to audit or secure an Oracle database, please ask for
details of our courses and consulting services

Phone: +44 (0)1904 791188
Fax  : +44 (0)1904 791188
Mob  : +44 (0)7742 114223
email: pete_at_petefinnigan.com
site : http://www.petefinnigan.com

Registered Office: 9 Beech Grove, Acomb, York, YO26 5LD, United Kingdom
Company No       : 4664901
VAT No.          : 940 6681 14

Please note that this email communication is intended only for the
addressee and may contain confidential or privileged information. The
contents of this email may be circulated internally within your
organisation only and may not be communicated to third parties without
the prior written permission of PeteFinnigan.com Limited.  This email is
not intended nor should it be taken to create any legal relations,
contractual or otherwise.

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

Received on Fri Nov 28 2008 - 04:45:35 CST