Saturday, January 3, 2009

Nested Tables

One concept of Oracle that I've always been intrigued by is Nested Tables. Nested tables are, basically, a table (dataset) stored in a single column. They are part of the object-relational features of Oracle. Now, I've never found a practical use for them as it's still relational data in reality.

Anyway, I've been playing around with them in regards to the test harness that I've mulling over.

I decided to try them out to store the arguments for each procedure/function. I can certainly string this together at runtime, but again, just trying to learn something new.

What if you want to be able to select a given value from inside the nested table? I swear in an earlier version of Oracle you just had to qualify, or maybe I'm just crazy, but here's how you do it now:

SELECT variable_nameFROM procedures p, TABLE( p.arguments );

VARIABLE_NAME------------------------------L_TESTING

You need to CAST your nested table using the TABLE clause. I still haven't figured out how (or if) you ever need to use ARGS (from the NESTED TABLE clause above).

Alright, now I'm going to add 10 records into the nested table. In the relational world, you'd have 2 tables to do this. One for the procedures and one for the arguments. I'll do it in "1."

I still don't have a practical application for this as this can be done on the fly using PL/SQL collections. Perhaps this in combination with your Middle Tier application (returning UDTs back to the calling application)...

Neither did I, but something in your SELECT statement just seemed smelly.

My first instinct was to stick a ARGUMENTS.COUNT in the select list. I was hoping that since SQL automatically DEREFs objects and allows attribute traversal, that would just invoke the COUNT collection function. That failed, however.

For what it's worth, Chet, I'm about 90% of the way through a book on this subject (Michael McLaughlin - Oracle Database 11g PL/SQL Programming).I can't promise you I can be of any use, but if you have an issue I can research for you while in the course of my regular study, let me know.I'd probably learn in the process and God knows I can use every trick I can find to motivate myself to slog through this thing (it's about 1200 pgs).Cheers,-Mike