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: 71810
Status: open
Priority: 0/
Queue: DBD-Oracle

People
Owner: Nobody in particular
Requestors: smosin [...] mail.ru
Cc:
AdminCc:

Bug Information
Severity: Important
Broken in: (no value)
Fixed in: (no value)



Subject: ora_type => ORA_RAW doesn't work for select
Download (untitled) / with headers
text/plain 297b
Hello, I need to select raw data from Oracle but it appeared that it doesn't work for SELECT statements. The workaround is to use PL/SQL procedures and bind_param_inout instead of SELECT and bind_col To reproduce the issue change connect data at the beginning of the raw_test.pl and run test.
Subject: raw_test.pl
Download raw_test.pl
text/x-perl 2.2k
# Sergey Mosin smosin @ mail . ru use strict; use warnings; use DBI; use DBD::Oracle qw/:ora_types/; my ($connect_string, $user, $password) = ('ai', 'scott', 'tiger'); my $raw_inp = pack('H*', '0123456789abcdef'); # input data my $raw_out; ; # output data my $sql; # SQL statements # Connect my $dbh = DBI->connect('DBI:Oracle:' . $connect_string, $user, $password , {RaiseError =>1, PrintWarn => 1}) or die 'Can\'t connect'; # Create table with only one raw column $dbh->do(<<EOF); # Create table DECLARE no_table EXCEPTION; PRAGMA exception_init(no_table , -942); -- ORA-00942: table or view does not exist BEGIN BEGIN execute immediate 'Drop table raw_test'; EXCEPTION WHEN no_table THEN null; -- inore END; execute immediate 'Create table raw_test(r raw(1000))'; END; EOF ; $sql = $dbh->prepare( <<EOF ); # Insert RAW data into the RAW_TEST table BEGIN insert into raw_test(r) values (:raw_inp); END; EOF ; $sql->bind_param_inout(':raw_inp', \$raw_inp, 100, { ora_type => ORA_RAW, StrictlyTyped => 1 }); $sql->execute; $sql->finish; $sql = $dbh->prepare( <<EOF ); # get raw data back as PL/SQL bind variable BEGIN select r into :raw_out from raw_test; END; EOF ; $sql->bind_param_inout(':raw_out', \$raw_out, 100, { ora_type => ORA_RAW, StrictlyTyped => 1 }); $sql->execute; $sql->finish; print STDOUT unpack('H*', $raw_inp), "\n"; print STDOUT unpack('H*', $raw_out), "\n\n"; $sql = $dbh->prepare('Select r from raw_test'); # get raw data back as column of SELECT statement $sql->execute; $sql->bind_col(1, undef, { ora_type => ORA_RAW, StrictlyTyped => 1 }); $raw_out = ($sql->fetch)->[0]; $sql->finish; print STDOUT unpack('H*', $raw_inp), "\n"; print STDOUT unpack('H*', $raw_out), "\n\n"; $sql = $dbh->prepare('Select r from raw_test'); # The same but WRONG ora_type just to show that it is ignored $sql->execute; $sql->bind_col(1, undef, { ora_type => 1E9, StrictlyTyped => 1 }); # wrong value for ora_type $raw_out = ($sql->fetch)->[0]; $sql->finish; print STDOUT unpack('H*', $raw_inp), "\n"; print STDOUT unpack('H*', $raw_out), "\n\n";
Download (untitled) / with headers
text/plain 604b
On Thu Oct 20 08:32:53 2011, smosin wrote: Show quoted text
> Hello, > > I need to select raw data from Oracle but it appeared that it doesn't > work for SELECT statements. > > The workaround is to use PL/SQL procedures and bind_param_inout instead > of SELECT and bind_col > > To reproduce the issue change connect data at the beginning of the > raw_test.pl and run test. >
What was the output of running your test script for you. I get: 0123456789abcdef 0123456789abcdef 0123456789abcdef 30313233343536373839414243444546 0123456789abcdef 30313233343536373839414243444546 Martin -- Martin J. Evans Wetherby, UK
Download (untitled) / with headers
text/plain 760b
On Thu Oct 20 14:12:27 2011, MJEVANS wrote: Show quoted text
> On Thu Oct 20 08:32:53 2011, smosin wrote:
> > Hello, > > > > I need to select raw data from Oracle but it appeared that it doesn't > > work for SELECT statements. > > > > The workaround is to use PL/SQL procedures and bind_param_inout instead > > of SELECT and bind_col > > > > To reproduce the issue change connect data at the beginning of the > > raw_test.pl and run test. > >
> > What was the output of running your test script for you. I get: > > 0123456789abcdef > 0123456789abcdef > > 0123456789abcdef > 30313233343536373839414243444546 > > 0123456789abcdef > 30313233343536373839414243444546 > > Martin
BTW, StrictlyTyped only works for numbers right now. Martin -- Martin J. Evans Wetherby, UK
From: smosin [...] mail.ru
Download (untitled) / with headers
text/plain 426b
Martin, I see the same output as you. I can insert raw data as RAW using PL/SQL block but when I get it back using select I have RAW data converted to hex string. Oracle does implicit conversion from RAW to VARCHAR2 as it requested by receiver using rawtohex. Of course I can convert it back to RAW using pack('H*', $raw_out) but it means that we send over network twice more data than we really need. Regards, Sergey
Download (untitled) / with headers
text/plain 1.6k
On Fri Oct 21 03:08:37 2011, smosin wrote: Show quoted text
> Martin, > > I see the same output as you. > > I can insert raw data as RAW using PL/SQL block but when I get it back > using select I have RAW data converted to hex string. > > Oracle does implicit conversion from RAW to VARCHAR2 as it requested by > receiver using rawtohex. > > Of course I can convert it back to RAW using pack('H*', $raw_out) but > it means that we send over network twice more data than we really need. > > Regards, > Sergey >
Sergey, we are in a world of pain with this. The call to OCIDefineByPos is called when prepare is called and it is that which sets the bind type in Oracle. When bind_col is called it is after the column is actually bound in DBD::Oracle so it is too late. This is just the way DBD::Oracle has been written. StrictlyTyped and DiscardString attributes are also set in bind_col but they are not applied until after the data is retrieved so they work but the type passed to bind_col is ignored unless those attributes are set and the type is a numeric type i.e., bind_col type and attributes only affect the data after it has been retrieved. It would seem this is arguably a design flaw in DBD::Oracle but I suspect it would be a major change to make types passed to bind_col to take affect BEFORE the columns are bound. BTW, ora_type => ORA_RAW in your call to bind_col does nothing anyway as it should be TYPE => ORA_RAW but it won't help anyway. I suspect you will have to live with this as it is as I'm certainly not going to reengineer this. BTW, I didn't write that code, I only help maintain it right now. May be someone else will have a different view on this. Martin -- Martin J. Evans Wetherby, UK
From: smosin [...] mail.ru
Download (untitled) / with headers
text/plain 1.3k
Show quoted text
> Sergey, we are in a world of pain with this. The call to OCIDefineByPos > is called when prepare is called and it is that which sets the bind type > in Oracle. When bind_col is called it is after the column is actually > bound in DBD::Oracle so it is too late. This is just the way DBD::Oracle > has been written. > > StrictlyTyped and DiscardString attributes are also set in bind_col but > they are not applied until after the data is retrieved so they work but > the type passed to bind_col is ignored unless those attributes are set > and the type is a numeric type i.e., bind_col type and attributes only > affect the data after it has been retrieved. > > It would seem this is arguably a design flaw in DBD::Oracle but I > suspect it would be a major change to make types passed to bind_col to > take affect BEFORE the columns are bound. > > BTW, ora_type => ORA_RAW in your call to bind_col does nothing anyway as > it should be TYPE => ORA_RAW but it won't help anyway. > > I suspect you will have to live with this as it is as I'm certainly not > going to reengineer this. BTW, I didn't write that code, I only help > maintain it right now. May be someone else will have a different view on > this. > > Martin
Martin, thank you for detailed explanation. I understand that it is not easy to fix. In any case we have a workaround. We will work with hex strings. Thank you for maintaining the code. Sergey


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.