General

How Do I Determine if the LOB Column with a Trigger is Being Updated?

Question

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.

Answer

You can use the UPDATING clause inside of the trigger to find out if the LOB column is being updated or not.

CREATE OR REPLACE TRIGGER......
...
   IF UPDATING('lobcol')
   THEN .....

...

Note: The above works only for top-level lob columns.

Reading and Loading LOB Data: What Should Amount Parameter Size Be?

Question

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:

ORA-21560: argument 3 is null, invalid, or out of range

Reducing the amount by 1 to 4294967295 causes the following error message:

ORA-22993: specified input amount is greater than actual source amount

Please help me understand why I am getting errors.

Answer

• PL/SQL:
  • For DBMS_LOB.LOADFROMFILE, you cannot specify the amount more than the size of the BFILE. So the code example you gave returns an error.

  • For DBMS_LOB.READ, the amount can 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 amount should be no larger than the size of the buffer, the amount is 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.

• OCI: Again, you cannot specify amount larger 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.