CODEWITHSUNDEEP
sqloracle-database
Roger
Roger

Reputation: 95

formatting csv file using sqlplus in shell script

I am using sqlplus in shell script to connect to DB and spool .csv file.

code is:-

sqlplus -s user/pass@db <<EOF> /dev/null 2>&1
SET COLSEP ','
SET FEEDBACK OFF
SET TRIMSPOOL ON
SET UNDERLINE OFF
SET LINESIZE 10000
SET PAGESIZE 10000

spool customer.csv 

select * from customer where cust_name='john';
spool off
EOF

output:- enter image description here

i get my header trim for few Colum's like 1st, 2nd 4th and 7th.

expected output:- enter image description here

Upvotes: 0

Views: 1002

Answers (1)

Justin Cave
Justin Cave

Reputation: 231651

You'd need to tell SQL*Plus how wide you want the columns to be. Assuming I've counted the length of each header correctly

column sys_creation_date format a17
column sys_update_date   format a15
column dl_service        format a10
column eff_date_time     format a13

You may be happier switching, though, to SQLcl since that makes generating CSV files easier. Just

select /*csv*/ * from emp

or

set sqlformat csv
select * from emp

Upvotes: 2

Related Questions