Re: DBD::Oracle - is ora_lob_read mangling BLOB data?

Can you tell us which version of DBD::Oracle you are using.  There is 
quite a differance between versions when it comes to UTF8 handling.

Milo van der Leij wrote:
> I am in the progress of changing some code that uses DBD::Oracle to use the documented ora_lob_read function instead of the undocumented blob_read, and I suddenly hit the error "Malformed UTF-8 character (unexpected end of string) in length".
>
> As it turns out, Perl's length function returns the number of characters in the string.  If Perl thinks the string contains text and the text is in utf-8, then this can be different from the number of bytes in the string.  I regret to say that I am very unfamiliar with how Perl handles utf-8, but I am familiar enough with Oracle to see that treating BLOB data as character data is incorrect.
>
> I see the call to SvUTF8_on in ora_lob_read in Oracle.xs, I see how the value of NLS_LANG plays in, and I see how it affects both CLOB and BLOB data, so I understand why I am getting the result I am getting.  But I have some questions:
>
> (For someone who understands utf-8 in Perl:) Is there danger in binary data being treated as utf-8, other than the length function crashing?  Is there a workaround?  (I see "use bytes", but Perl 5.10 doc for the length function says "the internal encoding is variable, and the number of bytes usually meaningless" which scares me.)
>
> (For someone who understands the stuff in Oracle.xs:) Is it even possible to change ora_lob_read to only call SvUTF8_on for CLOB data and not for BLOB data?
>
> My test case is included below.  Connection info will need to be altered before it runs.  Commenting out the line with AL32UTF8 makes the problem go away.
>
> use strict;
> use DBI;
> use DBD::Oracle qw(:ora_types);
>
> $ENV{'NLS_LANG'} = '_.AL32UTF8';
> my $hDB = DBI->connect('dbi:Oracle:TEST', 'SCOTT', 'TIGER');
>
> my $hSQL = $hDB->prepare(qq{
> 	DECLARE
> 		b blob;
> 	BEGIN
> 		dbms_lob.createtemporary(b,true);
> 		dbms_lob.writeappend(b,256,utl_raw.xrange('00','FF'));
> 		:b := b;
> 	END;
> }, { ora_auto_lob => 0 });
>
> my $b;
> $hSQL->bind_param_inout( ':b', \$b, 0, { ora_type => ORA_BLOB } );
> $hSQL->execute;
>
> my $data = $hDB->ora_lob_read( $b, 1, 1024 );
> print "Read ", length($data), " bytes.\n";
>
>
>