ORA-19041: Comment data cannot contain two consecutive '-'s

Question

I have a query which results me with below error: ORA-19041: Comment data cannot contain two consecutive '-'s

SQL Query:

SELECT XMLElement("errorlist", fXML.cdata('ORA31011: XML parsing failed 
ORA19213: error occurred in XML processing at lines 1 
LPX00214: CDATA section did not end in "]]>"')) FROM dual;

in this code, fXML.cdata is a function:

  FUNCTION CData(ValueExpr VARCHAR2) RETURN XMLTYPE
  IS BEGIN RETURN obj.fxml.CData(ValueExpr); END;

and obj.fxml.CData is a TYPE:

FINAL STATIC FUNCTION CData(ValueExpr VARCHAR2) RETURN XMLTYPE,

Even when I removed "-" from the passed string, it was giving me with the same error.

But When I remove "]]>", it process normally. Since the fetched data is stored in cdata. e.g.:

<errorlist><![CDATA[ORA-31011: XML parsing failed
ORA-19213: error occurred in XML processing at lines 1
LPX-00214: CDATA section did not end in ]]></errorlist>

But my constraint is to process data containing "]]>" as well.

Answer 1

Without seeing that your type function is doing, this seems to boil down to:

select XMLCData('ORA-31011: XML parsing failed 
ORA19213: error occurred in XML processing at lines 1 
LPX00214: CDATA section did not end in "]]>"')
from dual;

ORA-19041: Comment data cannot contain two consecutive '-'s

The error is somewhat confusing, but given that you don't have consecutive dashes and removing the ]]> part resolves the error, it seems the error code is just being reused without changing the text of the message.

The documentation says:

The following conditions apply to XMLCData:

• The value_expr cannot contain the substring ]]>.

There doesn't seem to any way to escape this; it's part of the specification. You would have to modify the string being passed in so that it doesn't contain that sequence of characters - but you would have to decide what to replace them with (if anything), and whatever is consuming the generated XML/CData would need to be aware of it, or not care.

If a human will end up reading it then replacing it with 'expected value' might be enough; or you could remove the >; or encode it e.g. by changing it to > (which sounds like too much of a hack); or add whitespace to break the character sequence up (which might be confusing); or whatever makes sense for your situation.

You could also split it into two CDATA sections, as suggested here; but that might be something you'd do inside your type function rather than something the caller has to know about. But again it depends how it will be consumed, and if the resulting XML will be understood.