IF you will run the following query on an oracle 11 db <\/p>\n
from dual<\/code><\/p>\n 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<\/strong>” . 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.<\/p>\n 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.<\/p>\n Oracle identified this problem as a known issue bug #13477790 <\/strong>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. A quick workaround for overcoming this bug without applying the 13477790 patch <\/strong>is adding a simple hint to the query. Adding the \/*+NO_XML_QUERY_REWRITE*\/ <\/strong>hint before the first element in the query. Will solve this issue.<\/p>\n from dual<\/code><\/p>\n","protected":false},"excerpt":{"rendered":" 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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[78,21,79],"class_list":["post-221","post","type-post","status-publish","format-standard","hentry","category-oracle","tag-78","tag-oracle-patch","tag-xmlsql"],"_links":{"self":[{"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/posts\/221","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/dbtricks.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=221"}],"version-history":[{"count":5,"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/posts\/221\/revisions"}],"predecessor-version":[{"id":226,"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/posts\/221\/revisions\/226"}],"wp:attachment":[{"href":"https:\/\/dbtricks.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=221"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbtricks.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=221"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbtricks.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=221"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}select xmlelement(\"root\",
\n (xmlelement(\"sub_root\",
\n xmlforest('value' as \"some_tag\",
\n (select xmlagg(xmlelement(\"test\",
\n xmlforest('code_value' as
\n \"Code\")))
\n from dual) as \"SOME_AGG_CODES\"))))<\/p>\n
\nAnother 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.<\/p>\nselect \/*+NO_XML_QUERY_REWRITE*\/<\/strong> xmlelement(\"root\",
\n (xmlelement(\"sub_root\",
\n xmlforest('value' as \"some_tag\",
\n (select xmlagg(xmlelement(\"test\",
\n xmlforest('code_value' as
\n \"Code\")))
\n from dual) as \"SOME_AGG_CODES\"))))<\/p>\n