ORA-01465 invalid hex number

Cause: In an UPDATE statement following a SELECT FOR UPDATE, part of the ROWID contains invalid characters. ROWID must be expressed in the proper and expected format for ROWID and within quotes.

Action: Enter the ROWID just as it was returned in the SELECT FOR UPDATE.

I hope that helps,

Brent
-- Yeah, I'm a Delt.

I expect you need to make use of the bind_param method in order to tell DBI what kind of data to expect. See BLOBs and error ORA-01465 for more.

OK, here is it. It's a bit mixed up as I've been trying different things.

Getting the impression this doesn't work though, despite what the documentation says.

# perform the connection using the Oracle driver
my $dbh = DBI->connect("dbi:Oracle:$database", "$username", "$password
+", {
    PrintError => 0,
    RaiseError => 0
}) or die "Can't connect to the database: $DBI::errstr\n";

print "INFO: connected to $database OK, updating picture_table with $p
+hoto\n";

# prepare update statement
print "INFO: reading $photo to blob\n";
open (BLOB, "<$photo") or die "Can't open $photo: $!";
binmode BLOB;
{
    local($/) = undef;
    $blob = <BLOB>;
}
close (BLOB);

$quotedblob = $dbh->quote($blob);

# THIS ALL WORKS BAR THE BLOB UPLOAD...!!!!
#my $sth = $dbh->prepare ("update ssi_pri_photos set recorded = sysdat
+e, recorded_by = 'David', photograph = '$blob' where person_number = 
+'$person_id'");
#my $sth = $dbh->prepare ("select recorded_by from ssi_pri_photos");
my $sth = $dbh->prepare ("update ssi_pri_photos set recorded = sysdate
+, recorded_by = 'David', photograph = ? where person_number = $person
+_id");

$sth->bind_param(1, $quotedblob, SQL_LONGVARBINARY);

# execute the statement in the database
$sth->execute or die "Can't execute SQL statement: $DBI::errstr\n";
[download]

Thanks to everyone for the suggestions so far, by the way.

Thanks,
David

First, BLOB support with Oracle and DBI definitely works, and many people have used it successfully.

I see a few things in your code. First, don't quote $blob. Second, you have to use bind variables when working with BLOBs. Don't try to just put $blob into the statement as a quoted string. Finally, "SQL_LONGVARBINARY" looks fishy to me. I have always used code that looks like this:

$sth->bind_param(1, $serialized, {ora_type => ORA_BLOB, ora_field => '
+data'
});
[download]