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
– Wernfried Domscheit, Commented Dec 22, 2015 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
– user3676476, Commented Dec 22, 2015 at 10:54
Do you get the same error for $sth->bind_param_array(1,\@xmldocuments,{ ora_type => ORA_CLOB })? — Wernfried Domscheit
– Wernfried Domscheit, Commented Dec 22, 2015 at 11:54

Wernfried Domscheit · Accepted Answer · 2015-12-22 15:19:49Z

1

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

edited Dec 22, 2015 at 15:19

Wernfried Domscheit

60.4k10 gold badges93 silver badges132 bronze badges

answered Dec 22, 2015 at 13:01

user3676476

3232 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Bulk Insert into Oracle XMLTYPE Using Perl

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related