rows and columns truncating when spooling in sqlplus

Question

*I am spooling some data from oracle database to CSV file using sqlplus, My resulting table has 44 columns and more than 7000 rows but when it spooling to csv it is displaying only 26 columns in excel(Index A to Z) also some rows are truncating. I want all columns should be print in a single line. I have tried increasing 'linesize' but the maximum is 32767 so it's not working also I tried 'wrap on' but the columns after 26th(Z in Excel) index coming in next line.

SET echo off
set embedded on
SET linesize 32767
SET LONG 90000 
SET LONGCHUNKSIZE 90000 
SET wrap off
SET heading off
SET pagesize 1000;
SET feed off;
SET colsep ',' 
SET termout off;
set trimout on;
SET trimspool ON; 
SELECT * FROM ix_web_user;
spool results.csv append;
SET newpage none;
/
spool off

I want all columns should print in a single line and rows should not be truncated.*

score 0 · Answer 1 · answered Jul 04 '19 at 11:10

You can export several csv files with different columns, but one column in each file must be a unique row identifier. For example, the first file contains 22 columns and the second file has 23 columns. A simple script connects lines by identifier and it turns out a final file with 44 columns and a line length of more than 32768.

file1.csv

id,col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12,col13,col14,col15,col16,col17,col18,col19,col20,col21,col22,col23,col24,col25,col26,col27,col28,col29,col30,col31,col32,col33,col34,col35,col36,col37,col38,col39,col40,col41,col42,col43
joe,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,2411111111111111111111111111111111111111111111111111111111111
sam,21,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,451111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111
mary,31,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,30111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111

file2.csv

id,col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12,col13,col14,col15,col16,col17,col18,col19,col20,col21
joe,33311111111111111111111111111111111111111111111111111111111111111111111111111111111111,33311111111111111111111111111111111111111111111111111111111111111111111111111111111111
mary,5551111111111111111111111111111111111111111111111111111111111111111111111111111111111,5551111111111111111111111111111111111111111111111111111111111111111111111111111111111
sam,44411111111111111111111111111111111111111111111111111111111111111111111111111111111111,44411111111111111111111111111111111111111111111111111111111111111111111111111111111111

file merge.ps1

$file1=(import-csv file1.csv -header id,col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,
                                     col11,col12,col13,col14,col15,col16,col17,col18,col19,col20,
                                     col21,col22,col23,col24,col25,col26,col27,col28,col29,col30,
                                     col31,col32,col33,col34,col35,col36,col37,col38,col39,col40,
                                     col41,col42,col43)[1..9999]
$file2=(import-csv file2.csv -header id,col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,
                                     col11,col12,col13,col14,col15,col16,col17,col18,col19,col20,
                                     col21)[1..9999]
$file1|
   %{
      $id=$_.id
      $m=$file2|?{$_.id -eq $id}
      $_.col23=$m.col1
      $_.col24=$m.col2
      $_.col25=$m.col3
      $_.col26=$m.col4
      $_.col27=$m.col5
      $_.col28=$m.col6
      $_.col29=$m.col7
      $_.col30=$m.col8
      $_.col31=$m.col9
      $_.col32=$m.col10
      $_.col33=$m.col11
      $_.col34=$m.col12
      $_.col35=$m.col13
      $_.col36=$m.col14
      $_.col37=$m.col15
      $_.col38=$m.col16
      $_.col39=$m.col17
      $_.col40=$m.col18
      $_.col41=$m.col19
      $_.col42=$m.col20
      $_.col43=$m.col21

    }
$file1|Export-Csv "file3.csv" -NoTypeInformation

output file3.csv

"id","col1","col2","col3","col4","col5","col6","col7","col8","col9","col10","col11","col12","col13","col14","col15","col16","col17","col18","col19","col20","col21","col22","col23","col24","col25","col26","col27","col28","col29","col30","col31","col32","col33","col34","col35","col36","col37","col38","col39","col40","col41","col42","col43"
"joe","1","2","3","4","5","6","7","8","9","10","11","12","13","14","15","16","17","18","19","20","2411111111111111111111111111111111111111111111111111111111111",,"33311111111111111111111111111111111111111111111111111111111111111111111111111111111111","33311111111111111111111111111111111111111111111111111111111111111111111111111111111111",,,,,,,,,,,,,,,,,,,
"sam","21","2","3","4","5","6","7","8","9","10","11","12","13","14","15","16","17","18","19","20","451111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111",,"44411111111111111111111111111111111111111111111111111111111111111111111111111111111111","44411111111111111111111111111111111111111111111111111111111111111111111111111111111111",,,,,,,,,,,,,,,,,,,
"mary","31","2","3","4","5","6","7","8","9","10","11","12","13","14","15","16","17","18","19","20","30111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111",,"5551111111111111111111111111111111111111111111111111111111111111111111111111111111111","5551111111111111111111111111111111111111111111111111111111111111111111111111111111111",,,,,,,,,,,,,,,,,,,

rows and columns truncating when spooling in sqlplus

1 Answers1