Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Perl > Perl Misc > oracle RAW data type and dbi

Reply
Thread Tools

oracle RAW data type and dbi

 
 
dcruncher4@aim.com
Guest
Posts: n/a
 
      02-12-2008
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 qw(ra_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

 
Reply With Quote
 
 
 
 
Heinrich Mislik
Guest
Posts: n/a
 
      02-13-2008
In article <(E-Mail Removed)>, http://www.velocityreviews.com/forums/(E-Mail Removed) says...

>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 qw(ra_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

--
Heinrich Mislik
Zentraler Informatikdienst der Universitaet Wien
A-1010 Wien, Universitaetsstrasse 7
Tel.: (+43 1) 4277-14056, Fax: (+43 1) 4277-9140

 
Reply With Quote
 
 
 
 
Peter J. Holzer
Guest
Posts: n/a
 
      02-13-2008
On 2008-02-13 11:25, Heinrich Mislik <(E-Mail Removed)> wrote:
> In article <(E-Mail Removed)>, (E-Mail Removed) says...
>>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.


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

>>When I view the data thru sqlplus I see it as hex.


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


>>Obviously it has to be converted back to original value when we fetch it.
>>It seems it is not happening.
>>
>>use DBD::Oracle qw(ra_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.


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

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem with dbi,odbc module and Oracle 9.2 - suffixed "L" with number data type dananrg@yahoo.com Python 5 03-07-2006 09:38 PM
install_driver(Oracle) failed: Can't load 'C:/Perl/site/lib/auto/DBD/Oracle/Oracle.dll' for module DBD::Oracle: load_file:The specified procedure could not be found at C:/Perl/lib/DynaLoader.pm line 230. Feyruz Perl Misc 4 10-14-2005 06:47 PM
how to load a picture to an oracle database with theLoad a pictureto Oracle with DBI perl module Vincent Le-Texier Perl Misc 1 12-03-2004 03:29 PM
DBI and DBI::Oracle packages configuration ulloa Perl 1 07-22-2004 05:52 PM
Mason, DBI, and DBI::Pg Asby Perl Misc 0 07-24-2003 09:04 PM



Advertisments