Hello,
Oracle newbie here.
I am getting this error "Character reference "�" is an invalid XML character" for XML data that is printed onto a report. The XML data that is causing the issue is from a VARCHAR2 data column in the database.
I have filtered out the column to a separate backup table I have created and I want to go through the records in order to find where this invalid character is in.
Then I need to write an update statement replacing the invalid character with a valid one.
I am not sure how to do this. Is there a regex I can write with a SELECT statement for this?
I tried below, but they didn't bring up any results:
select *
from tabname
where instr(colname,chr(56256)) > 0;
select *
from tabname
where colname like unistr('%\dbc0%');
Glad if someone one can help.
Thank you!
In XML you can use codes in the form &#NNNN; to represent an entry in the ISO/IEC 10646 character set.
For example, A is the character A, so XML processing can change this code to the letter A:
select xmltype ( '<test>A</test>' ).extract ('test') x
from dual;
X
<test>A</test>
� is an invalid code - it's a digit too long. So I
think you need to search for the exact text �,
not the converting this to ascii/unicode. e.g.:
select *
from tabname
where instr ( colname, '�' ) > 0;