MadraghRua has asked for the wisdom of the Perl Monks concerning the following question:

Hi Folks,
I am working with an Oracle database that contains a number of long raw data fields. I don't have information on how the data is stored into these fields - I just know that what should come out would be some variation on ascii.

I know a little about Perl::DBI but am not a complete expert, although I have read the O'Reilly book and did RTFM. So assuming I can do a select query to get the long raw data, how might I go about extracting what is in there? Basically I'm looking for some tests to try and figure out how the data has been stored so that I can better understand how to do this extraction. Could someone please give me a few ideas so that I can at least get started here?

Thanks in advance for the wit and wisdom.

MadraghRua
yet another biologist hacking perl....

Replies are listed 'Best First'.
Re: Extracting long raw data from Oracle
by tadman (Prior) on Feb 13, 2001 at 06:16 UTC
    As far as SQL goes, as long as the data you provide to the INSERT call fits in the target columns, it will go in the table as planned. "Raw" fields, or BINARY, or BLOB or whatever you happen to call them, are just the same as your regular run-of-the-mill strings -- You still have to "escape" the content. 
          # $blob_a_data is a GIF, or other binary data

      $db->do("INSERT INTO blob_table (blob_a)
               VALUES (?)", {}, $blob_a_data);

      # Later on...

      my ($blob_a_fromdb) = 
        $db->selectrow_array("SELECT blob_a
                              FROM blob_table");

      # $blob_a_fromdb and $blob_a_data should be the same.

    [download]
    Or you can use $db->quote($blob_a_value) to force the proper conversion. DBI should "decode" any of the inserted data back into its original format for you when you use any of the fetch() or select()-type functions. Calls such as the $db->do() above do automatic quotation of your data for '?' identified parameters. Experiment with $db->quote() to see how it behaves, as it escapes any potentially dangerous characters such as '\n', or " ' ".

    It sounds too good to be true, but it isn't.
[reply]
[d/l]

      Careful. When you do an insert for LONG datatypes, you may need to use bind_param. Otherwise data over 255 or so bytes gets cut off when inserted.

      UPDATE: Some example code is below:

        my $sql = qq{ INSERT INTO Object (Key, LongData) VALUES (?, ?) };
  my $sth = $dbh->prepare( $sql ) or croak $DBI::errstr;

  $sth->bind_param(1, $key,      { TYPE=>DBI::SQL_INTEGER } );
  $sth->bind_param(2, $longdata, { TYPE=>DBI::SQL_LONGVARCHAR } );
[reply]
[d/l]
[select]
        to make this a little shorter you can add 
        use DBI qw(:sql_types);

        [download]
        then instead of { TYPE=>DBI::SQL_INTEGER } you can just type SQL_INTEGER

        --BigJoe

        Learn patience, you must.
        Young PerlMonk, craves Not these things.
        Use the source Luke.
[reply]
[d/l]
Re: Extracting long raw data from Oracle
by sierrathedog04 (Hermit) on Feb 13, 2001 at 10:56 UTC
    Does the long raw contain what was originally character data? Is it less than 32Kb long?

    If so then the easiest solution may be to write an Oracle stored procedure which uses a rawtochar function to convert the long raw to Oracle's varchar2 data type and then returns the varchar2. Have Perl call this stored procedure in order to retrieve the converted long raw values.

    Here is an implementation of a rawtochar function which works for long raws of less than 32Kb. I recall that Oracle's documentation also includes a stored procedure which can retrieve and convert long raws of any size.

[reply]

Back to Seekers of Perl Wisdom