This works, but can you search for a specific value within a varray?
Charles Pareto Brand Protection Engineer [EMAIL PROTECTED] 408-525-5505 -----Original Message----- From: John Scoles [mailto:[EMAIL PROTECTED] Sent: Thursday, June 14, 2007 10:54 AM To: [EMAIL PROTECTED] Cc: dbi-users@perl.org Subject: Re: DBD-Oracle VARRAY not supported 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";}Th ere 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. >> >