Sample Header Ad - 728x90

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

3 votes
4 answers
85422 views
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: https://dba.stackexchange.com/questions/54149/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:** 1. Headings are not in one line, "set wrap off" does not help, because it truncates data. 2. 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. 3. 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!
Asked by Christoph Holzapfel (31 rep)
Dec 4, 2014, 01:43 PM
Last activity: Nov 24, 2023, 07:26 AM