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

Hi
I am trying to insert a file into a long raw column in an Oracle table.
The program fails when the file exceeds 32512 bytes.
Does anyone know if there is a way around this?
Here is a code extract: 
 use DBI; 
 use DBD::Oracle;

 open(BLOB, "/utl_dump/test.csv");
 $bytes = 0;
 $bytes = read(BLOB, $buff, 500000);
 close(BLOB);


 my $insert_into_cache = 

 q{
   begin 
   insert into report_cache_master (:cache_id, :blob);
   end;
  };

 my $insert_stmt = $dbh->prepare($insert_into_cache);

 $attrib{'ora_type'} = $LONG_RAW_TYPE;
 $insert_stmt->bind_param(":cache_id", 7777);
 $insert_stmt->bind_param(":blob", $muff, \%attrib);

 $insert_stmt->execute;

[download]
I have tried using ORA_BLOB type but it still fails.
Thanks
Jonathan

Replies are listed 'Best First'.
Re: Inserting a Long Raw into an Oracle table fails above 32512 bytes
by Limbic~Region (Chancellor) on May 15, 2008 at 18:17 UTC
    jonnybuchanan,
    FYI, long raw is limited to 32,760 bytes when used within PL/SQL. That tripped me up once. Here is the code I use to insert BLOBs into Oracle. 
    #!/usr/bin/perl
use strict;
use warnings;
use DBD::Oracle qw(:ora_types);

# Set up any Oracle environment variables needed

my $dbh = DBI->connect('dbi:Oracle:db', 'user', 'pass') or die $DBI::e
+rrstr;

# Get data from file
open(my $fh, '<', 'some.file') or die "Unable to open 'some.file' for 
+reading: $!";
binmode $fh;
my $blob;
{
    local $/ = undef;
    $blob = <$fh>;
}

my $sth = $dbh->prepare("UPDATE table SET col = ?") or die $dbh->errst
+r;
$sth->bind_param(1, $blob, {ora_type => ORA_BLOB});
$sth->execute() or die $dbh->errstr;

    [download]
    I have never had a problem with it.

    Cheers - L~R

[reply]
[d/l]
      Thanks. This works for me when I change ORA_BLOB to a long raw. Thanks again. Jonathan.
[reply]
Re: Inserting a Long Raw into an Oracle table fails above 32512 bytes
by moritz (Cardinal) on May 15, 2008 at 15:42 UTC
    See the section Handling BLOB / LONG / Memo Fields in the DBI docs
    In this situation, the value of the $h->{LongReadLen} attribute is used to determine how much buffer space to allocate when fetching such fields. The $h->{LongTruncOk} attribute is used to determine how to behave if a fetched value can’t fit into the buffer.
[reply]
[d/l]
[select]

      From the same document:

      The LongReadLen attribute only relates to fetching and reading long values; it is not involved in inserting or updating them.
[reply]

Back to Seekers of Perl Wisdom