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.

I start off with some SQL Objects:
CREATE TYPE r_arguments AS OBJECT
(
argument_name VARCHAR2(30),
variable_name VARCHAR2(30),
data_type VARCHAR2(30),
defaulted VARCHAR2(1),
position INTEGER,
sequence INTEGER,
in_out VARCHAR2(9)
);
/
show errors

CREATE OR REPLACE
TYPE t_arguments AS TABLE OF R_ARGUMENTS
/
I'll use T_ARGUMENTS as a data type for my table definition:
CREATE TABLE procedures
(
owner VARCHAR2(30)
CONSTRAINT nn_owner_procedures NOT NULL,
package_name VARCHAR2(30),
procedure_name VARCHAR2(30)
CONSTRAINT nn_procedurename_procedure NOT NULL,
function_or_procedure VARCHAR2(1)
CONSTRAINT forp_forp_procedures CHECK ( function_or_procedure IN ( 'F', 'P' ) )
CONSTRAINT nn_forp_procedures NOT NULL,
overload VARCHAR2(40),
arguments T_ARGUMENTS
)
NESTED TABLE arguments STORE AS args;
Thanks to Mr. Morgan and his always helpful library I was able to finally figure out the syntax. Specically, I was ommitting the NESTED TABLE clause there at the end.

When you describe the table and set the describe dept to all it looks like this:
CJUSTICE@ELEVEN>set describe depth all

@DESC PROCEDURES
Name Null? Type
----------------------------------------------------- -------- ----------------
OWNER NOT NULL VARCHAR2(30)
PACKAGE_NAME VARCHAR2(30)
PROCEDURE_NAME NOT NULL VARCHAR2(30)
FUNCTION_OR_PROCEDURE NOT NULL VARCHAR2(1)
OVERLOAD VARCHAR2(40)
ARGUMENTS T_ARGUMENTS
ARGUMENT_NAME VARCHAR2(30)
VARIABLE_NAME VARCHAR2(30)
DATA_TYPE VARCHAR2(30)
DEFAULTED VARCHAR2(1)
POSITION NUMBER(38)
SEQUENCE NUMBER(38)
IN_OUT VARCHAR2(9)
I got that wrapped up, so how to INSERT?
DECLARE
l_arguments T_ARGUMENTS;
BEGIN
l_arguments := T_ARGUMENTS();
l_arguments.EXTEND(1);
l_arguments(1) := R_ARGUMENTS
( argument_name => 'TESTING',
variable_name => 'L_TESTING',
data_type => 'NUMBER',
defaulted => 'Y',
position => 1,
sequence => 1,
in_out => 'IN' );

INSERT INTO procedures
( owner,
package_name,
procedure_name,
function_or_procedure,
overload,
arguments )
VALUES
( 'TESTING',
'TESTING',
'TESTING',
'F',
NULL,
l_arguments );
END;
/
And it looks like this:
OWNER   PACKAGE PROCEDU F OVER ARGUMENTS(ARGUMENT_NAME, VARIABLE_NAME, DATA_TYPE, DEFAULTED, POSITION, SEQUENCE
------- ------- ------- - ---- --------------------------------------------------------------------------------
TESTING TESTING TESTING F T_ARGUMENTS(R_ARGUMENTS('TESTING', 'L_TESTING', 'NUMBER', 'Y', 1, 1, 'IN'))
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_name
FROM
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."
DECLARE
l_arguments T_ARGUMENTS;
BEGIN
l_arguments := T_ARGUMENTS();

FOR i IN 1..10 LOOP
l_arguments.EXTEND(1);
l_arguments(i) := R_ARGUMENTS
( argument_name => 'TESTING',
variable_name => 'L_TESTING',
data_type => 'NUMBER',
defaulted => 'Y',
position => 1,
sequence => i,
in_out => 'IN' );
END LOOP;

INSERT INTO procedures
( owner,
package_name,
procedure_name,
function_or_procedure,
overload,
arguments )
VALUES
( 'TESTING',
'TESTING',
'TESTING',
'F',
NULL,
l_arguments );
END;
/
The result of a SQL statements looks like this:
OWNER   PACKAGE PROCEDU F OVER ARGUMENTS(ARGUMENT_NAME, VARIABLE_NAME, DATA_TYPE, DEFAULTED, POSITION, SEQUENCE
------- ------- ------- - ---- --------------------------------------------------------------------------------
TESTING TESTING TESTING F T_ARGUMENTS(R_ARGUMENTS('TESTING', 'L_TESTING', 'NUMBER', 'Y', 1, 1, 'IN'), R_AR
GUMENTS('TESTING', 'L_TESTING', 'NUMBER', 'Y', 1, 2, 'IN'), R_ARGUMENTS('TESTING
', 'L_TESTING', 'NUMBER', 'Y', 1, 3, 'IN'), R_ARGUMENTS('TESTING', 'L_TESTING',
'NUMBER', 'Y', 1, 4, 'IN'), R_ARGUMENTS('TESTING', 'L_TESTING', 'NUMBER', 'Y', 1
, 5, 'IN'), R_ARGUMENTS('TESTING', 'L_TESTING', 'NUMBER', 'Y', 1, 6, 'IN'), R_AR
GUMENTS('TESTING', 'L_TESTING', 'NUMBER', 'Y', 1, 7, 'IN'), R_ARGUMENTS('TESTING
', 'L_TESTING', 'NUMBER', 'Y', 1, 8, 'IN'), R_ARGUMENTS('TESTING', 'L_TESTING',
'NUMBER', 'Y', 1, 9, 'IN'), R_ARGUMENTS('TESTING', 'L_TESTING', 'NUMBER', 'Y', 1
, 10, 'IN'))
Not too pretty.

I now want to know how many arguments (yes, I already know, it's 10, but humor me):
SELECT 
owner,
package_name,
procedure_name,
overload,
COUNT(*) c
FROM
procedures p,
TABLE( p.arguments )
GROUP BY
owner,
package_name,
procedure_name,
overload;

OWNER PACKAGE PROCEDU OVER C
------- ------- ------- ---- ----------
TESTING TESTING TESTING 10
Voila!

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)...

8 comments:

Saager Mhatre said...

You do realize that you're running a cartesian between your base table and nested table there, right? If you're just looking for a count, the cardinality function should help.

oraclenerd said...

I was just playing with this stuff. I don't know much about it yet. I appreciate the pointer though...didn't realize those were out there.

tanks!

Saager Mhatre said...

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.

So, I fell back to...

select procedure_name, (select count(1) from arguments) arg_count from procedures

... just to avoid the cartesian (or atleast what I think is a cartesian.)

But, I figured there had to be a better way. So I looked up collection functions and there it was- CARDINALITY. Weird name, though. :P

oraclenerd said...

I'm glad you pointed me in that direction. I hadn't really read the docs on UDTs or nested tables (other than Mr. Morgan's library of course).

For me it just comes down to practicality. Once I can find a good use for them (nested tables and the like) I'd be glad to learn more, but until then...

DomBrooks said...

My rule of thumb - if you think you need this sort of OR structure, keep the underlying tables straight up relational and put an OR view on top.

oraclenerd said...

Hadn't thought of that Dom. I'll give that a shot. Gratzi!

Mike said...

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

oraclenerd said...

Thanks Mike! I just might have to take you up on that offer.