Mega Code Archive
Oracle 8 and writing data to a lob [blob-clob] field
<![CDATA[
Question:
I am using Oracle 8.0.4 as my database backend and when I attempt to write data
to a Blob/Clob field I keep getting the error "ORA-22990: LOB locators cannot span
transactions". The code I am using used to work against Oracle 7 servers but against
Oracle 8 it keeps raising this error. What is wrong?
Answer:
As of Oracle 8, Oracle mandates that in order to write data to a LOB datatype ( Blobs and
Clobs are subtypes of the LOB type) that you must be in a transaction. this is due to the
changes in the way oracle implemented the LOB data type. To get around this issue start a
transaction before you attempt to write data to a LOB data type.
for example if your code was (Delphi example)
Table1.Insert;
Table1KEYFLD.AsInteger := new_val;
Table1TESTFLD.LoadFromFile('D:\data\test.bmp');
Table1.Post;
change it to
Database1.StartTransaction;
Table1.Insert;
Table1KEYFLD.AsInteger := new_val;
Table1TESTFLD.LoadFromFile('D:\data\test.bmp');
Table1.Post;
Database1.Commit;
and this will get you around the issue.
For more information on LOB data types see the Oracle 8 documentation.
]]>