Large Objects   «Prev  Next»

Lesson 5Managing LOB size
ObjectiveUse LOB commands to adjust the storage size of LOBs

Managing LOB size

As their name indicates, LOBs are large. They take up a lot of storage space, so there are times when you want to be able to control the storage space to work with the size of the LOB storage.

LOB procedures

Earlier in this module, we mentioned the DBMS_LOB built-in PL/SQL package. A number of procedures in this package help you adjust the way that LOBs are stored, but four of them of them are most commonly used, as shown in the following table:

FunctionSimplified syntaxNotes
DBMS_LOB.COPY() 
DBMS_LOB.COPY(
dest_lob,
src_lob 
dest_offset, 
src_offset, 
amt);

where dest_lob is the LOB locator to which the data in src_lob is to be copied to, src_lob is the LOB locator of thesource LOB, dest_offset is the offset from the beginning of the destination LOB, src_offset is the offset from the beginningof the source LOB, and amt is the amount of bytes to be copied.		You can also specify an offset to begin copying the data from src_lob, the number of bytes to be copied, and the offset for thecopied data to be placed at in dest_lob.
DBMS_LOB.APPEND() 
DBMS_LOB.APPEND(
dest_lob,
src_lob);
where dest_lob is the LOB locator that the data in the src_lob is to be appended to and src_lob is the LOB locator or the source LOB.		 This procedure will take the contents from one LOB and append them to the end of another LOB.
DBMS_LOB.TRIM() 
DBMS_LOB.TRIM(
lob_locator,
newlen);

where lob_locator is the LOB locator for the LOB and newlen is an integer representing the new number of bytes in the LOB.		You must first retrieve the locator for the LOB. newlen cannot be more than the existing length of the LOB or an exception will be returned.
DBMS_LOB.ERASE() 
DBMS_LOB.ERASE(
lob_loc,
amount, 
offset);

where lob_loc is the LOB locator for the target LOB, amount is an integer representing the number of bytes to be erased, andoffset is an integer representing the offset of the action.		 You can erase data from within a LOB by setting an offset and a number of characters. The size of the LOB is not reduced when theERASE() procedure is used.
The following series of images illustrates the use of these procedures:
DBMS_LOB functions 1
1) With the COPY() function, the content of the src_lob are copied to the dest_lob

DBMS_LOB functions 2
2) With the APPEND() function, the contents of the src_lob are appended to the end of the contents of dest_lob

DBMS_LOB functions 3
3) With the TRIM() function, the length of the data in the src_lob is reduced to the number of bytes specified in the function

DBMS_LOB functions 4
4) With the ERASE() function, the data is erased from the src_lob, but the space is not reclaimed


In the next lesson, you will learn how Oracle uses LOBs to store large arrays.