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.

Linux For The Kids?

So there's this old laptop that's been hanging around my house in need of both a regular battery and now a CMOS battery as well.  It's a Thinkpad 600e, for the curious.  I'm thinking, now that my oldest is in school and her sister is coming up right behind her, that it might be time to designate one of my machines as the kids' setup.

Linux is the only real option, but which one?  You can't just say "ubuntu" whenever the question is "which distro?"  Sometimes it just don't work.  Here's the constraints:

* Easy online setup (driver recognition) so they can play Flash games.

* Must be able to play Flash games. :)

* Graphics requirements not high (this thing will probably do 800x600 at the most), but I'd like them to be able to play TuxTyping, TuxMath, those sorts of games.

* Boot without login option.  There's no need for them to worry about usernames and passwords.

* Fast bootup!  Perhaps most important of all, when a 4yr old child wants to play on the computer, she does not want to wait 3 minutes for it to get started. 

Suggestions appreciated.  The system does have a hard drive, so I shouldn't have to worry about running off a USB stick.  But if somebody tells me that Puppy Linux or some other mini-distro is what I'm looking for, then I'll take it!

Making Changes After The Roof Is On

Today a fellow engineer bemoaned late design changes to the spec, pulling out the old architect's analogy of changes "after the roof is on."

I decided why I don't love that analogy.  In the architect's world, he typically has a meeting with the people who are going to, you know, live in the house.  Software guys almost never get that option.  The closest we come would be more analogous to the architect going up to the carpenter and saying, "Hey, what kind of windows do you think the homeowner wants?  I like 3x4 pane, myself."  And then the carpenter says, "I dunno, I have 2x2 in mine, and all the houses in my neighborhood seem to have those.  They're much easier to clean."  They then have a discussion of the relative pros and cons of each, and finally pick one that they're both happy with - which has nothing to do with what the user might have wanted.

So yeah, it's quite possible that "after the roof is on", in our situation, the homeowner shows up and says "Wow, I hate those windows."

The best part is then when the carpenter and the architect, who picked the windows through mutual agreement, go away grumbling that the homeowner doesn't know what she's talking about, because obviously their decision was the better one.

Net Stop Beep

I've posted before about the three most beautiful words in a Windows console, "net stop beep".  This simple command will kill forever the ancient and annoying Control-G beep that pops up every now and then and, if you're like me, drives you right out of your headphones.

Only it's not forever, it's only until you reboot.

Anybody know how to kill it dead forever?  Does autoexec.bat still exist, can I put it in there? :)

Battery Fail

Recently I took about an hour worth of video on my camcorder.  It's one of those digital cams that has the mini tapes in it, and I'm always stuck trying to figure out the best way to get it off there and onto some format that we can watch regularly.  (One enterprising friend said to never bother, just plug the camera back into the tv whenever you want to watch).

It just so happens that the wife's Linux laptop has a Firewire port, and after a little wrangling I found some software (Kino) that would, in fact, capture the video nicely.

I get everything wired up and it starts running.  The whole setup is on the couch next to me as I work on my own laptop.  It dawns on me that while the laptop has about 3 hours of battery life, the camcorder does not usually do a full hour, so I go plug the camcorder in to wall power.

The video is 57 minutes long, and it transfers in real time.

At 54 minutes, the laptop shut itself down, out of battery.  I'd apparently not realized that while doing the firewire/video thing, battery would be eaten at a much greater rate.  Dangit.  No idea what it did to the actual file it had been building for the last hour, as I gave up and went to bed.

Moral of the story, don't guess, just plug everything in.

I Find This Fascinating

So the other day I notice that on the back of the men's room door at work is a new gadget.  It's situated right over the door handle, and does in fact read "Automatic Door Handle Deep Cleaning System."

I find this fascinating.  I realize that there are people that have an issue here.  You've done your thing, you've washed your hands, and what's the first thing you need to do next?  Touch the door handle.  I've seen places where they put the trash can near the door so that you can use your paper towel to avoid touching the handle, and then just throw it in the trash.

I love the idea that somebody had such a problem with this that they invented a device specifically for it.  This is not some sort of general purpose cleaner, people.  This thing's purpose in life is to clean door handles.  That's what it does.  That's all it does. 

So far everybody who I've spoken with about it is intrigued, and has no idea how it works.  Several people have suggested that perhaps it is some sort of ultraviolet thing.  I point out that it has a "refill" light on it, and it is unlikely to be filled with UV rays.

I couldn't help it, I actually googled for the thing to learn more about it (the link above).  Turns out it is situated above the door handle so that it can emit a "deep cleaning spray" down onto the handle at regularly programmed intervals.  Doesn't seem to say what exactly it's being sprayed with.

What'll they think of next?