Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

REPLACE not working - ASCII character - XML element

Jeevanantham VJun 7 2022

Hi,
I would like to persist the special character as it as in table column - CLOB.

When i execute below query, only & is replaced with & value, Other values are not replacing with actual special character.

Can you please assist.
Query:
SELECT
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
RTRIM(XMLAGG(XMLELEMENT(E,'<>''"&',' \n ','Test&').EXTRACT('//text()')).GETCLOBVAL(),' \n '),
CHR(60)||'lt;',CHR(60)),
CHR(39)||'apos;',CHR(39)),
CHR(38)||'amp;',CHR(38)),
CHR(62)||'gt;',CHR(62)),
CHR(34)||'quot;',CHR(34)) as repl from dual;

Query Output:
<>'"& \n Test&

Expected Output:
<>''"& \n Test&
This post has been answered by odie_63 on Jun 7 2022
Jump to Answer

Comments

Post Details

Added on Jun 7 2022
#plsql, #sql, #xml
3 comments
854 views
1 mention