PL/SQL   «Prev  Next»

Lesson 4 The DBMS_LOB package
ObjectiveDescribe the components of the DBMS_LOB package.

DBMS_LOB package

The DBMS_LOB package provides subprograms to operate on BLOBs, CLOBs, NCLOBs, BFILEs, and temporary LOBs. You can use DBMS_LOB to access and manipulate specific parts of a LOB or complete LOBs.
DBMS_LOB can read and modify BLOBs, CLOBs, and NCLOBs; it provides read-only operations for BFILEs. Most of the LOB operations are provided by this package.
The more commonly used procedures and functions contained within the DBMS_LOB package can be broadly categorized. The routines that modify the BLOB, CLOB, and NCLOB values include:
  1. APPEND(): Append the contents of the source LOB to the destination LOB.
  2. COPY(): Copy all or part of the source LOB to the destination LOB.
  3. ERASE(): Erase all or part of a LOB.
  4. LOADFROMFILE(): Load BFILE data into an internal LOB.
  5. TRIM(): Trim the LOB value to the specified shorter length.
  6. WRITE(): Write data to the LOB from a specified offset.
The routines that read or examine LOB values are:
  1. COMPARE(): Compare two entire or part of two LOBs.
  2. GETLENGTH(): Get the length of the LOB value.
  3. INSTR(): Return the matching position of the nth occurrence of the pattern within the LOB.
  4. READ(): Read data from the LOB starting at the specified offset.
  5. SUBSTR(): Return part of the LOB value starting at the specified offset.
The read-only routines specific to BFILEs are:
  1. code>FILECLOSE(): Close the file.
  2. FILECLOSEALL(): Close all previously opened files.
  3. FILEEXISTS(): Check if the file exists on the server.
  4. FILEGETNAME(): Get the directory alias and filename.
  5. FILEISOPEN(): Check if the file was opened using the input BFILE locators.
  6. FILEOPEN(): Open a file.
We will review some of these subprograms and how to use them in forthcoming lessons.
In the next lesson, you will learn how to write a PL/SQL command to retrieve and display LOB data.