ORA-31061: XDB error: special char to escaped char conversion failed
while executing a procedure the following error thrown
exec BU_OFFER.GET_UTILIZATION_REPORT_DATA1('bbc_00205196','','ALL','',:rc)
ORA-31061: XDB error: special char to escaped char conversion failed.
ORA-06512: at "FMS_ADMIN.BU_OFFER", line 175
ORA-06512: at line 1
ORA-06512: at "FMS_ADMIN.BU_OFFER", line 175
ORA-06512: at line 1
The query which is used for generating XML is
SELECT
A.ADDRESS -- this field containing special characters
FROM UTILIZATION_REPORT_DATA A,BU_CONNECTIONS B
WHERE B.PHONE_NO(+)=A.FTTH_PHONE_NO
AND A.OLT_IP IN(SELECT DISTINCT OLT_IP FROM OLT_STAFF_MAPPING WHERE BBC_USERNAME = 'bbc_00205196');
A.ADDRESS -- this field containing special characters
FROM UTILIZATION_REPORT_DATA A,BU_CONNECTIONS B
WHERE B.PHONE_NO(+)=A.FTTH_PHONE_NO
AND A.OLT_IP IN(SELECT DISTINCT OLT_IP FROM OLT_STAFF_MAPPING WHERE BBC_USERNAME = 'bbc_00205196');
changed the above query using REGEXP_REPLACE function
SELECT
REGEXP_REPLACE(A.ADDRESS, ''[^[:print:]]'', '''') ADDRESS
FROM UTILIZATION_REPORT_DATA A,BU_CONNECTIONS B
WHERE B.PHONE_NO(+)=A.FTTH_PHONE_NO
AND A.OLT_IP IN(SELECT DISTINCT OLT_IP FROM OLT_STAFF_MAPPING WHERE BBC_USERNAME = 'bbc_00205196');
REGEXP_REPLACE(A.ADDRESS, ''[^[:print:]]'', '''') ADDRESS
FROM UTILIZATION_REPORT_DATA A,BU_CONNECTIONS B
WHERE B.PHONE_NO(+)=A.FTTH_PHONE_NO
AND A.OLT_IP IN(SELECT DISTINCT OLT_IP FROM OLT_STAFF_MAPPING WHERE BBC_USERNAME = 'bbc_00205196');