Home » Other » Client Tools » HOW TO GET RID OF FIRST EMPTY LINE of the spooled file (Oracle10 / Linux and Wiindows)
| HOW TO GET RID OF FIRST EMPTY LINE of the spooled file [message #509655] |
Mon, 30 May 2011 23:49  |
newbie01.oracle
Messages: 14
Registered: December 2009
|
Junior Member |
|
|
Hi,
Does anyone know how to get rid of the first EMPTY line generated by SQL*Plus when spool'ing to a file.
I tried using set pagesize 0 and while that get rid of the empty blank line, it also disabled the heading ... 
Currently using sed as a work around. But unfortunately, can't use sed on Windows?
set pagesize 200
set heading on
set trimspool on
set lines 200
set verify off
set echo off
set feedback off
col name format a30
col dtp format a5
col aq_ha_notifications format a20
spool dba_services.out
select service_id, name, goal, dtp, aq_ha_notifications
from dba_services
;
As mentioned, using pagesize 0 remove the blank line but I also lost the heading.
|
|
|
|
|
|
| Re: HOW TO GET RID OF FIRST EMPTY LINE of the spooled file [message #509775 is a reply to message #509658] |
Tue, 31 May 2011 22:01  |
newbie01.oracle
Messages: 14
Registered: December 2009
|
Junior Member |
|
|
Michel Cadot wrote on Tue, 31 May 2011 01:17I tried your script exactly as you posted it (just adding "spoof off", I have no blank first line.
Add "show all" at the end of the script, execute it and post the result.
Regards
Michel
Hi Michel,
Thanks for your response.
As per your instruction. Output below with the show all and using spool off
SERVICE_ID GOAL DTP AQ_HA_NOTIFICATIONS
---------- ------------ ----- --------------------
1 NONE N NO
2 NONE N NO
3 N NO
4 N NO
5 NONE N NO
6 N NO
appinfo is OFF and set to "SQL*Plus"
arraysize 15
autocommit OFF
autoprint OFF
autorecovery OFF
autotrace OFF
blockterminator "." (hex 2e)
btitle OFF and is the first few characters of the next SELECT statement
cmdsep OFF
colsep " "
compatibility version NATIVE
concat "." (hex 2e)
copycommit 0
COPYTYPECHECK is ON
define "&" (hex 26)
describe DEPTH 1 LINENUM OFF INDENT ON
echo OFF
editfile "afiedt.buf"
embedded OFF
escape OFF
escchar OFF
feedback OFF
flagger OFF
flush ON
heading ON
headsep "|" (hex 7c)
instance "local"
linesize 200
lno 9
loboffset 1
logsource ""
long 80
longchunksize 80
markup HTML OFF HEAD "<style type='text/css'>
body {font:10pt Arial,Helvetica,sans-serif; color:black; background:White;}
p {font:10pt Arial,Helvetica,sans-serif; color:black; background:White;}
table,tr,td {font:10pt Arial,Helvetica,sans-serif; color:Black; background:#f7f7e7; padding:0px 0px 0px 0px; margin:0px 0px 0px 0px;}
th {font:bold 10pt Arial,Helvetica,sans-serif; color:#336699; background:#cccc99; padding:0px 0px 0px 0px;}
h1 {font:16pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; border-bottom:1px solid #cccc99; margin-top:0pt; margin-bottom:0pt; padding:0px 0px 0px 0px;-
}
h2 {font:bold 10pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; margin-top:4pt; margin-bottom:0pt;}
a {font:9pt Arial,Helvetica,sans-serif; color:#663300; background:#ffffff; margin-top:0pt; margin-bottom:0pt; vertical-align:top;}</style>
<title>SQL*Plus Report</title>
" BODY "" TABLE "border='1' width='90%' align='center' summary='Script output'" SPOOL OFF ENTMAP ON PREFORMAT OFF
newpage 1
null ""
numformat ""
numwidth 10
pagesize 200
PAUSE is OFF
pno 1
recsep WRAP
recsepchar " " (hex 20)
release 1002000500
repfooter OFF and is NULL
repheader OFF and is NULL
serveroutput OFF
shiftinout INVISIBLE
showmode OFF
spool OFF
sqlblanklines OFF
sqlcase MIXED
sqlcode 0
sqlcontinue "> "
sqlnumber ON
sqlpluscompatibility 10.2.0
sqlprefix "#" (hex 23)
sqlprompt "SQL> "
sqlterminator ";" (hex 3b)
suffix "sql"
tab ON
termout ON
timing OFF
trimout ON
trimspool ON
ttitle OFF and is the first few characters of the next SELECT statement
underline "-" (hex 2d)
USER is "SYS"
verify OFF
wrap : lines will be wrapped
BTW, the output above is from running sqlplus as below from within a UNIX script.
sqlplus -S /nolog <<SQLEND
conn / as sysdba
set pagesize 200
set heading on
set trimspool on
set lines 200
set verify off
set echo off
set feedback off
col name format a30
col dtp format a5
col aq_ha_notifications format a20
spool dba_services.out
select service_id, goal, dtp, aq_ha_notifications
from dba_services
;
spool off
show all
SQLEND
From the OS:
[oracle@server013 ]$ cat dba_services.out
SERVICE_ID GOAL DTP AQ_HA_NOTIFICATIONS
---------- ------------ ----- --------------------
1 NONE N NO
2 NONE N NO
3 N NO
4 N NO
5 NONE N NO
6 N NO
[oracle@server013 ]$
BTW, if I run SQL*Plus manually, the spool file also contains the SQL statements, I thought set echo off is supposed to suppress these? Also tried including set term off and I get the SQL statements in the spool file as well.
[oracle@server013 ]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.5.0 - Production on Wed Jun 1 14:54:59 2011
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
SQL> conn / as sysdba
Connected.
SQL> set pagesize 200
SQL> set heading on
SQL> set trimspool on
SQL> set lines 200
SQL> set verify off
SQL> set echo off
SQL> set feedback off
SQL>
SQL> col name format a30
SQL> col dtp format a5
SQL> col aq_ha_notifications format a20
SQL>
SQL> spool dba_services.out
select service_id, goal, dtp, aq_ha_notifications
SQL> 2 from dba_services
3 ;
SERVICE_ID GOAL DTP AQ_HA_NOTIFICATIONS
---------- ------------ ----- --------------------
1 NONE N NO
2 NONE N NO
3 N NO
4 N NO
5 NONE N NO
6 N NO
SQL> spool off
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, Data Mining and Real Application Testing options
[oracle@server013 ]$ cat dba_services.out
SQL> select service_id, goal, dtp, aq_ha_notifications
2 from dba_services
3 ;
SERVICE_ID GOAL DTP AQ_HA_NOTIFICATIONS
---------- ------------ ----- --------------------
1 NONE N NO
2 NONE N NO
3 N NO
4 N NO
5 NONE N NO
6 N NO
SQL> spool off
[oracle@server013 ]$
Thanks again for your response.
[Updated on: Wed, 01 June 2011 01:25] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: HOW TO GET RID OF FIRST EMPTY LINE of the spooled file [message #509790 is a reply to message #509775] |
Wed, 01 June 2011 01:24 |
 |
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Using "<<..." is different than using "@script.sql".
In the first case, you are in interactive mode.
In the second one (the one I used), you are in batch mode.
SQL*Plus behaviour is not the same one in both cases.
Use a SQL script if you want to completly keep hand on SQL*Plus behaviour.
Regards
Michel
|
|
|
|
Goto Forum:
Current Time: Sun Dec 22 05:17:05 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
