5 Replies Latest reply on May 25, 2012 1:28 PM by user11260693

    xmlcdata

    user11260693
      We are trying to create an XMLTYPE using XMLCDATA. I have shortened the statement for simplicity:

      SELECT XMLELEMENT("user",XMLCDATA('Here is a test string')) FROM dual;

      According to what I've read, this is the correct syntax, yet when the pl/sql is compiled, it throws the following error:

      ORA-00932: inconsistent datatypes: expected - got CHAR

      I tried executing it in SQL Developer and I get the same error.

      We are running 10g, which is supposed to support the XMLCDATA function. Does anyone know what's wrong?
        • 1. Re: xmlcdata
          odie_63
          According to what I've read, this is the correct syntax
          Yes, it's the correct syntax.
          yet when the pl/sql is compiled, it throws the following error:

          ORA-00932: inconsistent datatypes: expected - got CHAR
          So, what's really causing the error? Just running this simple SQL query or compiling some PL/SQL using this query?
          If you actually use a SELECT INTO, what's the datatype of the target variable?

          Give a reproducible test case.
          • 2. Re: xmlcdata
            user11260693
            i'm not sure what you mean by a test case. The statement fails in SQL Developer and when being compiled in pl/sql. The pl/sql statement is selecting into an XMLTYPE. When the XMLCDATA function is removed from the statement, the procedure compiles without error. Same for SQL Developer. If I remove the XMLCDATA function, the statement executes.
            • 3. Re: xmlcdata
              odie_63
              We are running 10g
              I overlooked that piece of information.

              Always give your complete version when posting, ie the four digits from :
              SELECT * FROM v$version;
              XMLCDATA is available starting with release 10.2.
              • 4. Re: xmlcdata
                sb92075
                http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions237.htm#SQLRF06205

                XMLCData generates a CDATA section by evaluating value_expr. The value_expr must resolve to a string. The value returned by the function takes the following form:

                <![CDATA[string]]>
                If the resulting value is not a valid XML CDATA section, then the function returns an error.The following conditions apply to XMLCData:

                The value_expr cannot contain the substring ]]>.
                If value_expr evaluates to null, then the function returns null.
                • 5. Re: xmlcdata
                  user11260693
                  Thanks for the info. Unfortunately we're running 10.1, so I suppose we'll have to write a function as a workaround.