Wednesday, July 25, 2012

learn - perl DBD:Oracle bind variable?


I was interested about perl (DBD:Oracle) connect with Oracle Database and bind variable, Everyone who works with Oracle Database know, we should use SQL or PL/SQL with binding variable. So, This was simple program for learning with DBD:Oracle.
bind_param 
bind_param_inout
[oracle@chart01 ~]$ cat add.pl
#!/usr/bin/perl
use strict;
use DBI;
my $num_args = $#ARGV + 1;
if ($num_args != 2) {
  print "Example:\t./add.pl number1 number2\n\n";
  exit;
}
my $i=$ARGV[0];
my $j=$ARGV[1];
my $result;
my $db = DBI->connect( "dbi:Oracle:orcl", "demo", "demo",{AutoCommit=>0, RaiseError=>1} )
or die "Couldn't connect to database: " . DBI->errstr;
my $sth = $db->prepare("BEGIN
                :result := fn_add(:i,:j);
                END; ")
or die "Couldn't prepare statement: " . $db->errstr;
$sth->bind_param( ":i",$i);
$sth->bind_param( ":j",$j);
$sth->bind_param_inout(":result", \$result,38);
$sth->execute()
or die "Couldn't execute statement: " . $sth->errstr;
$sth->finish;
$db->disconnect;
print "Result: $result \n\n"; 
[oracle@chart01 ~]$ ./add.pl 1 2
Result: 3
[oracle@chart01 ~]$ ./add.pl 1 1
Result: 2
[oracle@chart01 ~]$ ./add.pl 1 0
Result: 1
[oracle@chart01 ~]$ ./add.pl 100 50
Result: 150
[oracle@chart01 ~]$ ./add.pl 10 5
Result: 15
Then I checked in V$SQLAREA
SQL> select sql_text, executions  from v$sqlarea where sql_text like '%:result := fn_add(:i,:j)%';
SQL_TEXT                                                                                   EXECUTIONS
------------------------------------------------------------------------------------------ ----------
BEGIN                 :result := fn_add(:i,:j);                 END;                               14
However, If you would like to query by using "SELECT" command. You could bind column (bind_columns) as:

$sth = $db->prepare("select address from accounts where name = :name ")
        or die "Couldn't prepare statement: " . $db->errstr;
$sth->bind_param( ":name",$in_name);
$sth->execute()
or die "Couldn't execute statement: " . $sth->errstr;
$sth->bind_columns(undef, \$address);

Yes, that's something. I learned today. not a lot.

1 comment:

Mladen Gogala said...

There is an old article which explains that very well:

http://www.dba-oracle.com/t_dbi_interface1.htm