This is part of The Pile, a partial archive of some open source mailing lists and newsgroups.
To: dbi-users mailing list <dbi-users@isc.org>
Date: Tue, 8 Aug 2000 19:56:51 -0400 (EDT)
From: Lee Feigenbaum <lee@actifunds.com>
Subject: Oracle: Inserting BLOBs (Bug?)
I suspect that other people _must_ have experienced the
following problem before, but I could not find reference to
it in the DBD::Oracle docs or in the mailing list
archive. I'm looking for both confirmation of the problem
and possible workarounds / solutions.
I'm runnning Oracle 8.16 on Redhat Linux 6.1 and using
DBD::Oracle v1.03. I know that 1.06 is out, but looking
through the Changes file the changes between 1.03 and 1.06
did not seem to address this issue. I will welcome
information to the contrary.
In any case, here's the problem. When I connect to Oracle
with AutoCommit set explicitly to 1, I expect that an INSERT
statement should be committed completely immediately, and
hence should not block other database operations. And this
seems to be the case all the time _except_ when I'm
inserting data into a BLOB column.
That is: when I do an insert statement into a table
containing a BLOB column and then keep the database handle
alive (and connected), if I try to use sqlplus (or DBI, for
that matter) to delete the row just inserted, it blocks on
the other process! (that is, the DELETE hangs until the dbh
that the insert was done on goes out of scope).
The following program reliably triggers this behavior on my
system.
When run as
./blob.pl 1 a
Once the prompt appears, if I go to sqlplus and do a
DELETE FROM blob_test WHERE id = 1;
the DELETE hangs until I hit <ENTER> for the Perl script.
When run as
./blob.pl 1 a undef
this does not happen (because the dbh is undefined).
I suspect that the behavior is related to the multi-step process that the
DBI needs to do to insert LOBs; If I do a select (via DBI from a separate
process) on the row supposedly just inserted _before the inserting dbh is
destroyed_, then I receive the entire row **except for the blob data**!
Once I hit <ENTER> such that blob.pl exits, the select then returns the
entire row.
Anyway, I hope I made the situation clear, and I look forward to any and
all suggestions/confirmations.
Here's my simple test case:
---------------------blob.pl---------------------------------
#!/usr/bin/perl -w
use DBI qw(:sql_types);
use DBD::Oracle qw(:ora_types);
# Trace (up to 100!) shows _me_ nothing useful
#DBI->trace(1);
my $dbh = DBI->connect(
'VALID CONNECT STRING',
'VALID USERNAME',
'VALID PASSWORD',
{RaiseError => 1, AutoCommit => 1}
);
die(<<EOT) if grep {!defined($_)} @ARGV[0..1];
Usage:
./blob.pl id blob_data
EOT
# Table is
# CREATE TABLE blob_test (id number, data blob);
my $InsertSQL = 'INSERT INTO blob_test VALUES (?, ?)';
my $sth = $dbh->prepare($InsertSQL);
$sth->bind_param(1, $ARGV[0], SQL_INTEGER);
$sth->bind_param(2, $ARGV[1], {ora_field => 'data', ora_type => ORA_BLOB});
$sth->execute();
#Whether uncommented or not, the following line makes no difference
#undef $sth;
# With the third parameter present and eq to 'undef',
# the insert does not block a subsequent sqlplus delete
# without it, a subsequent sqlplus delete (on the row just inserted)
# blocks until <ENTER> is pressed
undef $dbh if $ARGV[2] eq 'undef';
print "Insert made. Please hit <ETNER> to exit the program\n";
my $Whatever = <STDIN>;
# EOF
------------------------------end blob.pl-------------------------
===
Subject: Oracle: Inserting BLOBs (Bug?) (fwd)
From: Lee Feigenbaum <lee@actifunds.com>
Date: Mon, 14 Aug 2000 09:25:36 -0400 (EDT)
I'm re-posting this message with some additional information; I got no
response the first time, but I have been able to reproduce the problem and
I find it hard to believe that no one has encountered this before :)
The additional information is simply that the same problem occurs with
Oracle CLOBs, and also that the obvious 'workaround' of wrapping the
INSERT in an actual transaction does solve the problem, but does not
explain the problem.
===
Subject: RE: Oracle: Inserting BLOBs (Bug?) (fwd)
From: Ilya Sterin <ideas_pc@usa.com>
Date: Mon, 14 Aug 2000 10:34:31 -0400 (EDT)
Forgive me if I didn't read carefully, but I believe that until you finish()
the statement handle the transaction is not commited. You can do multiple
transactions with on $sth and not commit until it is finish(). When your
program ends it automatically calls finish() on all your statement handles.
But since it hangs the trans is not commited.
===
Subject: RE: Oracle: Inserting BLOBs (Bug?) (fwd)
From: Lee Feigenbaum <lee@actifunds.com>
Date: Mon, 14 Aug 2000 10:36:25 -0400 (EDT)
Ahh; I new there was one piece of additional information I had:
I also tried adding a $sth->finish() at the point where the commented out
undef $sth is, and that did not help the problem either.
Your explanation seems correct except that this INSERT statement _should
not be_ part of a transaction in the first place! :)
Thanks for the suggestion though,
===
Subject: Re: Oracle: Inserting BLOBs (Bug?) (fwd)
From: Tim Bunce <Tim.Bunce@ig.co.uk>
Date: Mon, 14 Aug 2000 16:22:50 +0100
On Mon, Aug 14, 2000 at 10:34:31AM -0400, Ilya Sterin wrote:
>
> Forgive me if I didn't read carefully, but I believe that until you finish()
> the statement handle the transaction is not commited.
Read the DBI docs again. finish has _nothing_ to do with transactions.
You should always be able to remove all calls to finish from your code
and it would make no changes except perhaps generate a few warnings.
===
Subject: Re: Oracle: Inserting BLOBs (Bug?) (fwd)
From: Tim Bunce <Tim.Bunce@ig.co.uk>
Date: Mon, 14 Aug 2000 16:41:15 +0100
On Mon, Aug 14, 2000 at 09:25:36AM -0400, Lee Feigenbaum wrote:
>
> I'm re-posting this message with some additional information; I got no
> response the first time, but I have been able to reproduce the problem and
> I find it hard to believe that no one has encountered this before :)
No one has reported it.
> The additional information is simply that the same problem occurs with
> Oracle CLOBs, and also that the obvious 'workaround' of wrapping the
> INSERT in an actual transaction does solve the problem, but does not
> explain the problem.
DBD::Oracle has to jump through hops behind the scenes to handle lobs.
It inserts an empty lob locator, then has to refetch it to then insert
the lob through the refetched lob locator. Thanks Oracle. The problem
is the fact that Oracle 'lob locators' are only valid within a transaction.
If adding { $dbh->{AutoCommit}=0; $dbh->commit; $dbh->{AutoCommit}=1; }
after the insert fixes it then I can fix the internals.
Better still, you can try yourself... replace the OCI_DEFAULT param
in the OCIStmtExecute_log_stat call at line 1391 of oci8.c with this:
(DBIc_has(imp_dbh,DBIcf_AutoCommit)) ? OCI_COMMIT_ON_SUCCESS : OCI_DEFAULT
and let me know if that fixes it for you.
===
Subject: Re: Oracle: Inserting BLOBs (Bug?) (fwd)
From: Lee Feigenbaum <lee@actifunds.com>
Date: Mon, 14 Aug 2000 12:11:10 -0400 (EDT)
DBD::Oracle has to jump through hops behind the scenes to handle lobs.
> It inserts an empty lob locator, then has to refetch it to then insert
> the lob through the refetched lob locator. Thanks Oracle. The problem
> is the fact that Oracle 'lob locators' are only valid within a transaction.
>
> If adding { $dbh->{AutoCommit}=0; $dbh->commit; $dbh->{AutoCommit}=1; }
> after the insert fixes it then I can fix the internals.
>
> Better still, you can try yourself... replace the OCI_DEFAULT param
> in the OCIStmtExecute_log_stat call at line 1391 of oci8.c with this:
>
> (DBIc_has(imp_dbh,DBIcf_AutoCommit)) ? OCI_COMMIT_ON_SUCCESS : OCI_DEFAULT
>
> and let me know if that fixes it for you.
I tried this; to get it to compile I also added
D_imp_dbh_from_sth;
to the top of the function.
It compiled, but then failed the long section of make test miserably, so I
didn't go any further with testing whether it fixed the problem I'm
seeing.
The output of make test that failed is included here.
Thanks,
Lee
t/base..............ok
t/general...........ok
t/long..............# failed test 72 at line 101. ORA-22920: row
containing the LOB value is not locked (DBD
ERROR: OCILobTrim/OCILobWrite/LOB refetch)
# failed test 73 at line 102. ORA-22920: row containing the LOB value is
not locked (DBD ERROR: OCILobTrim/OCILobWrite/LOB refetch)
# failed test 74 at line 103. ORA-22920: row containing the LOB value is
not locked (DBD ERROR: OCILobTrim/OCILobWrite/LOB refetch)
# failed test 78 at line 118. Strings are of different lengths (0 vs 20),
Len 0
# failed test 79 at line 120. Strings are of different lengths (0 vs 20),
Len 0
# failed test 80 at line 122. Strings are of different lengths (0 vs 20),
Len 0
# failed test 84 at line 136.
# failed test 85 at line 138. truncation error not triggered (LongReadLen
81910, data 1)
# failed test 86 at line 142. (DBI::errstr undefined)
# failed test 90 at line 155.
# failed test 92 at line 158.
# failed test 94 at line 164. Strings are of different lengths (0 vs
71680), Len 0
# failed test 98 at line 197. (DBI::errstr undefined)
# failed test 99 at line 198. Strings are of different lengths (0 vs
10240)
# failed test 101 at line 201. (DBI::errstr undefined)
# failed test 102 at line 202. Strings are of different lengths (0 vs
81920)
# failed test 104 at line 229. Fetched length 0, expected 71680
# failed test 105 at line 230.
# failed test 107 at line 101. ORA-22920: row containing the LOB value is
not locked (DBD ERROR: OCILobTrim/OCILobWrite/LOB refetch)
# failed test 108 at line 102. ORA-22920: row containing the LOB value is
not locked (DBD ERROR: OCILobTrim/OCILobWrite/LOB refetch)
# failed test 109 at line 103. ORA-22920: row containing the LOB value is
not locked (DBD ERROR: OCILobTrim/OCILobWrite/LOB refetch)
# failed test 113 at line 118. Strings are of different lengths (0 vs 20),
Len 0# failed test 114 at line 120. Strings are of different lengths (0 vs
20), Len 0# failed test 115 at line 122. Strings are of different lengths
(0 vs 20), Len 0# failed test 119 at line 136.
# failed test 120 at line 138. truncation error not triggered (LongReadLen
81910, data 1)
# failed test 121 at line 142. (DBI::errstr undefined)
# failed test 125 at line 155.
# failed test 127 at line 158.
# failed test 129 at line 164. Strings are of different lengths (0 vs
71680), Len 0
# failed test 133 at line 197. (DBI::errstr undefined)
# failed test 134 at line 198. Strings are of different lengths (0 vs
10240)
# failed test 136 at line 201. (DBI::errstr undefined)
# failed test 137 at line 202. Strings are of different lengths (0 vs
81920)
# failed test 139 at line 229. Fetched length 0, expected 71680
# failed test 140 at line 230.
===
Subject: Re: Oracle: Inserting BLOBs (Bug?) (fwd)
From: Tim Bunce <Tim.Bunce@ig.co.uk>
Date: Mon, 14 Aug 2000 17:43:01 +0100
On Mon, Aug 14, 2000 at 12:11:10PM -0400, Lee Feigenbaum wrote:
> > DBD::Oracle has to jump through hops behind the scenes to handle lobs.
> > It inserts an empty lob locator, then has to refetch it to then insert
> > the lob through the refetched lob locator. Thanks Oracle. The problem
> > is the fact that Oracle 'lob locators' are only valid within a transaction.
> >
> > If adding { $dbh->{AutoCommit}=0; $dbh->commit; $dbh->{AutoCommit}=1; }
> > after the insert fixes it then I can fix the internals.
> >
> > Better still, you can try yourself... replace the OCI_DEFAULT param
> > in the OCIStmtExecute_log_stat call at line 1391 of oci8.c with this:
> >
> > (DBIc_has(imp_dbh,DBIcf_AutoCommit)) ? OCI_COMMIT_ON_SUCCESS : OCI_DEFAULT
> >
> > and let me know if that fixes it for you.
>
> It compiled, but then failed the long section of make test miserably, so I
> didn't go any further with testing whether it fixed the problem I'm seeing.
>
> t/long..............# failed test 72 at line 101. ORA-22920: row
> containing the LOB value is not locked (DBD ERROR: OCILobTrim/OCILobWrite/LOB refetch)
Okay. Makes sense. Undo that change and add code like this
if (DBIc_has(imp_dbh,DBIcf_AutoCommit))
dbd_db_commit(dbh, imp_dbh);
near the end of that function. You might have to fiddle around to get dbh.
===