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:
where instr(colname,chr(56256)) > 0;
where colname like unistr('%\dbc0%');
Glad if someone one can help.
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
� 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.:
where instr ( colname, '�' ) > 0;