Re: Nested table structures in Oracle & DBI

Is there any way in which you could avoid returning the table and instead, return a cursor?

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.

Comment on Re: Nested table structures in Oracle & DBI

Replies are listed 'Best First'.
Re: Re: Nested table structures in Oracle & DBI by perlboy26sf (Initiate) on Aug 14, 2002 at 23:33 UTC
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	[reply] [d/l]

Replies are listed 'Best First'.

Re: Re: Nested table structures in Oracle & DBI
by perlboy26sf (Initiate) on Aug 14, 2002 at 23:33 UTC

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]

[reply]
[d/l]