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

Table 1. DBMS_LOB functions and stored procedures
Function/stored procedure Function or stored procedure 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.
CONVERTOBLOB(dest_lob IN OUT,src_clob, amount, dest_offsetIN OUT, src_offset IN OUT,blob_csid, lang_context IN OUT,warning OUT) Stored procedure N/A Converts characters to binary data.
CONVERTTOCLOB(dest_lob IN OUT,src_blob, amount, dest_offsetIN OUT, src_offset IN OUT,blob_csid, lang_context IN OUT,warning OUT) Stored procedure N/A Converts binary data to characters.
COPY(dest_lob IN OUT, src_lob,amount [, dest_offset [,src_offset ]]) Stored procedure N/A Copies a large object to another one.
ERASE(lob_loc IN OUT, amount IN OUT [, offset ]) Stored procedure N/A Erases a large object.
GET_STORAGE_LIMIT(lob_loc) Function INTEGER Retrieves the storage limit for large objects.
GETLENGTH(lob_loc) Function INTEGER Retrieves the length of the large object.
INSTR(lob_loc, pattern [,offset [, nth ]]) Function INTEGER Retrieves the position of a pattern in the large object that starts at the specified offset.
READ(lob_loc, amount IN OUT,offset, buffer OUT) Stored procedure N/A Reads 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 DBMS_SQL package in PolarDB-O is only partially implemented when compared to Oracle's version. PolarDB-O only supports the functions and stored procedures that are listed in the preceding table.

The following table lists the public variables that can be used in the package.

Table 2. DBMS_LOB public variables
Public variable Data type 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

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 })

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 stored procedure compares two large objects by byte at the specified offsets within 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 ]]])

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 within the specified amount of bytes. If the data types of large objects are CLOB, the objects are compared within the specified amount 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. The position of the first byte or character is labeled as offset 1. The default value is 1.
offset_2 Specifies the position in the second large object to start the comparison. The position of the first byte or character is labeled as offset 1. The default value is 1.
status Specifies the comparison result. If the two large objects are the same at the specified offsets within the specified length, 0 (zero) 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)

Parameters

Parameter Description
dest_lob Specifies a target large object of the BLOB data type. You can use the CONVERTTOBLOB stored procedure to convert a large object of the CLOB data type into a large object of the BLOB data type.
src_clob Specifies a source large object of the CLOB data type. You can use the CONVERTTOBLOB stored procedure to convert a large object of the BLOB data type into a 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 writing of the source large object starts. The first byte is labeled as offset 1.
dest_offset OUT Specifies the location of the byte in the target large object after the write operation is complete. The first byte is labeled as offset 1.
src_offset IN Specifies the location of the character in the source large object where the conversion starts. The first character is labeled as offset 1.
src_offset OUT Specifies the location of the character in the source large object after the conversion is complete. The first character is labeled as offset 1.
blob_csid Specifies the character set ID of the target large object.
langcontext IN Specifies the language environment for the conversion. The default value of 0 is typically used for the setting.
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)

Parameters

Parameter Description
dest_lob Specifies a target large object of the CLOB data type. You can use the CONVERTTOBLOB stored procedure to convert a large object of the BLOB data type into a large object of the CLOB data type.
src_blob Specifies a source large object of the BLOB data type. You can use the CONVERTTOBLOB stored procedure to convert a large object of the CLOB data type into a 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 writing of the source large object starts. The first character is labeled as offset 1.
dest_offset OUT Specifies the location of the character in the target large object of the CLOB data type after the write operation is complete. The first character is labeled as offset 1.
src_offset IN Specifies the location of the byte in the source large object where the conversion starts. The first byte is labeled as offset 1.
src_offset OUT Specifies the location of the byte in the source large object after the conversion is complete. The first byte is labeled as offset 1.
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 of 0 is typically used for the setting.
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 ]])

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 the parameter must be the same as the large object specified by the src_lob parameter.
src_lob Specifies the location of the target large object to be copied. The data type of the parameter must be the same as the large object specified by 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. The first position is labeled as offset 1. The default value is 1.
src_offset Specifies the location of the character in the source large object where the copy operation starts. The first location is labeled as offset 1. The default value is 1.

ERASE

The ERASE stored procedure is used to erase a portion of the 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 ])

Parameters

Parameter Description
lob_loc Specifies the large object to be erased.
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 have been 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 from which erasing starts. The first byte or character is labeled as offset 1. The default value is 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)

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)

Parameters

Parameter Description
lob_loc Specifies the location of the large object. You can use the GETLENGTH function to retrieve the length of the 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 number of times in a large object. 

position INTEGER INSTR(lob_loc { BLOB | CLOB },
  pattern { RAW | VARCHAR2 } [, offset INTEGER [, nth INTEGER ]])

Parameters

Parameter Description
lob_loc Specifies the location of the large object in which you can use the INSTR function to search for the specified pattern.
pattern Specifies the pattern to match in the large object. The pattern is a combination of bytes or characters. If the data type of a large object is BLOB, the data type of the pattern must be RAW. If the data type of a large object 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. The default value is 1.
nth Specifies the nth number of times when the pattern appears starting from the specified offset. The default value is 1.
position Specifies the position where the 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 a large object into a buffer. 
READ(lob_loc { BLOB | CLOB }, amount IN OUT BINARY_INTEGER,
  offset INTEGER, buffer OUT { RAW | VARCHAR2 })

Parameters

Parameter Description
lob_loc Specifies the location of 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 are 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 the 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.

SUBSTR

The SUBSTR function is used to retrieve a portion of a large object. 
data { RAW | VARCHAR2 } SUBSTR(lob_loc { BLOB | CLOB }
  [, amount INTEGER [, offset INTEGER ]])

Parameters

Parameter Description
lob_loc Specifies the location of the large object to be read.
amount Specifies the number of bytes or characters to be returned. The default value is 32,767.
offset Specifies the position in the large object to start reading. The first byte or character is labeled as offset 1. The default value is 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 buffer 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)

Parameters

Parameter Description
lob_loc Specifies the location of 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 in the large object 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 })

Parameters

Parameter Description
lob_loc Specifies the location of a 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. The value of the offset starts from 1.
buffer Specifies the data in the buffer to be written to 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 })

Parameters

Parameter Description
lob_loc Specifies the location of the large object to which data is 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 data to be added to 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.