DBI BLOB Insert Issue - Database out of space

[tokpela]

Hi Monks,

I have run into an issue when inserting BLOBs into Oracle using DBI.

When our database ran out of space - records were still inserted into the table but the BLOBs were not inserted resulting in incomplete records (zero byte BLOBs).

I have been tasked with making sure that my script does not insert a row if this space issue comes up again. Our DBA created a test database for me to test this issue.

I created a test script to test insertions using both AutoCommit and a manual commmit/rollback insert.

When using AutoCommit - the record is inserted with a zero-byte BLOB. By inserted - I mean that the other non-BLOB data fields were inserted into the table.

DBI trace (level 1) shows the lob extension failure:

abcdefghijklmnopqrstuvw...', :p8=561] at test_script.pl line 75. ][ORA
+-01691: unable to extend lob segment OBX.SYS_LOB0000095979C00009$$ by
+ 128 
in tablespace OBX (DBD ERROR: OCILobWrite in post_execute_lobs)] at te
+st_script.pl  line 81.

Using manual commit/rollback, the results were as expected with no record inserted into the table and the following message received:

abcdefghijklmnopqrstuvw...', ...)= ( 1 ) [1 items] at test_file.txt li
+ne 71
    <- bind_param_inout(8, SCALAR(0x276f2f4), ...)= ( 1 ) [1 items] at
+ test_script.pl line 72
    !! ERROR: 1691 'ORA-01691: unable to extend lob segment OBX.SYS_LO
+B0000095979C00009$$ by 128 in tablespace OBX (DBD ERROR: OCILobWrite 
+in post_execute_lobs)' (err#1)
    <- execute= ( undef ) [1 items] at test_file.txt line 75
    !! ERROR: 1691 CLEARED by call to rollback method
    <- rollback= ( 1 ) [1 items] at test_file.txt line 80
    <- DESTROY(DBI::st=HASH(0x276f014))= ( undef ) [1 items] at test_s
+cript.pl line 81
    <- DESTROY(DBI::db=HASH(0x265ed64))= ( undef ) [1 items] at test_s
+cript.pl line 81

While I can fix this issue easily by making the script use a manual commit/rollback - my question is whether the AutoCommit response is as expected? I would have expected the same result with the entire record being rolled back.

Has anyone else had any experience with this issue?

Thank you!

Chris

[FloydATC]

First off: I'm assuming you've already passed the Oracle error message on to the DBA since that's not a Perl/DBI problem per se. Google has plenty of helpful pointers on that particular error message.

About the AutoCommit vs. manual commit/rollback: Without seeing the actual code we can only speculate, but generally speaking AutoCommit means you forego the whole "roll back in the event of failure" mechanism by committing each query as you go. How this ends up inserting a record with an empty field is hard to say.

If you simply inserted a single record with a number of fields, I'm tempted to say it SHOULD be an "all or nothing" thing; either the entire record makes it into the database with all fields intact or the single insert query should fail completely and leave no trace. This should be the case with or without AutoCommit.

On the other hand, if you insert and then update with AutoCommit, you'll end up with a partial if the update fails. This is what AutoCommit means.

Note that I have no way of knowing your proficiency level, please don't be offended if you know all of this perfectly well :-)

[erix]

Why don't you post your test script (minus the password)? Also give the structure(s) of table/index involved (ideally the CREATE TABLE, etc.)

Those of us with an Oracle instance can then "simply" try it out.

It might also prove useful if you provided all versions involved, i.e., of oracle, perl, DBI, DBD, etc. (This is generally useful for database questions).