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, Dec 22 '15 at 10:42
@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, Dec 22 '15 at 10:54
Do you get the same error for `$sth->bind_param_array(1,\@xmldocuments,{ ora_type => ORA_CLOB })`? — Wernfried Domscheit, Dec 22 '15 at 11:54
Did you try to set [ora_array_chunk_size](http://search.cpan.org/~pythian/DBD-Oracle-1.74/lib/DBD/Oracle.pm#ora_array_chunk_size)? — Wernfried Domscheit, Dec 22 '15 at 12:03
@WernfriedDomscheit Different error. LOBS not supported for array operation. Looks like I might need a different alternative. I can either do row by row processing or combine perl with system calls to run sqlloader. — user3676476, Dec 22 '15 at 12:46
As long as you use prepared statements the performance should be no issue, even when you do it row by row. — Wernfried Domscheit, Dec 22 '15 at 12:50
@WernfriedDomscheit I've tried that there. Can load about 1000 messages in 30s. More than good enough for my purposes. Thanks — user3676476, Dec 22 '15 at 12:59
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/98721/discussion-between-wernfried-domscheit-and-user3676476). — Wernfried Domscheit, Dec 22 '15 at 15:21

score 1 · Answer 1 · edited Dec 22 '15 at 15:19

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 Answers1