perl在创建oracle数据库时故障,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

[iyunv@localhost ~]# tar zxvf DBI-1.613.tar.gz

[iyunv@localhost ~]# cd DBI-1.613

[iyunv@localhost DBI-1.613]# perl Makefile.PL

[iyunv@localhost DBI-1.613]#make

[iyunv@localhost DBI-1.613]#make install

[iyunv@localhost ~]#cd

[iyunv@localhost ~]# tar zxvf DBD-Oracle-1.21.tar.gz

[iyunv@localhost ~]# cd DBD-Oracle-1.21

在oracle的HOME目录下的.bash_profile最后加上export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/jdbc/lib:$LD_LIBRARY_PATH

[iyunv@localhost DBD-Oracle-1.21]# . /home/db/oracle/.bash_profile

[iyunv@localhost DBD-Oracle-1.21]# perl Makefile.PL

[iyunv@localhost DBD-Oracle-1.21]#make

[iyunv@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