The DBMS_LOB package is used to perform operations on large objects.
| 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.
| 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 })| 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 ]]])| 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)| 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)| 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 ]])| 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 ])| 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)| 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)| 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 ]])| 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 })| 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 ]])| 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)| 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 })| 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 })| 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. |