How to remove control chars from clob, prior to creating xmltype
Hi,
I am struggling with what should be really straightforward ...
I have a simple program that takes an xml file stored in a clob column and attempts to convert it into an xml type.
The program is
declare
v_xml xmltype;
v_jobs_clob clob := empty_clob();
begin
dbms_lob.createtemporary(v_jobs_clob,true);
select clob_tmp into v_jobs_clob from clob_tmp where rownum < 2;
v_xml := XMLTYPE.createxml(v_jobs_clob);
end;
/
However my clob clearly contains at least one invalid character as I get this
declare
*
ERROR at line 1:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00217: invalid character 7 (U+0007)
Error at line 450
ORA-06512: at "SYS.XMLTYPE", line 5
ORA-06512: at line 8
What I would like to do is to strip all the control characters from the v_jobs_clob before calling createXML.
I have tried all sorts of things, like replace etc, but to no avail.
Please help...
Ralph