sql - spool text file content wrapping unexpectedly -
i generated dynamic spool text , stored them in table. use file spool text create dynamic_spool_script.sql. in dynamic_spool_script.sql, there unexpected wrapping between 50th , 60th characters @ random lines causing sybtax error when run dynamic_spool_script.sql. in original text, none of lines beyond 100 characters. using oracle 11g.
suggestion / appreciated.
edit: wrapped file name, created text variable in plsql. concatenated different text variables , store final text in table column of clob type.
v_file_name varchar2(200);
v_file_name := 'spool "'||v_out_path||'/'||v_dynamic_name||
'_rest_of_file_name_'||to_char(v_batch_date,'yyyymmdd')||'.csv"'||chr(10)||chr(10);
i can copy text table , run in sql plus directly. enough verify there no unexpected new lines or control characters? when spool text table file wrapping problem occurs.
end of edit
******file start*** set linesize 100
set pagesize 0
set long 200000
set echo off
set heading off
set term off
set time off
set feedback off
set timing off
column spool_file format a100
spool &1/dynamic_spool_script.sql
select * dynamic_spool_text;
spool off;
********file end**********
*******dynamic_spool_script.sql start***
some text....
spool "/file_path/file_name_not_more_than_100c #####a lot of space#####
_yyyymmdd.csv"
some text....
*********dynamic_spool_script.sql start***
you try
set trimspool on -- trimspool = on remove trailing spaces spooled output
it increase linesize.
did check there no newlines in records? might have remove them if case.
Comments
Post a Comment