Search code examples
sqloracle-databaseoracle11gclob

Preserve Special Charcters While Migrating Data

I am using the below code to extract data from source legacy database and putting into a CLOB column in target 11g database

RTRIM (  
            XMLAGG (XMLELEMENT (  
                       e,  
                       REPLACE (  
                          REGEXP_REPLACE (  
                             REPLACE (prod_desc, CHR (10), '~~~'),  
                             '[[:cntrl:]]'),  
                          '~~~',  
                          CHR (10)),  
                       CHR (10)) ORDER BY prod_date DESC).EXTRACT ('//text()').getclobval (),  
            CHR (10))

prod_desc column has '<' , '>' '&' and other special characters, these are getting replaced with &lt; &gt; , &amp; respectively.

How can I preserve the above values even I migrate the data from source to target?

Thanks

Solution

  • That's a side effect of Oracle XML tools.

    You can reverse it with:

    SQL> SELECT UTL_I18N.UNESCAPE_REFERENCE(RTRIM(XMLAGG(XMLELEMENT(E,'>')))) AS XML FROM dual;
XML
--------------------------------------------------------------------------------
<E>></E>

    vs

    SQL> SELECT RTRIM(XMLAGG(XMLELEMENT(E,'>'))) AS XML FROM dual;
XML
--------------------------------------------------------------------------------
<E>&gt;</E>