Database Software

Hi,

I have hit the bug 4277241 , which was referred to her as well: 3241538 for my 11.2.0.1 database.
This is due to the result set being to big.

Now i have read the metalink note and it says:

xmlagg() with a GROUP BY can fail with ORA-22813 if the result is too large.
This is normal and expected as there is a hard coded limit on the result
size *BUT* this fix allows event 44410 to be set which uses a different method
to evaluate the XMLAGG(). The event is disabled by default.

NOTE: If the event is set to work around the 30K limit then LOB leakage
      can occur. This is expected and is due to the way the limit is 
      avoided.

Workaround
  Rewrite the query without using "group by" by using select distinct method

Apparently using the event causes a memory leak which is ofcourse undesirable.

So is the problem caused by using the GROUP BY in conjunction with XMLAgg or just by using the XMLAgg altogether ?

If the answer is the GROUP BY then how does one rewrite a query without resorting to subqueries ?
DISTINCT can only be used with the SELECT keyword, so the second XMLAgg used below can not be preceded by an DISTINCT.
Using subqueries for every nest-level makes the query rather large and complicated , that is the reason for asking.

The query below can be rewritten using subqueries, and thus avoiding the GROUP BY, but i am more interested in alternatives using DISTINCT or whatever.

Ofcourse the query below doesn't give the error but does show what i mean:

select 
 xmlagg(
   xmlelement("department",
     XMLAGG(
       XMLElement("employee",
        xmlelement("first-name" , e.first_name),
        xmlelement("last-name", e.last_name)
                  ) 
          order by e.last_name
          ),
          d.department_name
         )
        )as "xml"
        from  employees e  
              join  departments d on (e.department_id = d.department_id)
              join locations l on ( d.location_id = l.location_id)
        group by d.department_name
    ;