Tuesday, October 28, 2008

Oracle ORA-3113 with XmlAgg and Order By

I've always said: if you've got a problem and you can't google the answer, then solve the problem and write a blog post about the answer :).

Many folks may not realize that Oracle's XmlAgg function has an "order by" clause available.  As a matter of fact I even found one supposed resource site that said "make sure you put an order by on the select that you'll pass to the XmlAgg", which just plain does not work.

You invoke it like this:

select
       xmlagg (
         xmlelement ( "recipient",
           xmlelement ( "id", id ),
           xmlelement ( "code", code ),
           xmlelement ( "label", label )
           )
           order by sort_order
           )
       from data_table;

Note in particular that the order by clause simply comes at the end of the XmlAgg, after the XmlElement clauses.  No comma or anything like that.

Well, I was trying this all day long inside a stored procedure and kept running into the dreaded ORA-3113 error, which basically means something bad happened somewhere in the middle.  Strangest thing, because that's certainly the right syntax.

Know what solved it for us?  The XmlAgg has to see the variable you want to order on.  In the example above I want to sort on sort_order, but there's no associated XmlElement for it.  I looked back in the references I'd googled and sure enough it's always mentioned.  So instead, I did this:

     xmlagg (
         xmlelement ( "recipient",
           xmlelement ( "id", id ),
           xmlelement ( "code", code ),
           xmlelement ( "label", label )
           xmlelement ( "order", sort_order )
           )
           order by sort_order
           )

And presto!  No more ORA-3113.

Not sure if that'll work for everybody, but it's worth a shot if you're stuck.

8 comments:

George said...

Perfect - Just what I was looking for.
Thank you very much!

Natarajan Arumugam said...

Great, It worked perfect . Thanks

ஆனந்த் said...

Thank you. I googled it for the answer :)

SJ Emmons said...

Thanks for this. For your information you don't have to put the sort order column in the element necessarily, it just has to be in the SELECT column list somewhere.

Anonymous said...

Thanks bro - you made my day !!

Bala

Anonymous said...

Excellent stuff, exactly what i was looking for.

Firas said...

Thanks, that helped me.

Anonymous said...

Awesome!! Thanks!