到: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
# tar zxvf DBI-1.613.tar.gz
# cd DBI-1.613
# perl Makefile.PL
#make
#make install
#cd
# tar zxvf DBD-Oracle-1.21.tar.gz
# cd DBD-Oracle-1.21
在oracle的HOME目录下的.bash_profile最后加上export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/jdbc/lib:$LD_LIBRARY_PATH
# . /home/db/oracle/.bash_profile
# perl Makefile.PL
#make
#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
下载DBI-1.6
和到:http://search.cpan.org/~pythian/DBD-Oracle-1.21/
下载DBD-Oracle-1.21
# tar zxvf DBI-1.613.tar.gz
# cd DBI-1.613
# perl Makefile.PL
#make
#make install
#cd
# tar zxvf DBD-Oracle-1.21.tar.gz
# cd DBD-Oracle-1.21
在oracle的HOME目录下的.bash_profile最后加上export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/jdbc/lib:$LD_LIBRARY_PATH
# . /home/db/oracle/.bash_profile
# perl Makefile.PL
#make
#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