|Oracle9i Application Developer's Guide - Large Objects (LOBs)
Release 1 (9.0.1)
Part Number A88879-01
Frequently Asked Questions about LOBs, 3 of 12
The project that I'm working on requires a trigger on a LOB column. The requirement is that when this column is updated, we want to check some conditions. How do I check whether there is any value in the NEW for this LOB column? Null does not work, since you can't compare BLOB with NULL.
You can use the UPDATING clause inside of the trigger to find out if the LOB column is being updated or not.
Note: The above works only for top-level LOB columns.
I read in one of the prior release Application Developer's Guides the following:
"When reading the LOB value, it is not an error to try to read beyond the end of the LOB. This means that you can always specify an input amount of 4Gb regardless of the starting offset and the amount of data in the LOB. You do need to incur a round-trip to the server to call OCILobGetLength() to find out the length of the LOB value in order to determine the amount to read. "
And again, under the DBMS_LOB.LOADFROMFILE() procedure...
"It is not an error to specify an amount that exceeds the length of the data in the source BFILE. Thus, you can specify a large amount to copy from the BFILE which will copy data from the src_offset to the end of the BFILE. "
However, the following code...
declare cursor c is select id, text from bfiles; v_clob clob; begin for j in c loop Dbms_Lob.FileOpen ( j.text, Dbms_Lob.File_Readonly ); insert into clobs ( id, text ) values ( j.id, empty_clob() ) returning text into v_clob; Dbms_Lob.LoadFromFile ( dest_lob => v_clob, src_lob => j.text, amount => 4294967296, /* = 4Gb */ dest_offset => 1, src_offset => 1 ); Dbms_Lob.FileClose ( j.text ); end loop; commit; end; /
causes the following error message:
Reducing the amount by 1 to 4294967295 causes the following error message:
Please help me understand why I am getting errors.
amountmore than the size of the BFILE. So the code example you gave returns an error.
amountcan be larger than the size of the data. But then, since PL/SQL limits the size of the buffer to 32K, and given the fact that the
amountshould be no larger than the size of the buffer, the
amountis restricted to 32K.
Please note that in PL/SQL, if the
amount is larger than the buffer size, it returns an error. In any case, the
amount cannot exceed 4Gig-1 because that is the limit of a ub4 variable.
amountlarger than the length of the BFILE in OCILobLoadFromFile. However, in OCILobRead, you can specify
amount=4Gig-1, and it will read to the end of the LOB.
We have a table with BLOB columns. We use NOLOGGING to be fast. It means that the BLOB chunks are not saved in redologs. What happens if the server crashes? At recovery, is the table data lost or is the table corrupted?
Any LOB data not written to the datafiles cannot be recovered from redo logs; it must be reloaded. Because the LOB index updates are written to redo, the index will indicate that the LOB exists, but the LOB will not exist (it was not recovered from redo), so my guess is that you will get a data corruption error.