During conventional path loads, the input records are parsed according to the field specifications, and each data field is copied to its corresponding bind array (an area in memory where SQL*Loader stores data to be loaded). When the bind array is full (or no more data is left to read), an array insert operation is performed.
SQL*Loader stores LOB fields after a bind array insert is done. Thus, if there are any errors in processing the LOB field (for example, the LOBFILE could not be found), then the LOB field is left empty. Note also that because LOB data is loaded after the array insert has been performed, BEFORE
and AFTER
row triggers may not work as expected for LOB columns. This is because the triggers fire before SQL*Loader has a chance to load the LOB contents into the column. For instance, suppose you are loading a LOB column, C1
, with data and you want a BEFORE
row trigger to examine the contents of this LOB column and derive a value to be loaded for some other column, C2
, based on its examination. This is not possible because the LOB contents will not have been loaded at the time the trigger fires.