Replace char control in xmlelement
Context
Some PL / SQL packages built to generate XML throw this error:
ORA-31061: Erreur XDB : special char to escaped char conversion failed.
This error occurs because some of the text selected in xmlelement contains control characters that are not allowed.
Decision
Replace all control characters of each xmlelement with a regular expression:
xmlelement("foo", REGEXP_REPLACE (bar, '[[:cntrl:]]', ''))
Problem with solution
I have 8 packages of about 5k lines each, where almost every line is xmlelement.
Another potential solution
I could write a regex to automatically replace every xmlelement value, but it fails when I have an xmlelement in an xmlelement with a subquery and a subquery, etc.
My question
Is there a smarter way than replacing each xmlelement value one at a time? I have been asked to do all the xmlelements of each package to prevent further errors, but I'm sure there is a better way to do this.
Edit
For example, you can reproduce the error with this query:
select xmlelement("foo", unistr('\0013b')) from dual;
And I'll fix it with this query:
select xmlelement("foo", regexp_replace(unistr('\0013b'), '[[:cntrl:]]', '')) from dual;
source to share
I don't think this is exactly what you want, but it is possible to generate the xml for your request without error using dbms_xmlgen. Here's an example:
declare
xml_output CLOB;
my_context dbms_xmlgen.ctxHandle;
begin
my_context := dbms_xmlgen.newcontext('select unistr (''\0013b'') from dual');
xml_output := dbms_xmlgen.getxml(my_context);
dbms_xmlgen.closecontext(my_context);
dbms_output.put_line(xml_output);
end;
source to share