reesesWeb

I had the opportunity to tussle with Oracle nested tables this week, because dealing with people who can't learn new things is so much fun. This is why, for the past ten years, I have had a huge disdain for people who don't use IDEs, debuggers, or profilers when working on code -- they think they're smart enough to reason things through, but it's amazing that it takes them longer to get things done, and when they do get things sorted, they suck. Proper tools are the robotic exoskeleton that enable decent programmers to lift cars and crush empty beer kegs against our foreheads.

One of these vi-wielding pansies whined this week about having to work with nested tables in Oracle, and not knowing how to do so via JDBC. There are two paths one can take to success in this space. If you have direct access to the database from the same machine hosting your IDE (which hopefully includes a debugger, so you can step through your code), you can inspect variables for type and content, and drill down into their members and do the same.

If you do not have direct access, things get a little dicier, and require more iterations. Your program will have to pass over the information at as low a level as possible, printing out types or members as necessary. #toString() can be your friend, as well as #getClass().getName(). In true "debug by printf" form, you can cause your program to spew whatever information it can glean. Your next iteration can cast unknown Objects to more specific classes, where you can explore the data exposed via its methods.

Most of you are nodding at this point, because you've been here. Of the people that read this, I know most of us converted to IDEs a long time ago, and even the stragglers have converted some time this century.

In my current consulting engagement, I run a large group of people who use TextPad, which pretty much describes its range of functionality. It's a text editor for windows that...edits text. I've been trying to shove the bletcherous-but-still-better Eclipse down their throats, but it's a slow battle. Asking these people if they can connect to a process with their debugger sometimes elicits an answer, the recollection of which makes me weep to this day.

A "professional services" engineer (this is a blog entry unto itself, the difference between professional services and consultants) was the vi-whiner mentioned above. I don't actually know if he used vi, so I might be exposing a prejudice, but his reaction irritated me enough to dig into the matter itself using my preferred Java editor, IDEA. I'm not as big a fan of IDEA as I used to be, because I think it's moving in the wrong direction, adding features instead of improving usability and consistency, but it's still a leap ahead of Eclipse or NetBeans.

I'll leave it as an exercise for the reader on how to set up nested tables in Oracle. I believe they've been available since version 8, but I could be wrong. I know for a fact they've been available since 9i. The senior DBA suggested them as an alternative to a fully-denormalised table suggested by the vi-whiner above, who was asking for a 2000-field table. Yes, 2000. This is not uncommon with search providers, of course, but it still makes ice water in my bowels when I hear about it.

Basically, you'll create a table as a type, and then add a special field and storage specifier to your containing table DDL. It's straightforward, and my only concern is that I don't know if this is one of those Oracle "features" like OPS that explode when you try to use them.

Regardless, vi-whiner wanted to use his 2000-field table (WHOSE FIELDS HE WOULD ACCESS BY NAME) rather than a nested table of name/type/value/etc. tuples. I get angry when faced with this kind of stupidity, and started grinding away to find out how nested tables are represented in Java.

First of all, I used Oracle's classes12.zip for Java 1.4 and Oracle 9 against a 9.2.0.5 instance. If you use an older or newer version, I imagine classes or methods may be different.

As you can guess, there are a few oracle specific classes in evidence, especially oracle.sql.ARRAY and oracle.sql.STRUCT. I don't know if any other RDBMS supports nested tables, and I'm too lazy to google for it, but I would assume a minimum of portability anyway.

The method for retrieving the records from the nested table is a little awkward via Oracle's JDBC driver. The type returned by ResultSet.getMetaData().getColumnType(n) (where n = the field storing your nested table) will be a number not used by another type, and #getColumnTypeName(n) will return the type name you gave your nested table.

However, when you iterate over the containing ResultSet, and you retrieve the nested table using ResultSet.getObject(n), you will receive an instance of oracle.sql.ARRAY. ARRAY sounds easy, right?

Well, what you really want to do with this ARRAY is call #getResultSet(), which will return a sort-of ResultSet. It'll be a nice friendly java.sql.ResultSet object, but you can't call #getMetaData() on it, for example. I didn't spend much time grinding through the documentation or the available methods, so I don't yet know how to retrieve the number of fields in this table if it's not already known. This turns out to be less important than you may think.

Iterating over this inner ResultSet is a little odd. It doesn't look like the table you declared when defining your nested table type. It has a significantly different structure. It has two fields, regardless of the DDL you used to define it. The first field (remember that ResultSet access is 1-indexed) is merely a numeric index field. It also appears to be 1-indexed.

The second field is stranger. It's an instance of oracle.sql.STRUCT, which is basically a wrapper around an array of attributes, which conform to your nested table specification. There's a very convenient method called #debugString() that will return a printable representation similar to this:

name = TSMRXC.ELEMENTS length = 2 attribute[0] = 175692 attribute[1] = 120.12

You can access the attributes via the oracle.sql.STRUCT.getAttributes() method, although there are a few other methods on that object that look interesting.

I have to admit that it's yet another piece of JDBC that makes things more annoying and difficult than they should be, and that perhaps nested tables themselves are a gross hack in almost every case instead of using a table with a foreign key relationship to the parent table, but if your vi-whiner insists on getting one table, feel free to impose this on him.

With that, I'm off to Ireland, where it is supposed to rain much of the week. I'm so glad I had the foresight not to buy a rain shell. I'm turning off the Treo once I'm safely on the plane to Dublin, and I'm not touching a computer until I come back, so you'll all have to make do with your melatonin or Xanax to get to sleep.

Bookmark/Search this post with:

delicious | digg | reddit | technorati