4 Replies Latest reply: Jan 17, 2013 11:51 PM by user_sneha RSS

    ORA-31061: XDB error: special char to escaped char conversion failed

    user_sneha
      Hi,

      When i run the below anonymous block, it runs into "ORA-31061: XDB error: special char to escaped char conversion failed" error:

      declare
      xml_string1 XMLTYPE;
      v_err_number VARCHAR2(4000);
      v_err_msg VARCHAR2(4000);
      BEGIN

      SELECT XMLELEMENT("rf",
      XMLFOREST('R' "ACN",
      FNAME "FNM",
      FTYPE "FTY",
      FCITY "FCT",
      FSTATE "FST"))
      INTO xml_string1
      FROM FRQ_FLR_DETAILS
      WHERE FID = 101;

      EXCEPTION
      WHEN OTHERS THEN
      v_err_number := SQLCODE;
      v_err_msg := SQLERRM;
      DBMS_OUTPUT.PUT_LINE('ERROR AT LINE NUMBER: ' ||
      DBMS_UTILITY.FORMAT_ERROR_BACKTRACE());
      DBMS_OUTPUT.PUT_LINE('ERROR CODE: ' ||v_err_number||' AND ERROR MSG: '|| v_err_msg);
      END;


      But. when I run just the select statement(given below), it works fine
      SELECT XMLELEMENT("rf",
      XMLFOREST('R' "ACN",
      FNAME "FNM",
      FTYPE "FTY",
      FCITY "FCT",
      FSTATE "FST"))
      INTO xml_string1
      FROM FRQ_FLR_DETAILS
      WHERE FID = 101;

      Database details:
      Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
      PL/SQL Release 11.2.0.3.0 - Production
      "CORE     11.2.0.3.0     Production"
      TNS for Linux: Version 11.2.0.3.0 - Production
      NLSRTL Version 11.2.0.3.0 - Production

      I am not very sure, but I read it somewhere that this might be because 11g doesn't handle control characters in XML, so one can replace the control characters to avoid the error. However, I have several such select statements that fetch data from various tables across our database, and it is practically impossible to find out all such data with control characters.

      Can anyone please help?

      Edited by: user13491035(sneha) on Jan 14, 2013 9:42 PM
        • 1. Re: ORA-31061: XDB error: special char to escaped char conversion failed
          Manik
          We need to see the data of that table (post the insert query and DDL of the table FRQ_FLR_DETAILS
          ) for which you are trying to convert into xml... provide us the data of fid=101.

          Also just simulated your code with dual table and it worked. So definetely issue with your data...
          DECLARE
             xml_string1    XMLTYPE;
             v_err_number   VARCHAR2 (4000);
             v_err_msg      VARCHAR2 (4000);
          BEGIN
             SELECT XMLELEMENT ("Emp",
                                XMLFOREST ('R' "Ac",
                                           'FNAME' "FNM",
                                           'FTYPE' "FTY",
                                           'FCITY' "FCT",
                                           'FSTATE' "FST"))
                       "Emp Element"
               INTO xml_string1
               FROM DUAL;
          EXCEPTION
             WHEN OTHERS THEN
                v_err_number := SQLCODE;
                v_err_msg := SQLERRM;
                DBMS_OUTPUT.PUT_LINE (
                   'ERROR AT LINE NUMBER: ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE ());
                DBMS_OUTPUT.PUT_LINE (
                   'ERROR CODE: ' || v_err_number || ' AND ERROR MSG: ' || v_err_msg);
          END;
          • 2. Re: ORA-31061: XDB error: special char to escaped char conversion failed
            user_sneha
            Thank you for the reply. Data exactly is my problem here. Above this, this is just a sample block that I posted. In real time, there are several such statements that fetch data from various tables, and almost each one is running into the aforesaid error. My question is, finding out the data that contains control characters being impossible in my case, as data keeps on changing everyday and a valid data might become invalid the other time it is referenced, can I somehow format the xml function used, so that it handles the control characters. Something like regexp_replace maybe.

            Hope i could explain the problem. Please let me know, if there is any other info that I can provide. Thank You once again!

            Edited by: user13491035(sneha) on Jan 14, 2013 10:27 PM
            • 3. Re: ORA-31061: XDB error: special char to escaped char conversion failed
              odie_63
              I am not very sure, but I read it somewhere that this might be because 11g doesn't handle control characters in XML.
              No, 11g or the database in general has nothing to do with it.
              The XML language itself doesn't support those characters, as explained in the spec : http://www.w3.org/TR/REC-xml/#charsets

              See this post for some options :
              {message:id=4076437}
              • 4. Re: ORA-31061: XDB error: special char to escaped char conversion failed
                user_sneha
                Those links were really helpful. Thank you.