Using PL/SQL (DBMS_LOB Package) To Work With LOBs

The PL/SQL DBMS_LOB package can be used for the following operations:

• Internal persistent LOBs and Temporary LOBs: Read and modify operations, either entirely or in a piece-wise manner.

• BFILEs: Read operations

  See Also: Oracle9i Supplied PL/SQL Packages Reference for detailed documentation, including parameters, parameter types, return values, and example code.

Provide a LOB Locator Before Invoking the DBMS_LOB Routine

As described in more detail below, DBMS_LOB routines work based on LOB locators. For the successful completion of DBMS_LOB routines, you must provide an input locator representing a LOB that exists in the database tablespaces or external filesystem, before you invoke the routine.

• Internal LOBs: First use SQL to define tables that contain LOB columns, and subsequently you can use SQL to initialize or populate the locators in these LOB columns.

• External LOBs: Define a DIRECTORY object that maps to a valid physical directory containing the external LOBs that you intend to access. These files must exist, and have READ permission for Oracle Server to process. If your operating system uses case-sensitive path names, specify the directory in the correct case. See Chapter 12, "External LOBs (BFILEs)", "Directory Object" for more information.

Once the LOBs are defined and created, you may then SELECT a LOB locator into a local PL/SQL LOB variable and use this variable as an input parameter to DBMS_LOB for access to the LOB value.

Examples provided with each DBMS_LOB routine will illustrate this in the following sections.

PL/SQL - LOB Guidelines

Client PL/SQL Procedures Cannot Call DBMS_LOB Routines

Client-side PL/SQL procedures cannot call DBMS_LOB package routines.

However, you can use server-side PL/SQL procedures or anonymous blocks in Pro*C/C++ to call DBMS_LOB package routines.

Offset and Amount Parameters: Fixed-Width Versus Varying-Width, Character or Byte For DBMS_LOB Package

For DBMS_LOB package, i.e., for both fixed and varying-width character sets, the following rules apply:

• CLOBs and NCLOBs: Offset and amount parameters are always in characters

• BLOBs and BFILES: Offset and amount parameters are always in bytes

DBMS_LOB.LOADFROMFILE: Specify Amount Parameter to be Less than Size of BFILE!

When using DBMS_LOB.LOADFROMFILE, you cannot specify the amount parameter to be larger than the size of the BFILE.

DBMS_LOB.READ: Amount Parameter Can be Larger than Data Size

When using DBMS_LOB.READ, the amount parameter can be larger than the size of the data. In PL/SQL, the amount should be less than or equal to the size of the buffer, and the buffer size is limited to 32K.

PL/SQL Functions and Procedures that Operate on BLOBs, CLOBs, NCLOBs, and BFILEs

PL/SQL functions and procedures that operate on BLOBs, CLOBs, NCLOBs, and BFILEs are summarized below:

• To modify internal LOB values, see Table 3-4

• To read or examine LOB values, see Table 3-5

• To create, free, or check on temporary LOBs, see Table 3-6

• For read-only functions on external LOBs (BFILEs), see Table 3-7

• To open or close a LOB, or check if LOB is open, see Table 3-8

PL/SQL Functions/Procedures To Modify BLOB, CLOB, and NCLOB Values

Table 3-4 PL/SQL: DBMS_LOB Procedures to Modify BLOB, CLOB, and NCLOB Values

Function/Procedure	Description
APPEND()	Appends the LOB value to another LOB
COPY()	Copies all or part of a LOB to another LOB
ERASE()	Erases part of a LOB, starting at a specified offset
LOADFROMFILE()	Load BFILE data into an internal LOB
TRIM()	Trims the LOB value to the specified shorter length
WRITE()	Writes data to the LOB at a specified offset
WRITEAPPEND()	Writes data to the end of the LOB

PL/SQL Functions/Procedures To Read or Examine Internal and External LOB Values

Table 3-5  PL/SQL: DBMS_LOB Procedures to Read or Examine Internal and External LOB values

Function/Procedure	Description
COMPARE()	Compares the value of two LOBs
GETCHUNKSIZE()	Gets the chunk size used when reading and writing. This only works on internal LOBs and does not apply to external LOBs (BFILEs).
GETLENGTH()	Gets the length of the LOB value
INSTR()	Returns the matching position of the nth occurrence of the pattern in the LOB
READ()	Reads data from the LOB starting at the specified offset
SUBSTR()	Returns part of the LOB value starting at the specified offset

PL/SQL Functions/Procedures To Operate on Temporary LOBs

Table 3-6 PL/SQL:  DBMS_LOB Procedures to Operate on Temporary LOBs

Function/Procedure	Description
CREATETEMPORARY()	Creates a temporary LOB
ISTEMPORARY()	Checks if a LOB locator refers to a temporary LOB
FREETEMPORARY()	Frees a temporary LOB

PL/SQL Read-Only Functions/Procedures for BFILEs

Table 3-7  PL/SQL: DBMS_LOB Read-Only Procedures for BFILEs

Function/Procedure	Description
FILECLOSE()	Closes the file. Use CLOSE() instead.
FILECLOSEALL()	Closes all previously opened files
FILEEXISTS()	Checks if the file exists on the server
FILEGETNAME()	Gets the directory alias and file name
FILEISOPEN()	Checks if the file was opened using the input BFILE locators. Use ISOPEN() instead.
FILEOPEN()	Opens a file. Use OPEN() instead.

PL/SQL Functions/Procedures To Open and Close Internal and External LOBs

Table 3-8   PL/SQL: DBMS_LOB Procedures to Open and Close Internal and External LOBs

Function/Procedure	Description
OPEN()	Opens a LOB
ISOPEN()	Sees if a LOB is open
CLOSE()	Closes a LOB

These procedures are described in detail for specific LOB operations, such as, INSERT a row containing a LOB,in these chapters:

• Chapter 10, "Internal Persistent LOBs"

• Chapter 11, "Temporary LOBs"

• Chapter 12, "External LOBs (BFILEs)"

You can access many of the PL/SQL LOB example scripts from these chapters, at $ORACLE_HOME/rdbms/demo/lobs/plsql.