Home » Other » Client Tools » Spool command
Spool command [message #19913] Thu, 18 April 2002 03:19 Go to next message
James Briar
Messages: 72
Registered: January 2002
Location: Morden (South London)
Member
How can i stop a select statement from being written to a spool file. For example :-

sqlplus '/ as sysdba'
SQL> spool test;
SQL> select sysdate from dual;
SYSDATE
---------
18-APR-02

SQL> spool off;
SQL> exit

When i look at the file test.lst it has the entries :-

SQL> select sysdate from dual;
SYSDATE
---------
18-APR-02

SQL> spool off;

I've tried using various options on the set command but had no success (Sun Solaris 8 on unix / Oracle 8i).
I can remove the unwanted entries from the spool file using unix editing commands etc but it would be nice if i could remove these using Oracle.

Thanks.
Re: Spool command [message #19916 is a reply to message #19913] Thu, 18 April 2002 04:53 Go to previous messageGo to next message
Epe
Messages: 99
Registered: March 2002
Member
Hello,

I'm certain that you can get it done by using the set operator to set system variables. I don't know them by heart, but you can see all of them in the Oracle on-line manuals on :
http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a82950/ch8.htm#1001857

Hope you'll find what you're looking for,

epe
Re: Spool command [message #19921 is a reply to message #19913] Thu, 18 April 2002 06:47 Go to previous messageGo to next message
James Briar
Messages: 72
Registered: January 2002
Location: Morden (South London)
Member
I went through all the set options but still could not get rid of the problem where the select statement appeared in the spool file. I've found another way which has solved the problem. If i code a -s option after the sqlplus statement then this gets rid of the unwanted stuff in the spool file (running from root):-

cat > /tmp/test << EOF
spool testfile;
select sysdate from dual;
spool off;
EOF
su - oracle "sqlplus -s '/ as sysdba' < /tmp/test"

testfile.lst just contains the output from the select only.

Thanks very much epe for your reply.
Re: Spool command [message #19923 is a reply to message #19921] Thu, 18 April 2002 07:14 Go to previous messageGo to next message
Mike
Messages: 417
Registered: September 1998
Senior Member
Hi,

Here i got an example which should solve your problem. For more details see:
http://otn.oracle.com/docs/products/oracle9i/doc_library/901_doc/server.901/a88827/ch840.htm#81505

To start the following script execute something like: sqlplus /nolog @c:temptest.sql

Here the script called test.sql:

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

connect scott/tiger@orcl

set heading off
-- ON prints column headings in reports; OFF suppresses column headings

set pagesize 0
-- Sets the number of lines in each page. You can set PAGESIZE to zero to suppress all headings, page breaks,
-- titles, the initial blank line, and other formatting information.

set feedback off
-- Displays the number of records returned by a query when a query selects at least n records.
-- ON or OFF turns this display on or off. Turning feedback ON sets n to 1.
-- Setting feedback to zero is equivalent to turning it OFF

set linesize 200
-- Sets the total number of characters that SQL*Plus displays on one line before beginning a new line

set echo off
-- ON lists the commands; OFF suppresses the listing.

spool c:temptest.txt

select * from emp
/

spool off

exit

-- -------------------------------------
Re: Spool command [message #19925 is a reply to message #19913] Thu, 18 April 2002 08:37 Go to previous messageGo to next message
Grant
Messages: 578
Registered: January 2002
Senior Member
You can't as far as I know. You can turn spool on and off and concatinate the log file with the host command.

SQL> spool t.log
.
.
.
SQL> spool off
SQL> !cat t.log > output.log
SQL> select sysdate from dual;
SQL> spool t.log
.
.
.
SQL> spool off
SQL> !cat t.log >> output.log
SQL> ...
SQL> spool on
Re: Spool command [message #19932 is a reply to message #19921] Thu, 18 April 2002 23:22 Go to previous messageGo to next message
James Briar
Messages: 72
Registered: January 2002
Location: Morden (South London)
Member
Thanks for your Email Mike. I'll keep the information you sent for future reference. I've fixed the problem by using the -s option on the sqlplus command :-

Running from root.

cat > /tmp/test << EOF
spool test2
select sysdate from dual;
spool off;
EOF

su - oracle "sqlplus -s '/ as sysdba' < /tmp/test"

If i look at the file test2.lst then all thats in there is the output produced by the select command.
Re: Spool command [message #19934 is a reply to message #19913] Thu, 18 April 2002 23:39 Go to previous message
James Briar
Messages: 72
Registered: January 2002
Location: Morden (South London)
Member
Thanks for your reply Grant. I'll keep your sql tips and have a play around. I've got around the problem by doing the following (using the -s option on sqlplus) :-

Running from root.

cat > /tmp/test << EOF
spool test2
select sysdate from dual;
spool off;
EOF

su - oracle "sqlplus -s '/ as sysdba' < /tmp/test"

If i look at the file test2.lst then all thats in there is the output produced by the select command.
Previous Topic: FREE TOAD Triggers ?
Next Topic: What does it mean ?
Goto Forum:
  


Current Time: Fri Mar 29 09:43:49 CDT 2024