DBMS_LOB module

The DBMS_LOB module provides the capability to operate on large objects.

In the following sections describing the individual procedures and functions, lengths and offsets are measured in bytes if the large objects are BLOBs. Lengths and offsets are measured in characters if the large objects are CLOBs.

The DBMS_LOB module supports LOB data up to 10M bytes.

The schema for this module is SYSIBMADM.

The DBMS_LOB module includes the following routines which can contain BLOB and CLOB versions (for example, the OPEN procedure has an OPEN_BLOB and OPEN_CLOB implementation).

Table 1. Built-in routines available in the DBMS_LOB module
Routine Name Description
APPEND procedure Appends one large object to another.
CLOSE procedure Close an open large object.
COMPARE function Compares two large objects.
CONVERTTOBLOB procedure Converts character data to binary.
CONVERTTOCLOB procedure Converts binary data to character.
COPY procedure Copies one large object to another.
ERASE procedure Erase a large object.
GET_STORAGE_LIMIT function Get the storage limit for large objects.
GETLENGTH function Get the length of the large object.
INSTR function Get the position of the nth occurrence of a pattern in the large object starting at offset.
ISOPEN function Check if the large object is open.
OPEN procedure Open a large object.
READ procedure Read a large object.
SUBSTR function Get part of a large object.
TRIM procedure Trim a large object to the specified length.
WRITE procedure Write data to a large object.
WRITEAPPEND procedure Write data from the buffer to the end of a large object.
Note: In partitioned database environments, you will receive an error if you execute any of the following routines inside a WHERE clause of a SELECT statement:
  • dbms_lob.compare
  • dbms_lob.get_storage_limit
  • dbms_lob.get_length
  • dbms_lob.instr
  • dbms_lob.isopen
  • dbms_lob.substr
The following table lists the public variables available in the module.
Table 2. DBMS_LOB public variables
Public variables Data type Value
lob_readonly INTEGER 0
lob_readwrite INTEGER 1