perl操作oracle数据库

到：http://search.cpan.org/~timb/DBI-1.613/lib/DBI/DBD/SqlEngine.pm
下载DBI-1.6
和到：http://search.cpan.org/~pythian/DBD-Oracle-1.21/
下载DBD-Oracle-1.21

[root@localhost ~]# tar zxvf DBI-1.613.tar.gz
[root@localhost ~]# cd DBI-1.613
[root@localhost DBI-1.613]# perl Makefile.PL
[root@localhost DBI-1.613]#make
[root@localhost DBI-1.613]#make install

[root@localhost ~]#cd
[root@localhost ~]# tar zxvf DBD-Oracle-1.21.tar.gz
[root@localhost ~]# cd DBD-Oracle-1.21
在oracle的HOME目录下的.bash_profile最后加上export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/jdbc/lib:$LD_LIBRARY_PATH

[root@localhost DBD-Oracle-1.21]# . /home/db/oracle/.bash_profile
[root@localhost DBD-Oracle-1.21]# perl Makefile.PL
[root@localhost DBD-Oracle-1.21]#make
[root@localhost DBD-Oracle-1.21]#make install

若报如下错误：
nstall_driver(Oracle) failed: Can't load '/usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi/auto/DBD/Oracle/Oracle.so' for mod
ule DBD::Oracle:
解决方案如下：
# echo /u01/app/oracle/product/10.2.0/db_1/lib >> /etc/ld.so.conf
# echo /usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi/auto/DBD/Oracle >> /etc/ld.so.conf
# ldconfig
# ldd /usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi/auto/DBD/Oracle/Oracle.so
查看lib包的相关性是否正常

我的测试数据如下：
建表与插入数据
create table BOOKS
(
  ISBN           VARCHAR2(13) not null,
  TITLE          VARCHAR2(200),
  SUMMARY        VARCHAR2(2000),
  AUTHOR         VARCHAR2(200),
  DATE_PUBLISHED DATE,
  PAGE_COUNT     NUMBER
);
insert into books (ISBN, TITLE, SUMMARY, AUTHOR, DATE_PUBLISHED, PAGE_COUNT)
values ('0-596-00977-1', 'Oracle PL/SQL Programming', 'For the past ten years,  Oracle PL/SQL Programming has been the bestselling book on PL/SQL', 'Steven Feuerstein', to_date('01-09-2010 17:45:07', 'dd-mm-yyyy hh24:mi:ss'), 700);

insert into books (ISBN, TITLE, SUMMARY, AUTHOR, DATE_PUBLISHED, PAGE_COUNT)
values ('0-13-238182-6', 'Perl by Example', 'Perl by Example, Fourth Edition, is the easiest, most hands-on way to learn Perl.', 'Ellie Quigley', to_date('01-09-2010 18:29:56', 'dd-mm-yyyy hh24:mi:ss'), 600);
commit;

使用到的函数：
CREATE OR REPLACE FUNCTION booktitle (isbn_in IN VARCHAR2)
   RETURN VARCHAR2
IS
   l_isbn books.title%TYPE;
   CURSOR icur IS SELECT title FROM books WHERE isbn = isbn_in;
BEGIN
   OPEN icur;
   FETCH icur INTO l_isbn;
   CLOSE icur;
   RETURN l_isbn;
END;
/

callbook.pl的 perl脚本如下：
#!/usr/bin/perl

use strict;
use DBI qw(:sql_types);

# either make the connection or DIE
my $dbh = DBI->connect(
    'dbi:Oracle:db',
    'scott',
    'tiger',
    {
        RaiseError => 1,
        AutoCommit => 0
    }
) || die "Database connection not made: $DBI::errstr";

my $retval;

# make parse call to oracle, get statement handle
eval {
    my $func = $dbh->prepare(q{
        BEGIN
            :retval := booktitle(isbn_in => :bind1);
        END;
    });

# bind the parameters and execute
    $func->bind_param(":bind1", "0-13-238182-6");
    $func->bind_param_inout(":retval", \$retval, SQL_VARCHAR);
    $func->execute;

};

if( $@ ) {
    warn "Execution of stored procedure failed: $DBI::errstr\n";
    $dbh->rollback;
} else {
   print "Execution of stored procedure returned: $retval\n";
}
# don't forget to disconnect
$dbh->disconnect;

其中：   
    'dbi:Oracle:db',
    'scott',
    'tiger',
的db为数据库SID，scott和tiger为用户名和密码

启动数据库和监听器后，在oracle用户下执行perl脚本:
$perl callbook.pl