Getting newline after every Spooled record in CSV using the bellow code having data with embeded newlines: so can not replace it tried putting it under quotes it corrected the data new lines but getting a new line after spooling in to CSV in unix box but if I m directy spooling into csv via toad it gives the right data not sure why SET SQLBLANKLINES OFF not working .
set serveroutput on size 1000000
set pagesize 0
set feedback off
set serveroutput on size 1000000
set pagesize 0
set feedback off
set echo off
set verify off
set lines 32767
set trimspool on
SET TRIMSPOOL ON
set space 0
SET TAB off
SET NEWPAGE NONE
SET SQLBLANKLINES OFF
whenever sqlerror exit sql.sqlcode;
define tbl = &sourcetabelname;
define dlm = 44;
define fn = filename.csv;
spool &fn
DECLARE
v_extract_view varchar2(30) := upper('&tbl');
v_header_yn varchar2(1) := upper('Y');
v_delimiter_asc1 varchar2(3);
v_delimiter_asc2 varchar2(3);
v_cnt number(3) := 0;
v_sql varchar2(32000);
v_header varchar2(32000);
cursor c_hdr is
select column_name
from all_tab_columns
where table_name = v_extract_view
and column_name <> 'rec_no' and column_name<>'campaign_staging_id'
order by COLUMN_ID;
BEGIN
v_header := '';
v_sql := '';
--v_delimiter_asc1 is the passed in delimiter surrounded by single quotes
execute immediate 'select chr(39)||chr(&dlm.)||chr(39) from dual' into v_delimiter_asc1;
--v_delimiter_asc2 is the passed in delimiter surrounded by double quotes
execute immediate 'select chr(32)||chr(&dlm.)||chr(32) from dual' into v_delimiter_asc2;
if &dlm.=44 then
for rec in c_hdr
loop
if v_cnt = 0
then
v_header := rec.column_name;
v_sql := 'create or replace view camp_insert_lead_dump as select ''"''||REPLACE( REPLACE ("' || rec.column_name || '" ,''
'',chr(10) ),chr(&dlm.),chr(32))||''"''';
else
v_header := v_header || v_delimiter_asc2 || rec.column_name;
v_sql := v_sql || '||' || v_delimiter_asc1 || '||' || ' ''"''||REPLACE( REPLACE ("' || rec.column_name || '" ,''
'',chr(10) ),chr(&dlm.),chr(32))||''"''';
end if;
v_cnt := v_cnt + 1;
end loop;
--v_header := v_header || '"';
v_sql := v_sql || ' data from ' || v_extract_view ;
else
for rec in c_hdr
loop
if v_cnt = 0
then
v_header := '"' || rec.column_name;
v_sql := 'create or replace view camp_insert_lead_dump as select ''"''|| REPLACE( REPLACE ("' || rec.column_name || '" ,''
'',chr(10) ),chr(&dlm.),chr(32))||''"''';
else
v_header := v_header || v_delimiter_asc2 || rec.column_name;
v_sql := v_sql || '||' || v_delimiter_asc1 || '||' || ' ''"''|| REPLACE( REPLACE ("' || rec.column_name || '" ,''
'',chr(10) ),chr(&dlm.),chr(32))||''"''';
end if;
v_cnt := v_cnt + 1;
end loop;
v_header := v_header || '"';
v_sql := v_sql ||'||' || ' data from ' || v_extract_view ;
end if;
dbms_output.put_line(v_header);
--dbms_output.put_line(v_sql);
execute immediate v_sql;
END;
/
---select /*csv*/ replace(DATA,'
',chr(10) ) DATA from camp_insert_lead_dump;
select TRIM(DATA) DATA from camp_insert_lead_dump;
spool off
quit;
can not use replace on the file in unix box because it is replacing all the blank lines in the column data .