oracle RAW data type and dbi

Discussion in 'Perl Misc' started by dcruncher4@aim.com, Feb 12, 2008.

  1. Guest

    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:)ora_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
    , Feb 12, 2008
    #1
    1. Advertising

  2. In article <>, 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:)ora_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 ':eek: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
    Heinrich Mislik, Feb 13, 2008
    #2
    1. Advertising

  3. On 2008-02-13 11:25, Heinrich Mislik <> wrote:
    > In article <>, 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:)ora_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
    Peter J. Holzer, Feb 13, 2008
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.

Share This Page