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 })
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 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 ]]])
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 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)
Table 5. 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)
Table 6. 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 ]])
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 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 ])
Table 8. 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)
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 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 ]])
Table 11. 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 })
Table 12. 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 ]])
Table 13. 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)
Table 14. 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 })
Table 15. 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 })
Table 16. 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.
|