RE: perl DBI/DBD: can I pass in an array as parameter?

not sure if this is what you want. one sql call

select tab1.col1, tab2.col2 from tab1, tab2

where tab1.ID1 = tab2.ID2
and   tab1.X = ?
and   tab1.X = ?
and   tab1.X = ?

@my_array = (1,2,3);
sth->execute(@my_array);

On Tue, 1 Jul 2003, Guang Mei wrote:

> No. I want to reduce the numebr of sql calls.
>
> For example,
>
> @my_array = (1,2,3);
>
> I don't want to do
>
> select tab1.col1, tab2.col2 from tab1, tab2
> where tab1.ID1 = tab2.ID2
> and tab1.X = 1;
>
> select tab1.col1, tab2.col2 from tab1, tab2
> where tab1.ID1 = tab2.ID2
> and tab1.X = 2;
>
> select tab1.col1, tab2.col2 from tab1, tab2
> where tab1.ID1 = tab2.ID2
> and tab1.X = 3;
>
> because that would require three calls to Oracle. I want to pass @my_array
> in so the sql statment only run once and return all the rows. Similar to
> "forall ..." in PL/SQL situation.
>
> Guang
>
> -----Original Message-----
> STEVE OLLIG
> Sent: Tuesday, July 01, 2003 2:56 PM
> To: Multiple recipients of list ORACLE-L
>
>
> if i get you right - you're trying to treat the resultset from a query as an
> array in Perl. That's the way it works. Try something like this:
>
> $sth = $dbh->prepare (" select tab1.col1, tab2.col2 from tab1, tab2
> where tab1.ID1 = tab2.ID2
> and tab1.X = ? ");
> my $resultset = $sth->execute("value for X");
> foreach (@$resultset) {
> my($col1, $col2) = @$_;
> # do more stuff
> }
>
> -----Original Message-----
> Sent: Tuesday, July 01, 2003 11:20 AM
> To: Multiple recipients of list ORACLE-L
>
>
> Hi:
>
> Does anyone know if I can pass an array in perl to an sql using DBI/DBD so
> that I get result set from Oracle?
>
> Specifically, I want do something like in perl:
>
>
> $sth = $dbh->prepare (" select tab1.col1, tab2.col2 from tab1, tab2
> where tab1.ID1 = tab2.ID2
> and tab1.X = ? ");
>
> my @my_array;
>
> # filling in my_array with data here
>
> $sth->execute(@my_array);
>
> my ($col1, $col2);
> while(($col1, $col2) = $sth->fetchrow_array()) {
>
> # do processing of $col1 and $col2;
> }
>
> I am hoping to save repeated sql calls, do only one call and get all the
> rows back.
>
> Is it possible in any version DBI/DBD? I looked at
>
> http://perldoc.com/perl5.6.1/lib/DBI.html
>
> and I does not seem to be able to figure it out. If it is possible, any
> simple example code somewhere?
>
> Thanks.
>
> Guang
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Guang Mei
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: STEVE OLLIG
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Guang Mei
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Alex
  INET: [EMAIL PROTECTED]

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).