A simple workaround for bug 13477790 – XML/SQL query hangs after Oracle 11

IF you will run the following query on an oracle 11 db

select xmlelement("root",
(xmlelement("sub_root",
xmlforest('value' as "some_tag",
(select xmlagg(xmlelement("test",
xmlforest('code_value' as
"Code")))
from dual) as "SOME_AGG_CODES"))))

from dual

you might find that the sq/xml query simply hangs for many minutes after the upgrade. Many times, the following message will be displayed ” end-of-file on communication channel” . In other cases, it is possible that your session will be disconnected or might not be able to send a break message because you client will simply freeze.

This is even more problematic if this query was written on earlier versions of oracle like oracle 9. The same query will work without a problem on Oracle 9 or oracle and this can be very challenging if you encounter the problem after the actual upgrade to oracle 11.

Oracle identified this problem as a known issue bug #13477790 and released a patch with the same number. It appears that this patch solves this issue but applying a patch is not always easy on large production systems.
Another option for solving this issue is rewriting the the query using an additional xmlelement instead of the xmlforest but this can be time consuming as well.

A quick workaround for overcoming this bug without applying the 13477790 patch is adding a simple hint to the query. Adding the /*+NO_XML_QUERY_REWRITE*/ hint before the first element in the query. Will solve this issue.

select /*+NO_XML_QUERY_REWRITE*/ xmlelement("root",
(xmlelement("sub_root",
xmlforest('value' as "some_tag",
(select xmlagg(xmlelement("test",
xmlforest('code_value' as
"Code")))
from dual) as "SOME_AGG_CODES"))))

from dual

Share on RedditEmail this to someoneShare on Google+Share on LinkedInShare on FacebookDigg thisTweet about this on Twitter

Leave a Reply

Your email address will not be published. Required fields are marked *