0

I generated a dynamic spool text and stored them in a table. I then use File A to spool the text to create dynamic_spool_script.sql. In dynamic_spool_script.sql, there is unexpected wrapping between the 50th and 60th characters at random lines causing sybtax error when i run dynamic_spool_script.sql. In my original text, none of the lines are beyond 100 characters. I am using oracle 11g.

Any suggestion / help will be greatly appreciated.


Edit: For the wrapped file name, i created the text with a variable in plsql. I then concatenated the different text variables and store the final text in the 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 the text from the table and run in sql plus directly. Is that enough to verify that there are no unexpected new lines or control characters? just that when I spool the text from table to a file the wrapping problem occurs.


End of Edit

******File A 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 * from dynamic_spool_text;
spool off;
********File A 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***

user393624
  • 11
  • 3

1 Answers1

2

You could try

SET TRIMSPOOL ON 
-- TRIMSPOOL = ON will remove trailing spaces from spooled output

It also could help to increase the linesize.

Did you check that there are no newlines in the records? You might have to remove them if this is the case.

Devolus
  • 21,661
  • 13
  • 66
  • 113