Re: Nested table structures in Oracle & DBI

[talexb]

Pardon the duplication, but the contents of perlboy26sf's scratch pad are:

[Wed Aug 14 01:38:28 2002] device2obj.pl: Field 3 has an Oracle type (
+108) which is not explicitly supported at /opt/perl/modules/Oracle/8.
+1.6/lib/5.6.0/DBD/Oracle.pm line 293.
[Wed Aug 14 01:38:28 2002] device2obj.pl: Field 4 has an Oracle type (
+108) which is not explicitly supported at /opt/perl/modules/Oracle/8.
+1.6/lib/5.6.0/DBD/Oracle.pm line 293.
[Wed Aug 14 01:38:28 2002] device2obj.pl: Field 5 has an Oracle type (
+108) which is not explicitly supported at /opt/perl/modules/Oracle/8.
+1.6/lib/5.6.0/DBD/Oracle.pm line 293.

# sql table definition
 ----------------------------------------- -------- ------------------
+----------
 INTERFACE_ID                                       NUMBER
 DEVICE_ID                                          NUMBER
 INTERFACE                                          VARCHAR2(32)
 ARP                                                ARPS_NT
 CDP                                                CDPS_NT
 EIGRP                                              EIGRPS_NT
 ADMIN_STATE                                        VARCHAR2(32)
 LINE_PROTOCOL_STATE                                VARCHAR2(48)
 HARDWARE                                           VARCHAR2(48)
 ADDRESS                                            VARCHAR2(48)
 MTU                                                VARCHAR2(48)
 BW                                                 VARCHAR2(48)
 DLY                                                VARCHAR2(48)
 RELIABILITY                                        VARCHAR2(48)
 TXLOAD                                             VARCHAR2(48)
 RXLOAD                                             VARCHAR2(48)
 ENCAPSULATION                                      VARCHAR2(48)
 LOOPBACK_STATE                                     VARCHAR2(48)
 ARP_TYPE                                           VARCHAR2(48)
 ARP_TIMEOUT                                        VARCHAR2(48)
 LAST_INPUT                                         VARCHAR2(48)
 LAST_OUTPUT                                        VARCHAR2(48)
 LAST_OUTPUT_HANG                                   VARCHAR2(48)

essentially, whats happening is that the following sql:

    my $sql = "select d.device_id, i.interface_id, i.arp, i.eigrp, i.c
+dp from device_objtab d, interfaces_objtab i where d.device_id = $obj
+ect_id";

is attempting to recover a type (three, really) 108 ora_type field, wh
+ich in these instances are user-defined nested table structures.

i was hoping to find a way to tell DBI how to handle this type for ret
+rieval.

   my $sth = $dbh->prepare($sql) || warn "[@caller]\n";
   if (!(defined $sth)) { $OraMethods::error = "Can't prepare $sql: $D
+BI::errstr $sql \n -> [@caller]\n" and return 0; }

--t. alex

"Mud, mud, glorious mud. Nothing quite like it for cooling the blood!"
--Michael Flanders and Donald Swann

[frankus]

Monk Stuff
It saddened me to see whilst upvoting talexb's reply; it had been down-voted.
The contents of a scratchpad are temporary and in adding the notes here,
it's easier to provide a permanent record of the question in one location,
for the benefit of other querants and those providing answers.

Oracle Stuff
It's not clear what the field types contain.
Perhaps the easiest solution would be to write a PL/SQL function to decode the troublesome fields.

AFAIK there isn't such a thing as a nested table structure in Oracle or any other relational databases?

--

Brother Frankus.

¤

[perlboy26sf]

Actually, in fact there are such things (and why was this 
marked as a duplicate?)

http://download-west.oracle.com/otndoc/oracle9i/901_doc/appdev.901/a88878/adobjdes.htm#441615

While oracle is in fact at it's core a relational database structure, 
oracle8i and above provide some nifty objectish approaches
to both storing and recovering data in more complex structures.

Think of the fields represented here with *_NT as being tables
within the row elements of the interface table.  In perl you
would call this strucrure a multi dimensional hash, where the keys
*_NT point to embedded hashes as their values.

The upshot of doing something like this is that when you are
dealing with many tables which have extremely large counts of
row data, you do not have to do explicit joins to find the
data you wish to retrieve, thus making things run ALOT faster 
by avoiding the need to create a large cartesian product and
then eliminating rows before retrieving your data.

I cant possibly have been the first person to try this. <:)

   --perlboy

[frankus]

Okay, I read the blurb, and it sounds pretty grotesque.
Personally I'd just do two SQLs rather than gild a lily...
and not knit the code so closely to one database manufacturer.


I cant possibly have been the first person to try this. <:)
You aren't.. However, you'll not want to hear that..
From what I've read they're akin to LOBS, I guess you've checked this on CPAN?

--

Brother Frankus.

¤