LOB Programmatic Environments, 4 of 9

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:
Oracle8i 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 11, "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.

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-3
To read or examine LOB values, see Table 3-4
To create, free, or check on temporary LOBs, see Table 3-5
For read-only functions on external LOBs (BFILEs), see Table 3-6
To open or close a LOB, or check if LOB is open, see Table 3-7

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

Table 3-3 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-4 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-5 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-6 PL/SQL: DBMS_LOB Read-Only Procedures for BFILEs

Function/Procedure	Description
`FILECLOSE()`	Closes the file¹
`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²
`FILEOPEN()`	Opens a file³

¹ Use CLOSE() instead.
² Use ISOPEN() instead.
³ Use OPEN() instead.

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

Table 3-7 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

We will describe these procedures in greater detail as we explore specific LOB operations (e.g., INSERT a row containing a LOB) in the following chapters:

You will be able to access associated PL/SQL example scripts from your Oracle8i software CD /rdbms/demo directory in a future release.