Oracle spool issues

Question

currently i use below code to spool csv file but i have find some line length more than 4000 characters , so it will no propey display

set echo off
set feedback off
set heading on
set linesize 32000
set pagesize 0
set termout off
set trim off
set trimspool on
set array 100
set underline off
set wrap off
set flush off
set verify off
set embedded on

select cloumnA||','||ColumnB ||','||ColumnC ..... from my_tab_name ；

so i modify my code but the result is not my want.

set echo off
set feedback off
set heading on
set linesize 32000
set pagesize 0
set termout off
set trim off
set trimspool on
set array 100
set underline off
set wrap off
set flush off
set verify off
set embedded on
select cloumnA,ColumnB ,ColumnC ..... from my_tab_name ；

the result 1:
A_value,B_value,C_value..... (the result is correct ,but only can show 4000 characters)

the result 2:
A_value
B_value
C_value
.... 

Anyone can help,thanks a lot!

Answer 1

When you concatenate columns (cloumnA||','||ColumnB ||','||ColumnC), the result is a VARCHAR2, which can't be more than 4000 characters (in typical circumstances).

See this similar question for some different options.

You could use COLSEP:

set colsep ','
spool myfile.csv
select cloumnA,ColumnB,ColumnC ..... from my_tab_name;

Which will get you something like

A_value           ,B_value      ,C_value
A_value           ,B_value      ,C_value
A_value           ,B_value      ,C_value

Which most people don't like, but it doesn't have a 4000-character line limit.

If you're using SQL Developer, you can use the easy /*csv*/ hint

select /*csv*/ cloumnA,ColumnB,ColumnC ..... from my_tab_name;

And if it's possible for you to use the newer SQLcl instead of SQL*Plus, you can use set sqlformat:

set sqlformat csv
spool myfile.csv
select cloumnA,ColumnB,ColumnC ..... from my_tab_name;