CLOB data goes to next line while spooling
944969Jun 20 2012 — edited Jul 12 2012Hi,
I have a table having 2 CLOB columns. I store XML there. I am trying to spool the data of the table in an excel. When the XML size is OK, I can spool it well but when it is large, i get a new line after half of the second XML data. Something like this:
Desired:
COLUMN1 COLUMN2 COLUMN3 COLUMN4
<XML 1>ABBBBBBBB CCCCCC JJJJ<XML 1> <XML 2> DDDDDDDDDDDDD FFFFFF <XML 2> 23-June-2012 MARRY
Problem:
COLUMN1 COLUMN2 COLUMN3 COLUMN4
<XML 1>ABBBBBBBB CCCCCC JJJJ<XML 1> <XML 2> DDDDDDDDDDDDD
FFFFFF <XML 2> 23-June-2012 MARRY
My query is below:
ORACLE_HOME=/opt/oracle/product/current
export ORACLE_HOME
$ORACLE_HOME/bin/sqlplus -S user/pwd@db > temp.xls 2>&1 <<!
set linesize 32767 long 90000 longchunksize 50000 trimout on trimspool on
set heading off pages 0 trimspool on feedback off echo off termout off
spool Report.xls
select replace (xml1,'<?xml version="1.0" encoding="UTF-8"?>' , null)||chr(9)||replace (xml2,'<?xml version="1.0" encoding="UTF-8"?>' , null)||chr(9)||request_id||chr(9)|| name||chr(9)|| CREATED_BY||chr(9)||CREATION_DATE||chr(9)||LAST_UPDATED_BY||chr(9)||LAST_UPDATE_DATE
from table_name where request_id = 331;
spool off
exit;
!
Please help!