perlknight has asked for the wisdom of the Perl Monks concerning the following question:

heres's an example of bind variable in sql: 

 exec :deptno:=20; 
 select * from emp where deptno=:deptno;

[download]

Is there a way to do this with Oracle DBD? Thanks.

Replies are listed 'Best First'.
Re: How do I use bind variable w/oracle DBD?
by lachoy (Parson) on Feb 26, 2002 at 21:43 UTC

    Check the docs :-) To make your SQL portable, it's usually better to use placeholders.

     my $deptno = 20;
 my $sql = 'SELECT * FROM emp WHERE deptno = ?';
 my $sth = $dbh->prepare( $sql );
 $sth->execute( $deptno );
 ...

    [download]

    Oracle also comes with the additional bind_param_inout() feature, but you should look at the more complete docs in perldoc DBD::Oracle for that. (I'd also recommend against using SELECT *, but that's a private matter between you and the rest of your team :-)

[reply]
[d/l]
[select]
      I know, select * is not a good idea; It was just an example to get the point across. Well, anyway after checking the docs, I found out how to do it. With bind variable it actually improve performance bec. the sql sythax doesn't have to be reparse. Well here's how: 
      $sth_detections = $dbh->prepare("$QUERY2") || die $DBI::errstr;
for ...
my $city = $city[$i];
my $station = $station[$i]; 

$sth_detections->bind_param(":city", $city);
$sth_detections->bind_param(":station", $station);
$sth_detections->execute;
@row = $sth_detections->fetchrow;

      [download]
[reply]
[d/l]
        I would like to know how to do this with Win32::ODBC . Many of the functions and structures are not available there. Thanks
[reply]

Back to Seekers of Perl Wisdom