Retrieving multi-level tables from an Oracle DB

From: Nicholas Trandem (nicholas.trandem_at_fedex.com)
Date: 09/13/04 

To: dbi-users@perl.org
Date: Mon, 13 Sep 2004 09:58:56 -0400

I'm trying to write a Perl program that filters the contents of an input
file. After each line from the input file is parsed, the fields of the
line are submitted to if statements to determine which output file to
write the line to. It would be pretty straightforward except for the
requirement that the filter needs to assemble these if statements at
runtime based on rules stored in the DB. The rules are stored in an
Oracle multi-level table defined as:

CREATE TYPE rule_expression_t AS OBJECT (
  field_name VARCHAR2(128),
  condition CHAR(2),
  value VARCHAR2(20)
  );

CREATE TYPE rule_t AS TABLE OF rule_expression_t;

CREATE TABLE filter_rules (
  name VARCHAR2(128),
  created_by VARCHAR2(10),
  created_dt DATE,
  updated_by VARCHAR2(10),
  updated_dt DATE,
  rule rule_t
)NESTED TABLE rule STORE AS filter_rules_nested_table;

What I would like to do, if possible, is make one query (along the lines
of "SELECT rule FROM filter_rules") and stuff the results into a
three-dimensional list (something like
@rules_list[rule][rule_expression][field of rule_expression]). Is there a
way to do this using DBI and DBD::Oracle? I can't seem to find any
examples or documentation about passing whole multi-level tables into and
out of Perl. Any help would be appreciated!

Thanks!
_____
Nicholas A Trandem
x2953