0

I'm writing a shell script to spool multiple columns in PL/SQL table to CSV. But all the columns are added to a single column in the CSV file. Can't seem to figure out what the problem is.

FILE="x26837a/test.csv"i
sqlplus -s MyConnection << EOF
set heading on;
set pagesize 1000;
set tab on;
column owner format a10;
column parent_object_name format a20;
column sub_object_name format a30;
column object_type format a40;
column invalid_abbr format a50;
column email_flag format 9999999;
set linesize 300;
SPOOL $FILE;
SELECT * FROM NM_STD_TBL WHERE ROWNUM < 10;
SPOOL OFF;
EXIT;
EOF
Varghese
  • 47
  • 9
  • Have a look at this answer, you might get some clues [link](http://stackoverflow.com/questions/643137/how-do-i-spool-to-a-csv-formatted-file-using-sqlplus) – Sudipta Mondal May 09 '17 at 13:36
  • Can you explain what you mean by "all the columns are added to a single column in the CSV file"? Do you mean there is no separator character? – William Robertson May 09 '17 at 14:21
  • @WilliamRobertson I have six columns in my table- owner, parent_object_name, sub_object_name, object_type, invalid_abbr and email_flag. So the entire row is added to a single column in the csv file instead of 6 columns. – Varghese May 09 '17 at 14:38
  • I checked that link too @Sudipta Mondal. DIdn't help – Varghese May 09 '17 at 14:40
  • Do you mean there is no separator character? What separator character are you using? I don't see one. – William Robertson May 09 '17 at 14:42
  • Though in any case, SQL*Plus always arranges SQL results in tabular columns, not CSV, so if you want to generate delimited output you will have to concatenate the columns manually, taking care of things like date formats and the case where the separator character appears within a column's value, for example if your separator is `,` and the data contains a string with the value `10, Downing Street`. – William Robertson May 09 '17 at 14:44

1 Answers1

1

I believe, if you are using SQL Plus, you will need to do 

set colsep ,

so that it creates a CSV for you.

Sudipta Mondal
  • 2,550
  • 1
  • 19
  • 20