Perl/DBI -从Postgres插入行到Oracle表
Perl/DBI -从Postgres插入行到Oracle表
提问于 2014-08-14 17:06:55
我正在编写一个脚本，它将读取postgresql表中的数据并将其插入到oracle表中，下面是我的脚本：
#!/usr/local/bin/perl
use strict;
use DBI;
use warnings FATAL => qw(all);
my $pgh = pgh(); # connect to postgres
my $ora = ora(); # connect to oracle
my @rows;
my $rows =[] ;
my $placeholders = join ", ", ("?") x @rows;
my $sth = $pgh->prepare('SELECT * FROM "Employees"');
$sth->execute();
while (@rows = $sth->fetchrow_array()) {
$ora->do("INSERT INTO employees VALUES($placeholders)");
}
#connect to postgres
sub pgh {
my $dsn = 'DBI:Pg:dbname=northwind;host=localhost';
my $user = 'postgres';
my $pwd = 'postgres';
my $pgh = DBI -> connect($dsn,$user,$pwd,{'RaiseError' => 1});
return $pgh;
}
#connect to oracle
sub ora {
my $dsn = 'dbi:Oracle:host=localhost;sid=orcl';
my $user = 'nwind';
my $pwd = 'nwind';
my $ora = DBI -> connect($dsn,$user,$pwd,{'RaiseError' => 1});
return $ora;
}我得到了以下错误：
DBD::Oracle::db do failed: ORA-00936: missing expression (DBD ERROR: error possibly near <*> indicator at char 29 in 'INSERT INTO employees VALUES(<*>)') [for Statement "INSERT INTO employees VALUES()"] at /usr/share/perlproj/cgi-bin/scripts/nwind_pg2ora.pl line 19.请帮我把代码正确无误。非常感谢!！托尼亚。
回答 3
Stack Overflow用户
回答已采纳
发布于 2014-08-24 16:10:13
请参阅DBD::Oracle的文档，您必须绑定BLOB的参数值，如：
use DBD::Oracle qw(:ora_types);
$sth->bind_param($idx, $value, { ora_type=>ORA_BLOB, ora_field=>'PHOTO' });Stack Overflow用户
发布于 2014-08-14 19:50:01
my @rows;
my $rows =[] ;
my $sth = $pgh->prepare('SELECT * FROM "Employees"');
$sth->execute();
while (@rows = $sth->fetchrow_array()) {
my $placeholders = join ", ", ("?") x @rows;
$ora->do("INSERT INTO employees VALUES($placeholders)");
}您将加入一个空的@行来创建一个空的$placeholders。在while循环中，在do()之前执行连接。
Stack Overflow用户
发布于 2014-08-14 20:12:26
下面延迟创建一个语句句柄，用于根据返回记录中的列数插入Oracle数据库。
然后将这些列值插入数据库，因此很明显，我们假设表结构是相同的：
use strict;
use DBI;
use warnings FATAL => qw(all);
my $pgh = pgh(); # connect to postgres
my $ora = ora(); # connect to oracle
my $sth = $pgh->prepare('SELECT * FROM "Employees"');
$sth->execute();
my $sth_insert;
while (my @cols = $sth->fetchrow_array()) {
$sth_insert ||= do {
my $placeholders = join ", ", ("?") x @cols;
$ora->prepare("INSERT INTO employees VALUES ($placeholders)");
};
$sth_insert->execute(@cols);
}页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/25313645
复制相关文章
相似问题
腾讯云开发者
