ORA-03113 and Getting CLOB Value from XMLTYPE

 

The Quest Code Tester for Oracle development team is making more and more extensive use of the XMLTYPE datatype in the Oracle database. XML documents are a perfect fit for several requirements of Code Tester, in particular storing values for nested structures like collections of records, which in turn contain collections as fields.

After adding an XMLTYPE column to our table, we then updated our table API packages to support retrieval of the XML document as a CLOB. You do this by invoking the GetClobVal method of the XMLTYPE object type.

Here's an example of the way we did this:

DECLARE
   l_clob   CLOB;
BEGIN
   SELECT qu_attributes.dp_value.getclobval ()
     INTO l_clob
     FROM qu_attributes
    WHERE parent_guid = 'a' AND usage_type = 'a';
END; 

Sadly, when I run this block of code on 11.2 (and we've seen in on 10g as well), I get the following error:

ORA-03113: end-of-file on communication channel

That is one nasty error! And, as any of you who have encountered ORA-03113 must know, this error does not identify a problem in our code. It reflects a bug of some sort in Oracle's code.

So the question that comes to mind immediately is: what's the workaround?

It looks like the problem has to do with executing this method inside the query. I moved the call to the method into its own function:

CREATE OR REPLACE FUNCTION gcv (xmltype_in IN XMLTYPE)
   RETURN CLOB
IS
BEGIN
   RETURN xmltype_in.getclobval ();
END;
/

and then I called the function in my query:

DECLARE
   l_clob   CLOB;
BEGIN
   SELECT gcv (qu_attributes.dp_value)
     INTO l_clob
     FROM qu_attributes
    WHERE parent_guid = 'a' AND usage_type = 'a';
END;

Then the ORA-03113 error disappeared. So that's the workaround, folks.

If you get an ORA-03113 in a query that works with XMLType columns, check to see if GetClobVal is being called and if so, move it out to a separate function and call the function from within the query.