Hi am using following shell script to create csv from oracle database
sqlplus -s user1/user1<<!
set heading off
SET FEEDBACK OFF
set wrap off
set colsep ,
SET LINESIZE 200
set trims on
select 'username','userid' from dual;
select username,userid from users;
SET PAGESIZE 0
command to avoid the blank lines. this also suppresses column headings, so you can remove
SET HEADING OFF
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
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
termout off will suppress terminal output.
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