The DBMS_LOB package is used to perform operations on large objects.

Table 1. Functions and stored procedures in the DBMS_LOB package
Function or stored procedure Type Return type Description
APPEND(dest_lob IN OUT,src_lob) Stored procedure N/A Appends a large object to another.
COMPARE(lob_1, lob_2 [, amount[, offset_1 [, offset_2 ]]]) Function INTEGER Compares two large objects bytewise at the specified offsets for the specified length.
CONVERTOBLOB(dest_lob IN OUT,src_clob, amount, dest_offset IN OUT, src_offset IN OUT,blob_csid, lang_context IN OUT,warning OUT) Stored procedure N/A Converts a large object of the CLOB data type into a large object of the BLOB data type.
CONVERTTOCLOB(dest_lob IN OUT,src_blob, amount, dest_offset IN OUT, src_offset IN OUT,blob_csid, lang_context IN OUT,warning OUT) Stored procedure N/A Converts a large object of the BLOB data type into a large object of the CLOB data type.
COPY(dest_lob IN OUT, src_lob,amount [, dest_offset [,src_offset ]]) Stored procedure N/A Copies a large object to another.
ERASE(lob_loc IN OUT, amount IN OUT [, offset ]) Stored procedure N/A Erases a portion of data in a large object.
GET_STORAGE_LIMIT(lob_loc) Function INTEGER Specifies the maximum storage space that can be used by a large object in the database.
GETLENGTH(lob_loc) Function INTEGER Queries the length of the large object.
INSTR(lob_loc, pattern [,offset [, nth ]]) Function INTEGER Retrieves the position where the specified pattern appears for the specified nth time in a large object.
READ(lob_loc, amount IN OUT,offset, buffer OUT) Stored procedure N/A Reads a portion of data from a large object.
SUBSTR(lob_loc [, amount [,offset ]]) Function RAW or VARCHAR2 Retrieves a portion of a large object.
TRIM(lob_loc IN OUT, newlen) Stored procedure N/A Trims a large object to the specified length.
WRITE(lob_loc IN OUT, amount,offset, buffer) Stored procedure N/A Writes data to a large object.
WRITEAPPEND(lob_loc IN OUT,amount, buffer) Stored procedure N/A Writes data from the buffer to the end of a large object.
The following table lists the public variables available in the DBMS_LOB package.
Table 2. Public variables in the DBMS_LOB package
Public variable Data type Variable value
compress off INTEGER 0
compress_on INTEGER 1
deduplicate_off INTEGER 0
deduplicate_on INTEGER 4
default_csid INTEGER 0
default_lang_ctx INTEGER 0
encrypt_off INTEGER 0
encrypt_on INTEGER 1
file_readonly INTEGER 0
lobmaxsize INTEGER 1073741823
lob_readonly INTEGER 0
lob_readwrite INTEGER 1
no_warning INTEGER 0
opt_compress INTEGER 1
opt_deduplicate INTEGER 4
opt_encrypt INTEGER 2
warn_inconvertible_char INTEGER 1
Note In the following sections, if the data type of a large object is BLOB, the length and offset of the object are measured in bytes. If the data type of a large object is CLOB, the length and offset are measured in characters.

APPEND

The APPEND stored procedure is used to append a large object to another. The data types of the two large objects must be the same.
APPEND(dest_lob IN OUT { BLOB | CLOB }, src_lob { BLOB | CLOB })
Table 3. Parameters
Parameter Description
dest lob Specifies the location of the target large object. The data type of the dest_lob parameter must be the same as that of the src_lob parameter.
src lob Specifies the location of the source large object. The data type of the src_lob parameter must be the same as that of the dest_lob parameter.

COMPARE

The COMPARE function compares two large objects bytewise at the specified offsets for the specified length. The data types of the two large objects that are compared must be the same.
status INTEGER COMPARE(lob_1 { BLOB | CLOB },
  lob_2 { BLOB | CLOB }
  [, amount INTEGER [, offset_1 INTEGER [, offset_2 INTEGER ]]])
Table 4. Parameters
Parameter Description
lob_1 Specifies the location of the first large object. The data type of the lob_1 parameter must be the same as that of the lob_2 parameter.
lob_2 Specifies the location of the second large object. The data type of the lob_2 parameter must be the same as that of the lob_1 parameter.
amount
  • If the data types of large objects are BLOB, the objects are compared for the number of bytes.
  • If the data types of large objects are CLOB, the objects are compared for the number of characters.
The default value of the amount parameter is the maximum size of a large object.
offset 1 Specifies the position in the first large object to start the comparison. Default value: 1.
offset_2 Specifies the position in the second large object to start the comparison. Default value: 1.
status
  • Specifies the comparison result. If the two large objects are the same at the specified offsets within the specified length, 0 is returned.
  • If the objects are not the same, a non-zero value is returned.
  • If the value of the amount, offset_1, or offset_2 parameter is smaller than 0, NULL is returned.

CONVERTTOBLOB

The CONVERTTOBLOB stored procedure is used to convert a large object of the CLOB data type into a large object of the BLOB data type.
CONVERTTOBLOB(dest_lob IN OUT BLOB, src_clob CLOB,
  amount INTEGER, dest_offset IN OUT INTEGER,
  src_offset IN OUT INTEGER, blob_csid NUMBER,
  lang_context IN OUT INTEGER, warning OUT INTEGER)
Table 5. Parameters
Parameter Description
dest lob Specifies a target large object of the BLOB data type.
src clob Specifies a target large object of the CLOB data type.
amount Specifies the number of characters to be converted in the large object specified by the src_clob parameter.
dest_offset IN Specifies the location of the byte in the target large object where the write operation starts.
dest_offset OUT Specifies the location of the byte in the target large object after the write operation is complete.
src offset IN Specifies the location of the character in the source large object where the conversion starts.
src_offset OUT Specifies the location of the character in the source large object after the conversion is complete.
blob csid Specifies the character set ID of the target large object.
langcontext IN Specifies the language environment for the conversion. The default value 0 is usually used.
langcontext OUT Specifies the language environment after the conversion is complete.
warning
  • If the conversion is successful, 0 is returned.
  • If the conversion fails, 1 is returned.

CONVERTTOCLOB

The CONVERTTOCLOB stored procedure is used to convert a large object of the BLOB data type into a large object of the CLOB data type.
CONVERTTOCLOB(dest_lob IN OUT CLOB, src_blob BLOB,
  amount INTEGER, dest_offset IN OUT INTEGER,
  src_offset IN OUT INTEGER, blob_csid NUMBER,
  lang_context IN OUT INTEGER, warning OUT INTEGER)
Table 6. Parameters
Parameter Description
dest lob Specifies a target large object of the CLOB data type.
src_blob Specifies a source large object of the BLOB data type.
amount Specifies the number of bytes to be converted in the large object specified by the src_blob parameter.
dest_offset IN Specifies the location of the character in the target large object where the write operation starts.
dest_offset OUT Specifies the location of the character in the target large object after the write operation is complete.
src offset IN Specifies the location of the byte in the source large object where the conversion starts.
src_offset OUT Specifies the location of the byte in the source large object after the conversion is complete.
blob csid Specifies the character set ID of the target large object.
CLOB. langcontext IN Specifies the language environment for the conversion. The default value 0 is usually used.
langcontext OUT Specifies the language environment after the conversion is complete.
warning
  • If the conversion is successful, 0 is returned.
  • If the conversion fails, 1 is returned.

COPY

The COPY stored procedure is used to copy a large object to another. The data types of the source and target large objects must be the same.
COPY(dest_lob IN OUT { BLOB | CLOB }, src_lob 
{ BLOB | CLOB },
  amount INTEGER
  [, dest_offset INTEGER [, src_offset INTEGER ]])
Table 7. Parameters
Parameter Description
dest lob Specifies the location of the target large object to which a source large object is copied. The data type of this parameter must be the same as that of the src_lob parameter.
src lob Specifies the location of the target large object to be copied. The data type of this parameter must be the same as that of the dest_lob parameter.
amount Specifies the number of bytes or characters to be copied in the large object specified by the src_lob parameter.
dest offset Specifies the location in the target large object where writing of the source large object starts. Default value: 1.
src offset Specifies the location of the character in the source large object where the copy operation starts. Default value: 1.

ERASE

The ERASE stored procedure is used to erase a portion of data in a large object. For a large object of the BLOB data type, the specified portion is replaced with a 0-byte filter. For a large object of the CLOB data type, the specified portion is replaced with spaces. The operation does not change the size of the large object.
ERASE(lob_loc IN OUT { BLOB | CLOB }, amount IN OUT INTEGER
  [, offset INTEGER ])
Table 8. Parameters
Parameter Description
lob loc Specifies the large object that you want to delete.
amount IN Specifies the number of bytes or characters to be erased in the large object.
amount OUT Specifies the number of bytes or characters that were erased. If the end of the large object is reached before the specified number of bytes or characters has been erased, the output value is smaller than the input value.
offset Specifies the location in the large object where the erasing operation starts. Default value: 1.

GET_STORAGE_LIMIT

The GET_STORAGE_LIMIT function is used to retrieve the maximum storage space that can be used by large objects.
size INTEGER GET_STORAGE_LIMIT(lob_loc BLOB)

size INTEGER GET_STORAGE_LIMIT(lob_loc CLOB)
Table 9. Parameters
Parameter Description
size Specifies the maximum storage space that can be used by a large object in the database.
lob loc The parameter is provided to ensure the compatibility with Oracle databases and can be ignored during runtime.

GETLENGTH

The GETLENGTH function is used to retrieve the length of a large object.
amount INTEGER GETLENGTH(lob_loc BLOB)

amount INTEGER GETLENGTH(lob_loc CLOB)
Table 10. Parameters
Parameter Description
lob loc Specifies the name of the large object.
amount Specifies the length of the large object.
  • For a large object of the BLOB data type, the length is measured in bytes.
  • For a large object of the CLOB data type, the length is measured in characters.

INSTR

The INSTR function is used to retrieve the position where the specified pattern appears for the specified nth time in a large object.
position INTEGER INSTR(lob_loc { BLOB | CLOB },
  pattern { RAW | VARCHAR2 } [, offset INTEGER [, nth INTEGER ]])
Table 11. Parameters
Parameter Description
lob loc Specifies the name of the large object.
pattern Specifies the pattern to match in the large object. The pattern is a combination of bytes or characters.
  • If the data type of the large object specified by the lob_loc parameter is BLOB, the data type of the pattern must be RAW.
  • If the data type of the large object specified by the lob_loc parameter is CLOB, the data type of the pattern must be VARCHAR2.
offset Specifies the position to start searching for the pattern in the large object specified by the lob_loc parameter. The first byte or character is labeled as offset 1. Default value: 1.
nth Specifies the nth time when the specified pattern appears starting from the specified offset. Default value: 1.
position Specifies the position where the specified pattern appears for the specified nth time in the large object. The search starts from the specified offset.

READ

The READ stored procedure is used to read a portion of data in a large object into a buffer.
READ(lob_loc { BLOB | CLOB }, amount IN OUT BINARY_INTEGER,
  offset INTEGER, buffer OUT { RAW | VARCHAR2 })
Table 12. Parameters
Parameter Description
lob loc Specifies the large object to be read.
amount IN Specifies the total number of bytes or characters to be read.
amount OUT Specifies the total number of bytes or characters that were read. If no more data is available for reading, 0 is returned and the DATA_NOT_FOUND exception is thrown.
offset Specifies the location where the read operation starts in the large object. The first byte or character is labeled as offset 1.
buffer Specifies the variable that receives a portion of data in the large object.
  • If the data type of the lob_loc parameter is BLOB, the data type of the buffer parameter must be RAW.
  • If the data type of the lob_loc parameter is CLOB, the data type of the buffer parameter must be VARCHAR2.

SUBSTR

The SUBSTR function is used to retrieve a portion of data in a large object.
data { RAW | VARCHAR2 } SUBSTR(lob_loc { BLOB | CLOB }
  [, amount INTEGER [, offset INTEGER ]])
Table 13. Parameters
Parameter Description
lob loc Specifies the large object to be read.
amount Specifies the number of bytes or characters to be returned. Default value: 32,767.
offset Specifies the location where the read operation starts in the large object. The first byte or character is labeled as offset 1. Default value: 1.
data Specifies the retrieved portion of the large object.
  • If the data type of the lob_loc parameter is BLOB, the data type of the data parameter must be RAW.
  • If the data type of the lob_loc parameter is CLOB, the data type of the data parameter must be VARCHAR2.

TRIM

The TRIM stored procedure is used to trim a large object to the specified length.
TRIM(lob_loc IN OUT { BLOB | CLOB }, newlen INTEGER)
Table 14. Parameters
Parameter Description
lob loc Specifies the large object to be trimmed.
newlen Specifies the total number of bytes or characters in the large object to be trimmed.

WRITE

The WRITE stored procedure is used to write data to a large object. Any data from the large object starting at the specified offset within the specified length is overwritten by data in the buffer.
WRITE(lob_loc IN OUT { BLOB | CLOB },
  amount BINARY_INTEGER,
  offset INTEGER, buffer { RAW | VARCHAR2 })
Table 15. Parameters
Parameter Description
lob loc Specifies the large object to be written.
amount Specifies the number of bytes or characters in the buffer to be written to the large object.
offset Specifies the position of the byte or character in the large object where the write operation starts.
buffer Specifies the retrieved portion of the large object.
  • If the data type of the lob_loc parameter is BLOB, the data type of the buffer parameter must be RAW.
  • If the data type of the lob_loc parameter is CLOB, the data type of the buffer parameter must be VARCHAR2.

WRITEAPPEND

The WRITEAPPEND stored procedure is used to add data to the end of a large object.
WRITEAPPEND(lob_loc IN OUT { BLOB | CLOB },
  amount BINARY_INTEGER, buffer { RAW | VARCHAR2 })
Table 16. Parameters
Parameter Description
lob loc Specifies the large object to which data is to be added.
amount Specifies the number of bytes or characters in the buffer to be added to the end of the large object.
buffer Specifies the retrieved portion of the large object.
  • If the data type of the lob_loc parameter is BLOB, the data type of the buffer parameter must be RAW.
  • If the data type of the lob_loc parameter is CLOB, the data type of the buffer parameter must be VARCHAR2.