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