Oracle8i Application Developer's Guide - Large Objects (LOBs)
Release 2 (8.1.6)

Part Number A76940-01





Go to previous page Go to beginning of chapter Go to next page

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:

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.

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:

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:

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 


Appends the LOB value to another LOB 


Copies all or part of a LOB to another LOB 


Erases part of a LOB, starting at a specified offset 


Load BFILE data into an internal LOB 


Trims the LOB value to the specified shorter length 


Writes data to the LOB at a specified offset 


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 


Compares the value of two LOBs 


Gets the chunk size used when reading and writing. This only works on internal LOBs and does not apply to external LOBs (BFILEs). 


Gets the length of the LOB value 


Returns the matching position of the nth occurrence of the pattern in the LOB 


Reads data from the LOB starting at the specified offset 


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 


Creates a temporary LOB 


Checks if a LOB locator refers to a temporary LOB 


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 


Closes the file1 


Closes all previously opened files 


Checks if the file exists on the server 


Gets the directory alias and file name 


Checks if the file was opened using the input BFILE locators2


Opens a file3
1 Use CLOSE() instead.
2 Use ISOPEN() instead.
3 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 


Opens a LOB 


Sees if a LOB is open 


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.

Go to previous page Go to beginning of chapter Go to next page
Copyright © 1996-2000, Oracle Corporation.

All Rights Reserved.