CODEWITHSUNDEEP
regexoracle-databaseplsql
Jean-François Savard
Jean-François Savard

Reputation: 21004

Replace control char in xmlelement

Context

Some PL/SQL package created to generate XML are throwing this error :

ORA-31061: Erreur XDB : special char to escaped char conversion failed.

This error happen because some of the text selected in the xmlelement contains control characters which are not allowed.

Solution

Replace all control chars of each xmlelement with a regex :

xmlelement("foo", REGEXP_REPLACE (bar, '[[:cntrl:]]', ''))

Problem with solution

I have 8 packages of about 5k rows each where almost each row is an xmlelement.

Other potential solution

I tought I could write a regex to replace each xmlelement's value automatically, but it fails when I have xmlelement in xmlelement with subquery and sub-subquery etc.

My Question

Is there a smarter way then replacing each xmlelement's value one by one ? I was asked to do all xmlelement of each packages to prevent further bugs but I'm sure there is a better way of doing this.

Edit

For example, you can reproduce the bug with this query :

select xmlelement("foo", unistr('\0013b')) from dual;

And I would fix it using this query :

select xmlelement("foo", regexp_replace(unistr('\0013b'), '[[:cntrl:]]', '')) from dual;

Upvotes: 5

Views: 4367

Answers (1)

JaseAnderson
JaseAnderson

Reputation: 2925

I don't think this is exactly what you want, but it is possible to generate xml for your query without error using dbms_xmlgen. Here is 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;

Upvotes: 2

Related Questions