oracle RAW data type and dbi

[dcruncher4]

we are storing binary data in an oracle table as RAW data type. Oracle
converts RAW data type into hex values at the time of storing. When I
view the data thru sqlplus I see it as hex.

Obviously it has to be converted back to original value when we fetch it.
It seems it is not happening.

use DBD::Oracle qwora_types);
$o_sth->bind_col(1,\$binvalue,{ ora_type => ORA_RAW } ) ;

when I do print $binvalue, I see it as hex only? Why?
Curiously Pro-C converts that to original form.

What am I missing?

Oracle DBD is 1.17

 

[Heinrich Mislik]

we are storing binary data in an oracle table as RAW data type. Oracle
converts RAW data type into hex values at the time of storing. When I
view the data thru sqlplus I see it as hex.

Obviously it has to be converted back to original value when we fetch it.
It seems it is not happening.

use DBD::Oracle qwora_types);
$o_sth->bind_col(1,\$binvalue,{ ora_type => ORA_RAW } ) ;

when I do print $binvalue, I see it as hex only? Why?
Curiously Pro-C converts that to original form.

perldoc DBD::Oralce does not mention bin_col anywhere. It seems, that ora_types can only be used with bin_param.

Here is a crazy workaround that seems to work:

use DBD::Oracle 'ra_types';

my $sth=$dbh->prepare('begin select my_raw_column into ? from some_table;end;');
$sth->bind_param_inout(1,\my $val,100,{ora_type => ORA_RAW});
$sth->execute;
print $val'

Cheers

Heinrich

 

[Peter J. Holzer]

No. It converts the raw value to hex when you *read* it into a character
variable.

That's because sqlplus reads all columns as character types (it has to
print them, after all).

perldoc DBD::Oralce does not mention bin_col anywhere. It seems, that
ora_types can only be used with bin_param.

perldoc DBI does specifically mention { ora_type => 97 } as an example
for a driver-specific attribute for bind_col.

However, it also says that the second parameter can be undef, which
doesn't seem to work, either:

Can't DBI::st=HASH(0xa5d52e0)->bind_col(2, undef,...), need a reference
to a scalar at ./raw_test line 23.

so it's probably not quite up to date.

hp