[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; }
[download]

--t. alex

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

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.

¤

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

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
[download]

This probably means rewriting the SQL to live within a stored procedure and calling said stored procedure from your perl code (this is what we do). But we've found we can convert most/all of our requirements to cursors.

A nice caveat is this: Do not return NULL instead of a zero-row cursor. Current DBIs, AFAIK, don't like this. Use a cursor to something like SELECT column FROM dual WHERE 1 = 0 for this case.

Regards.

SOLVED: W00H00!

After much beverage goodness, and consideration, I ended up
answering my own question.  Since DBD::Oracle.pm doesnt want
to grab the nested_table as a type, convert the nested 
structure into a flattened table type ala table() and then 
pass it thru DBD::Oracle.  

So, if you replace the $sql line above with the following:

   my $sql = "select i.device_id, a.device_id from interfaces_objtab i
+, table(i.arp) a ";  

Instead of trying to figure out what a nested table is, 
it just returns a 'flattened' view of the data structure,
much to my delight, since DBD::Oracle can cope with that
a little better.

Thanks for the replies guys!
[download]

-- perlboy26sf