Home » SQL & PL/SQL » 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 Go to next message
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 ... Sad

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 #509658 is a reply to message #509655] Tue, 31 May 2011 01:17 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I 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
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 Go to previous messageGo to next message
newbie01.oracle
Messages: 14
Registered: December 2009
Junior Member
Michel Cadot wrote on Tue, 31 May 2011 01:17
I 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 #509777 is a reply to message #509775] Tue, 31 May 2011 22:22 Go to previous messageGo to next message
BlackSwan
Messages: 25793
Registered: January 2009
Location: SoCal
Senior Member
>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.

when you did it manually you did not include the "-S" command line switch that place sqlplus into SILENT mode
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 Go to previous message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: Using Unix environment variables
Next Topic: Pl sql editor
Goto Forum:
  


Current Time: Tue Jan 16 20:11:30 CST 2018

Total time taken to generate the page: 0.01445 seconds