Well I have had a look into it and I do not think there is anything in
DBD::Oracle we have to do to make varray work as it is simple SQL and is
handled at the OCI level of the SQL commnd
For example the following script works without any issues.
#!/usr/local/bin/perl#use DBI;use strict;use vars qw($dbh $sth $sql);
$dbh = DBI->connect('dbi:Oracle:','[EMAIL PROTECTED]','xxx');$dbh->do("DROP
TABLE
GAS_LOG");$dbh->do("DROP TYPE GAS_LOG_VA");$dbh->do("DROP TYPE
GAS_LOG_TY");$dbh->do("CREATE TYPE GAS_LOG_TY AS OBJECT ( GALLONS
NUMBER, FILLUP_DATE DATE, GAS_STATION
VARCHAR2(255))");$dbh->do("CREATE TYPE GAS_LOG_VA AS VARRAY(100) OF
GAS_LOG_TY");$dbh->do("CREATE TABLE GAS_LOG (VIN NUMBER NOT
NULL, GAS_LOG GAS_LOG_VA)");$dbh->do("insert into gas_log values
(101010101010101,
gas_log_va(gas_log_ty(32,sysdate-1,'Shell')))");$dbh->do("insert into
gas_log values (222222222222222,
gas_log_va(gas_log_ty(27,sysdate-1,'Texaco')))");$dbh->do("insert into
gas_log values (321321321321321, gas_log_va(
gas_log_ty(45,sysdate-10,'Diamond Shamrock'),
gas_log_ty(31,sysdate-9,'Shell'), gas_log_ty(32,sysdate-8,'Shell'),
gas_log_ty(33,sysdate-7,'Texaco'), gas_log_ty(34,sysdate-6,'Texaco'),
gas_log_ty(35,sysdate-5,'Diamond Shamrock')))");$sql='select a.vin,var.*
from gas_log a, table(gas_log)
var';$sth=$dbh->prepare($sql);$sth->execute();my $values =
$sth->fetchall_arrayref();print_time("Test 1 mid:"); foreach my
$r_value (@$values ){ print
$r_value->[0].",".$r_value->[1].",".$r_value->[2].",".$r_value->[3]."\n";}There
may be permission isssues with the user who is connecting that took me a
little while to figure out. Otherwise I think you can get all your data
using plain SQL It is just a matter of syntax
----- Original Message -----
From: "Charles Pareto" <[EMAIL PROTECTED]>
To: "'John Scoles'" <[EMAIL PROTECTED]>
Cc: <dbi-users@perl.org>; <[EMAIL PROTECTED]>
Sent: Wednesday, June 06, 2007 12:18 PM
Subject: RE: DBD-Oracle VARRAY not supported
Sure, here is my example-
I'm declaring a column as type varray like this:
"CREATE TYPE phone as varray(20) of varchar2(30)"
I then insert phone numbers into the varray like this:
insert into ADDRESS values (phone('949-933-5680', '949-600-5866',
'949-699-3608'))
This creates a column of varray where I can store up to 20 phone numbers
in
that one column field.
When I select this column to see my phone numbers I will see this:
PHONE
--------------------------------------------------------
PHONE('949-933-5680', '949-600-5866', '949-699-3608')
When I try and return this column using DBD-Oracle with the statement
"select phone from address"
I get the error message like below.
prepare failed: ERROR OCIDefineObject call needed but not implemented yet
[for Statement "select phone from address"]
I read somewhere that objects aren't implemented in DBD-Oracle and this is
what I'm trying to find out.
Thanks,
Chuck
Charles Pareto
Brand Protection Engineer
[EMAIL PROTECTED]
408-525-5505
-----Original Message-----
From: John Scoles [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 06, 2007 3:46 AM
To: [EMAIL PROTECTED]
Cc: dbi-users@perl.org
Subject: Re: DBD-Oracle VARRAY not supported
'varray' haven't hear of that in years. At least not since Nested Tables
came along . I aways thoght they were for PL/SQL only.
I will have a look at them later this week. I am just wondering how one
want
to look at the data in DBI??
Can you give me a quick working example of what you want to do and an
example of what you want to see in the end?
cheers
John Scoles
----- Original Message -----
From: "Tim Bunce" <[EMAIL PROTECTED]>
To: "Charles Pareto" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>; <dbi-users@perl.org>
Sent: Wednesday, June 06, 2007 4:39 AM
Subject: Re: DBD-Oracle VARRAY not supported
On Tue, Jun 05, 2007 at 05:03:23PM -0700, Charles Pareto wrote:
Hi Tim,
[...] All I'm looking for is a way to select a column that is defined
as type varray.
Is there any way this can be accomplished using DBD-Oracle or
anything
else?
For simple data you could use SQL statements to convert the info into a
string.
There are probably better ways (returning ref cursor, pipelined function
etc)
but I'm a little out of touch with Oracle these days. Check google and
the archives.
Of course, the best option would be to implement proper support for
varrays.
That's probably not to difficult. I'll say "patches welcome" on behalf of
Pythian,
who are the lead maintainers.
Tim.
p.s. Don't use cpanforum. Very few people read it. The dbi-users@perl.org
list
is the best place to get help.
