Skip Menu |
 

Preferred bug tracker

Please visit the preferred bug tracker to report your issue.

This queue is for tickets about the DBD-Oracle CPAN distribution.

Report information
The Basics
Id: 32477
Status: resolved
Priority: 0/
Queue: DBD-Oracle

People
Owner: Nobody in particular
Requestors: p.bressan [...] expediacorporate.fr
Cc:
AdminCc:

Bug Information
Severity: Normal
Broken in: (no value)
Fixed in: 1.21



Subject: DBD::Oracle ; Insert large XML/LOB files into Oracle XMLType columns
Download (untitled) / with headers
text/plain 3.7k
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 text
2> 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 text
3> 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
From: p.bressan [...] expediacorporate.fr
Download (untitled) / with headers
text/plain 4.2k
I forgot to give some elements about my configuration : DBD-Oracle-1.20 Oracle client 10g Ubuntu Linux :) Patrick Bressan Le Lun. Jan. 21 10:54:34 2008, pbressan a écrit : Show quoted text
> 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 : >
> 1> Creation of a sample Oracle table
> > CREATE TABLE TEST_TABLE ( > id integer not null, > xmldata xmltype, > clobdata clob > ); >
> 2> 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 ### > ############################################################### >
> 3> 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
From: p.bressan [...] expediacorporate.fr
Download (untitled) / with headers
text/plain 616b
Another link about this problem. Thanks a lot for that hack. http://coding.derkeiler.com/Archive/Perl/perl.dbi.users/2005-04/msg00373.html So, one way it ca be performed is : After CLOB insertion [...] $dbh->do("UPDATE TEST_TABLE SET xmldata = SYS.XMLTYPE.CREATEXML(clobdata) where ID=1"); $dbh->commit; $query = "SELECT xmltype.getclobval(xmldata) AS MESSAGE FROM TEST_TABLE WHERE ID = 2"; $sth = $dbh->prepare($query); $sth->execute(); my $result = $sth->fetchall_arrayref; # XML large file has been correctly inserted. print "RES = ".$result->[0][0]."\n"; I you have any other suggestions, let me know.
Download (untitled) / with headers
text/plain 116b
Fixed in trunk and will be release in 1.21 Check the POD in the next release for 'Support for Insert of XMLType'


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.