Forum Stats

  • 3,722,204 Users
  • 2,244,252 Discussions
  • 7,849,703 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

how to eliminate blank line in sqlplus output

vxwo0owxv
vxwo0owxv Member Posts: 237
edited December 2011 in SQL & PL/SQL
#!/bin/ksh

#Hi I am doing a data extract using sqlplus but the output has a blankline in it.
#How I ask sqlplus to not putting that blankline. Thanks so much for the kind help

mydate=`date '+%m%d%Y'`

file_ext=".txt"
file_hdr="testlinespace"
OUTFILE=`echo $file_hdr$mydate$file_ext`
echo $OUTFILE

mytime_start=`date '+%Y%m%d_%H%M%S'`
echo $mytime_start

sqlplus -silent /nolog << EOF
CONNECT scott/[email protected]

set echo off
set colsep ' '
set pagesize 0
set trimspool on
set headsep off
set linesize 100
set pagesize 50
set heading off
set feedback off
set timing off
set time off
set termout off
set headsep off

spool $OUTFILE


WITH a1 as
(
SELECT object_id,object_name from all_objects
)
SELECT * FROM a1 where rownum < 1001;



spool off

select to_char(sysdate,'YYYYMMDD_HH24MISS') from dual;

exit
$

Answers

  • User_Z52BC
    User_Z52BC Member Posts: 200 Blue Ribbon
    Use the below.
    #!/bin/ksh
    
    #Hi I am doing a data extract using sqlplus but the output has a blankline in it.
    #How I ask sqlplus to not putting that blankline. Thanks so much for the kind help
    
    mydate=`date '+%m%d%Y'`
    
    file_ext=&quot;.txt&quot;
    file_hdr=&quot;testlinespace&quot;
    OUTFILE=`echo $file_hdr$mydate$file_ext`
    echo $OUTFILE
    
    mytime_start=`date '+%Y%m%d_%H%M%S'`
    echo $mytime_start
    
    sqlplus -silent /nolog &lt;&lt; EOF
    CONNECT scott/[email protected]
    
    set echo off
    set colsep ' '
    set pagesize 0
    set trimspool on
    set headsep off
    set linesize 100
    set heading off
    set feedback off
    set timing off
    set time off
    set termout off
    set headsep off
    
    spool $OUTFILE
    
    WITH a1 as
    (
    SELECT object_id,object_name from all_objects
    )
    SELECT * FROM a1 where rownum &lt; 1001;
    
    
    
    spool off
    
    select to_char(sysdate,'YYYYMMDD_HH24MISS') from dual;
    
    exit
This discussion has been closed.