The DBMS_LOB package is used to perform operations on large objects.
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.
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 })
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 ]]])
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 |
|
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 |
|
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. |
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 |
|
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. |
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 |
|
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 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 ])
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)
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 name of the large object. |
amount | Specifies the length of the large object.
|
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 ]])
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.
|
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 })
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.
|
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 ]])
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.
|
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 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 })
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.
|
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 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.
|