How to format sql-plus-spool-file to *.csv with all columns in one line and row-content with linebreaks as one field?

I am not a professional, sorry. But perhaps you can help me anyway ...

I have a table with 27 columns and within the rows data with linebreaks. When I try to export selected rows with sql-plus spool, I have several problems. I tried the advices given here: How to make sqlplus output appear in one line?, but they don't help me. I'll explain below.

First the configuration of the SQL plus query:

set term off    
set echo off    
set underline off    
set colsep ';'    
set linesize 32767    
set pages 10000    
set trimspool on    
set trimout on    
set feedback off    
set heading on    
set newpage 0    
set headsep off    
;set wrap off    
set termout off    
set long 20000    

spool D:\tmp_Datenaustausch\Export-141204.csv    

SELECT MEDIENNR, ISBN, ISBN2, ISBNSUCH, KATKARTE1, BUCHKARTE1, SIGNATUR1, KATKARTE2,
   BUCHKARTE2, SIGNATUR2, KATKARTE3, BUCHKARTE3, SIGNATUR3, MAB2, FEHLERCODE, FARBE, 
   ANZSYKKARTEN, KKSTATUS, BKSTATUS, MABSTATUS, SESTATUS, FARBSTATUS, ASSTATUS, 
   REZENSION, REZENSENT, STICHWORTE, UPDDATE FROM Bekartungsdaten WHERE upddate like 
   TO_DATE('2014-12-01', 'yyyy-mm-dd');    

spool off;    
exit;    
/

My problems:

• Headings are not in one line, "set wrap off" does not help, because it truncates data.

• Some fields contain text with linebreaks, sql+ puts the lines of these fields in seperate fields in the csv. I need them in one field with linebreaks like stored in the database.

• How do I get quotationmarks at the beginning and end of each field/cell in the csv?

I would be very glad, if some could help me!

1) If you want the headings in one line, you have to use a UNION with a select from dual. Example:

select '"'||'col1'||'","'||'"col2"'||'","'||'"col3"'
  from dual
union
select '"'||col1||'","'||col2||'","'||col3||'"'
  from table;

2) This one is tricky. It helps to know what ascii character your line breaks actually are. Depending on how they were written, generally (but not always) they will be chr(10) or a combination of chr(10)chr(13). You can use the REPLACE function in your query to replace those with '', which should get rid of them.

3) If you want quotes, see my example #1 above.

HTH.

StackExchange Database Administrators Q#84303, answer score: 3