使用Perl大容量插入Oracle XMLTYPE

Question

我正在使用Perl DBD:：Oracle尝试将XML字符串数组大容量插入Oracle XMLTYPE列中。如果我批量插入到CLOB中，我可以让它工作，但当我尝试通过Strawberry Perl插入XMLTYPE列时，它会崩溃。

有人能够从Perl批量插入XMLTYPE吗？

以下是两个代码片段。一个用于CLOB，另一个用于XMLTYPE。。。。

sub save_xml {
$log->write("Inserting XML messages into table:$table, in $mode: mode"); my @status; my $sql='INSERT INTO ' . $table . ' (XMLCONTENT) VALUES (?)'; my $sth = $dbh->prepare_cached($sql) || die "Cannot prepare statement: $DBI::errstr"; $sth->bind_param_array(1,@xmldocuments) || die "Cannot bind parameter array: $DBI::errstr"; $sth->execute_array({ArrayTupleStatus=>@status}) || die "Cannot bulk insert into table: $table: $DBI::errstr"; $log->write("Inserted $status rows into table: $table"); }

sub save_xml {
$log->write("Inserting XML messages into table:$table, in $mode: mode"); my @status; my $sql='INSERT INTO ' . $table . ' (XMLCONTENT) VALUES (?)'; my $sth = $dbh->prepare_cached($sql) || die "Cannot prepare statement: $DBI::errstr"; $sth->bind_param_array(1,@xmldocuments,{ ora_type => ORA_XMLTYPE }) || die "Cannot bind parameter array: $DBI::errstr"; $sth->execute_array({ArrayTupleStatus=>@status}) || die "Cannot bulk insert into table: $table: $DBI::errstr"; $log->write("Inserted $status rows into table: $table"); }

Answer 1

我无法使用二进制XMLTYPE进行批量绑定。然而，使用以下代码逐行处理满足了我的要求：

sub save_xml{ 
   my ($xml) = @_; 
   $log->write("Inserting XML message into table:$table, in $mode mode"); 
   my $sql='INSERT INTO ' . $table . ' (XMLCONTENT) VALUES (:xml)'; 
   my $sth = $dbh->prepare_cached($sql); 
   if ( $mode eq "BINARY" ) { 
       $sth-> bind_param(":xml", $xml, { ora_type => ORA_XMLTYPE }); 
   } else { 
       $sth-> bind_param(":xml", $xml); 
   } 
   $sth->execute() || die "Error whilst inserting into table: $table: $DBI::errstr"; 
   $log->write("Insert into table:$table successful"); 
}