Bulk Insert into Oracle XMLTYPE Using Perl

Question

I'm using Perl DBD::Oracle to try and bulk insert an array of XML strings into an Oracle XMLTYPE column. I can get it to work if I bulk insert into a CLOB but when I try inserting into the XMLTYPE column via Strawberry Perl it crashes.

Has anyone being able to bulk insert into XMLTYPE from Perl?

Here are the two code snippets. One for CLOB and the second for 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"); }

Did you try 'INSERT INTO ' . $table . ' (XMLCONTENT) VALUES (XMLTYPE(?))' or INSERT INTO ' . $table . ' (XMLCONTENT) VALUES (XMLPARSE(CONTENT ? WELLFORMED))' and then send your XML as CLOB? — Wernfried Domscheit
@WernfriedDomscheit I'm getting an error from Oracle ora01461 can't bind a LONG only for insert into a LONG column. I think I definitely need the type deceleration — user3676476
Do you get the same error for $sth->bind_param_array(1,\@xmldocuments,{ ora_type => ORA_CLOB })? — Wernfried Domscheit

user3676476 user3676476 · Accepted Answer · 2015-12-22T13:01:41

I couldn't get the bulk bind to work with binary XMLTYPE. However row by row processing using the code below satisfies my requirements:

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"); 
}

Bulk Insert into Oracle XMLTYPE Using Perl

1 Answers