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

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.

Replies are listed 'Best First'.
Re: BLOBs and error ORA-01465
by panix (Monk) on Jul 26, 2002 at 00:31 UTC
    Replace the ora_types in bind_param() with ora_type.

    DBD::Oracle is probably being a little harsh silently discarding bad arguments (it's an easy enough mistake to make as well, ora_type/ora_types are both valid in other places).

[reply]

      Aargh. Well you're right panix. The irritating bit is that I tried doing this before, and that I've used ora_types to bind to CLOB fields without any problem.

      What I've found then is that the "same" error arises under two circumstances: 1) if I use ora_types instead of ora_type with BLOB fields; and 2) if I use our in-house DBI wrapper module with BLOBs. The persistence of the error when testing under the latter is the reason why I wrote off the ora_type business as a non-factor here. All of which makes for a gut-wrenching lesson in the importance of a rigorous breakdown of possible causal relations over the span of multiple tests and even theories.

      Anyway, thank you for your work panix++

      Update:I believe I've narrowed the problem down to the method DBD::_::do defined in DBI.pm, which our DBI wrapper module relies upon in most cases: 

      sub do {
    my($dbh, $statement, $attr, @params) = @_;
    my $sth = $dbh->prepare($statement, $attr) or return undef;
    $sth->execute(@params) or return undef;
    my $rows = $sth->rows;
    ($rows == 0) ? "0E0" : $rows;
}

      [download]
      And this is line 3383 DBI.pm 
      If any arguments are given, then C<execute> will effectively call
L</bind_param> for each value before executing the statement.  Values
bound in this way are usually treated as C<SQL_VARCHAR> types unless
the driver can determine the correct type (which is rare), or unless
C<bind_param> (or C<bind_param_inout>) has already been used to
specify the type.

      [download]
      Between the code and this comment, and the fact that I can insert, update, etc., values in CLOB fields using do but must call bind_param explicitly when working with BLOB fields leads me to believe that execute is ignoring the attributes bound in prepare and is successfully able to ascertain on its own the type of the CLOB data but not BLOB data. But now I'm wondering why that method takes attributes at all if they aren't going to be used.

[reply]
[d/l]
[select]
Re: BLOBs and error ORA-01465
by Ryszard (Priest) on Jul 25, 2002 at 18:11 UTC
    To paraphrase a use group post i found with google:

    You can't insert a blob object(or any large object) using this standard statement.

    You may use pl/sql block with dbms_lob package to insert large objects or write codes to do so(java with jdbc or perl with dbi)...

    You can search technet.oracle.com for the dbms_lob package.. For sample codes of java with jdbc to insert large object, you can find it under the $ORACLE_HOME/jdbc/ if you install O8i with jdbc drivers..(the sample codes also available in the technet)

    For perl, if you install the DBI and DBD for oracle, pls read the readme file and you will find it.

    You can find the original post here .

    Tim Bunce also mentions it wont work with ActiveStates version DBD::Oracle here (Which isnt going to be much help to you.)

    I recently compiled perl 5.6.1 with the latest version of DBD/DBI (on Solaris 2.6) and the only way I could get it to compile was force it to compile as Oracle 7. I wonder if your problem may be related to the compilation of your DBD?

[reply]
      (NB: fever and I are working on the same project)

      We are using vanilla perl 5.6.1 and we were able to successfully build DBD::Oracle against Oracle 8.1.7 (on Solaris 7). We've been using this system successfully for all kinds of interaction between perl and Oracle including pushing CLOBs in and out of Oracle, using simple insert/select statements. DBD::Oracle is designed, according to it's documentations, to allow you to do this with both BLOBs and CLOBs.

      So the problem and frustration we've been facing comes from the fact that DBD::Oracle doesn't appear to be behaving for us according to it's documentation,
[reply]

Back to Seekers of Perl Wisdom