I'm banging my head against a problem that I know others have encountered in the past few years judging by posts to the DBI newgroup archive and elsewhere, but I have yet to see a solution for it. I'm trying to insert a serialized object into a BLOB (Binary Large Object) column using the Storable module, which was discussed recently here and for which I had a working albeit awkward solution. At perrin's suggestion I've tried working with BLOBs again, only to run into the problem that caused me to explore other options two years ago. The exact error is:
DBD::Oracle::st execute failed: ORA-01465: invalid hex number (DBD ERR
+OR: OCIStmtExecute)

[download]
which according to Oracle docs means that the "ROWID contains invalid characters", a problem that occurs sometimes apparently when doing SQL updates. The environment is Solaris 2.7, Oracle 8.1.7 and DBI 1.30.

The table structure is very simple: 

Name   Null?    Type
----   -----    ----
ID     NOT NULL VARCHAR2(2)
DATA   NOT NULL BLOB

[download]
Here's the test script I'm using to elicit the error: 

use strict;
use warnings 'all';
use Storable qw(nfreeze, thaw);
use DBD::Oracle qw(:ora_types);

my $id = shift;
my $thingy = 'Oh the joy' x 100; # dummy data, no effect on results

my %types = (ora_types => ORA_BLOB, ora_field => 'data');

my $data = eval { Storable::nfreeze(\$thingy) };
die "$@\n$data\n" if !$data || $@ ne '';

my $insert_stmt = "insert into blobtest values ('$id', ?)";

my $sth = $dbh->prepare($insert_stmt);
$sth->bind_param(1, $data, \%types);
$sth->execute();

[download]
Now, it does work to translate the serialized data into hexadecimal using unpack, but this undermines the whole point of writing out to a BLOB!

Any clues would be greatly appreciated.

In reply to BLOBs and error ORA-01465 by djantzen

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.