Spool CSV file using BASH Script | Errors with Output in generated CSV

Question

I need to spool the output of tables from db to CSV file. I checked and referred to How do I spool to a CSV formatted file using SQLPLUS?

But it's not working successfully. What I am getting is only the first 3 columns being separated by , and not the remaining. Others are getting separated by newline.

EDIT : Schema Details of Table being Spooled.

WWID               VARCHAR2 (5 Byte) 
TIMELOG_DATE       DATE 
TOTAL_HOURS        NUMBER (10,2) 
ACTIVITY           VARCHAR2 (100 Byte) 
SUBACTIVITY        VARCHAR2 (100 Byte) 
REF_PROJECT_ID     VARCHAR2 (30 Byte) 
REF_PROJECT_DESC   VARCHAR2 (250 Byte)
WORK_REQUEST_ID    VARCHAR2 (30 Byte)
EMP_COMMENTS       VARCHAR2 (100 Byte)
APPROVER_COMMENTS  VARCHAR2 (100 Byte)

Script:

echo "\n>>> ******Data Processing Started at `date '+%d-%m-%Y %T %Z'`******" >>${LOGFILE}

sqlplus $fims_user/$fims_pwd << EOF
set serveroutput on;
set colsep ,     ;-- separate columns with a comma
set pagesize 0   ;-- No header rows
set trimspool on ;-- remove trailing blanks
set headsep off  ;-- this may be useful...depends on your headings.

spool /home/fimsctl/datafiles/outbound/timelog/timelog_file_`date +%y%m%d`.csv

select * from FIMS_OWNER.TIMELOG_EXTRACT_OUTBOUND_T;

commit;
spool off
exit
EOF

echo "\n>>> ******Data Load Completed at `date '+%d-%m-%Y %T %Z'`******" >>${LOGFILE}

echo "End of the script">> ${LOGFILE}

And Output in CSV i am getting is:

SQL> select * from FIMS_OWNER.TIMELOG_EXTRACT_OUTBOUND_T;
iv315,29-DEC-14,          8
DUMMY

REF01
New Ref Project of type CPRM
66
NA


iv315,30-DEC-14,          8
DUMMY

REF01
New Ref Project of type CPRM
66
NA


iv315,31-DEC-14,          8
DUMMY

REF01
New Ref Project of type CPRM
66
NA

That is values are then separated by newline(when seen in wordpad)

Thanks

Answer 1

The defaut linesize is 80 characters, so by default columns will wrap onto new lines when that length will be exceeded. Your 100-byte columns will cause that behaviour. You can add SQL*Plus commands to change that:

-- any number at least as large as the longest possible output
set linesize 1024
set wrap off

select * is generally frowned up as the output can change unexpectedly if the table definition changes - i.e. a column is added - or if the table has columns in different orders in different environments. (Which arguably shouldn't happen with source control, and generally doesn't matter as long as you don't use *. If you list the columns you want explicitly, it isn't much extra work to concatenate them with manually-added separators, e.g:

select wwid
  ||','|| to_char(timelog_date, 'DD-MON-YY') -- or another format
  ||','|| total_hours
  ||','|| activity
  ||','|| subactivity
  ... -- etc
from FIMS_OWNER.TIMELOG_EXTRACT_OUTBOUND_T;

That will remove extra whitespace that is currently going to pad all the CSV rows to the same length, and reduce the output file size. The output is then a single column, so the colsep setting isn't relevant any more.