| Spool command [message #19913] |
Thu, 18 April 2002 03:19  |
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 #19921 is a reply to message #19913] |
Thu, 18 April 2002 06:47  |
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 |
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 |
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 |
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 |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
