| Subject: | DBD::Oracle ; Insert large XML/LOB files into Oracle XMLType columns |
Hello Tim,
I have met a "known" problem regarding insertion of large XML files
into Oracle XMLType columns. The DBD::Oracle POD recommends to use
buffer LOB technic, that's my understanding.
Here is a short code sample :
Show quoted text
1> Creation of a sample Oracle table
CREATE TABLE TEST_TABLE (
id integer not null,
xmldata xmltype,
clobdata clob
);
Show quoted text2> A sample perl code that demonstrates
there is no problem with "clob" column type.
#!/usr/bin/perl -w
use strict;
use DBI qw(:sql_types);
use DBD::Oracle qw(:ora_types);
my $dbh = undef;
eval {
$dbh = DBI->connect(
'dbi:Oracle:MY_SID',
'MY_LOGIN',
'MY_PASSWORD',
{ RaiseError => 1, AutoCommit => 0 });
$dbh->{LongReadLen} = 512 * 1024;
$dbh->{LongTruncOk} = 1;
};
die($@) if ($@);
my $query = "
INSERT INTO TEST_TABLE ( ID, clobdata ) VALUES ( 1, EMPTY_CLOB() ) ";
my $sth = $dbh->prepare($query) or print "Prepare Error: $DBI::errstr";
$sth->execute() or print "Execute Error: $DBI::errstr";
$query = "SELECT clobdata FROM TEST_TABLE WHERE ID = 1 ";
$sth = $dbh->prepare($query, { ora_auto_lob => 0 })
or print "Prepare Error: $DBI::errstr";
$sth->execute()
or print "Execute Error: $DBI::errstr";
my ($LOB) = $sth->fetchrow_array()
or print "Fetch Error: $DBI::errstr";
my $fileName = ___PATH_TO_LARGE_XML_FILE___
# BEGIN WRITING CHAR_DATA COLUMN
my $offset = 1; # Offsets start at 1, not 0
my $length = 0;
my $buffer = '';
my $chunk_size = 4096;
open CHAR_FH, $fileName or die "open";
while( $length = read( CHAR_FH, $buffer, $chunk_size ) ) {
$dbh->ora_lob_write($LOB, $offset, $buffer);
$offset += $length;
}
close CHAR_FH;
$dbh->disconnect;
###############################################################
### The large XML data is well inserted into clobdata colum ###
###############################################################
Show quoted text3> Trying now to do the same thing in the XMLType column
#!/usr/bin/perl -w
use strict;
use DBI qw(:sql_types);
use DBD::Oracle qw(:ora_types);
my $dbh = undef;
eval {
$dbh = DBI->connect(
'dbi:Oracle:MY_SID',
'MY_LOGIN',
'MY_PASSWORD',
{ RaiseError => 1, AutoCommit => 0 });
$dbh->{LongReadLen} = 512 * 1024;
$dbh->{LongTruncOk} = 1;
};
die($@) if ($@);
my $query = "
INSERT INTO TEST_TABLE ( ID, xmldata ) VALUES ( 2, EMPTY_CLOB() ) ";
my $sth = $dbh->prepare($query) or print "Prepare Error: $DBI::errstr";
$sth->execute() or print "Execute Error: $DBI::errstr";
$query = "SELECT xmldata FROM TEST_TABLE WHERE ID = 2 ";
$sth = $dbh->prepare($query, { ora_auto_lob => 0 })
or print "Prepare Error: $DBI::errstr";
$sth->execute()
or print "Execute Error: $DBI::errstr";
my ($LOB) = $sth->fetchrow_array()
or print "Fetch Error: $DBI::errstr";
my $fileName = ___PATH_TO_LARGE_XML_FILE___
# BEGIN WRITING CHAR_DATA COLUMN
my $offset = 1; # Offsets start at 1, not 0
my $length = 0;
my $buffer = '';
my $chunk_size = 4096;
open CHAR_FH, $fileName or die "open";
while( $length = read( CHAR_FH, $buffer, $chunk_size ) ) {
$dbh->ora_lob_write($LOB, $offset, $buffer);
$offset += $length;
}
close CHAR_FH;
$dbh->disconnect;
### I get an ORA-00932 error ; Inconsistent data types.
DBD::Oracle::st execute failed: ORA-00932: types de données incohérents
; attendu : NUMBER ; obtenu : CLOB (DBD ERROR: error possibly near <*>
indicator at char 62 in '
INSERT INTO TEST_TABLE ( ID, xmldata )
VALUES ( 2, <*>EMPTY_CLOB() ) ')
I have read some posts regarding this problem over the internet :
http://www.mail-archive.com/dbi-users@perl.org/msg29344.html
http://groups.google.com/group/perl.dbi.users/msg/289b15ee609d3e1d
I'm not sure if there are any hack I can use for this.
Should I use OCI::Oracle ? Use Oracle cursors ?
Thanks in advance for any help.
Patrick Bressan