Amol Solanke Amol Solanke - 4 years ago 847
Bash Question

remove blank lines from csv using shell script for oracle

Hi am using following shell script to create csv from oracle database

export ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server
export PATH=$PATH:$ORACLE_HOME/bin
sqlplus -s user1/user1<<!
Spool on
set heading off
SET FEEDBACK OFF
set wrap off
set colsep ,
SET LINESIZE 200
set trims on
Spool /home/ist/username.csv
select 'username','userid' from dual;
select username,userid from users;
Spool off
EXIT
!


I am getting following output

enter image description here

as you can see there is blank line at first and third row
but am expecting file without blank lines.
Any help will be appreciated.

Answer Source

Use the

SET PAGESIZE 0

command to avoid the blank lines. this also suppresses column headings, so you can remove

SET HEADING OFF

The command

SPOOL on

does not make sense because it starts spooling in a file named on.lst. So remove this command, too.

If you want to display the heading with the column name you can try the following settings

set HEADING ON
SET FEEDBACK OFF
set WRAP OFF
set COLSEP ,
SET LINESIZE 32767
set NEWPAGE none
set UNDERLINE OFF
set TRIMSPOOL ON
set TRIMOUT ON
set PAGESIZE 50000

┬┤heading on┬┤ is the default so you must not set it. It enables the display of the column names when a select starts. underline off suppresses the '---' line between column names and data of a select. pages 50000 sets the pagesize to its maximum value (Oracle 11.2). linesize 32767 sets the linesize to its maximum value (Oracle 11.2). newpage none is necessary to suppress this empty line at the beginning of a page that was the primary concern of your posting. All this can be found in the SQL*Plus Command Reference

The termout off parameter suppresses only output created by a scripts that is executed with the @ or @@ command. It dos not suppress out by a command entered in the SQL*plus console. If you use

sqlplus user/passw@connect <<!
...
!

you use the here-document syntax of the shell language which simulates the interactive input. So put your sql commands in a script, e.g. script.sql, and execute

sqlplus user/passw@connect @script.sql

then termout off will suppress terminal output.


Instead of

colsep ,
select username,userid
...

which returns something like

user1                 ,          14
nextuser              ,         236
myuser                ,          11
...

you can use leave the COLSEP unchanged and execute

select username||','||userid
...

to get the following output

user1,14
nextuser,236
myuser,11
...

Maybe this is useful http://dba.stackexchange.com/a/64620/2047

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download