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