DBMS_LOB v17
The DBMS_LOB package lets you operate on large objects. The following table lists the supported functions and procedures. EDB Postgres Advanced Server's implementation of DBMS_LOB is a partial implementation when compared to Oracle's version. Only the functions and procedures listed in this table are supported.
| Function/procedure | Return type | Description |
|---|---|---|
APPEND(dest_lob IN OUT, src_lob) | n/a | Appends one large object to another. |
CLOSE(file_loc IN OUT) | n/a | Closes an open BFILE. |
COMPARE(lob_1, lob_2 [, amount [, offset_1 [, offset_2 ]]]) | NUMBER | Compares two large objects. |
CONVERTOBLOB(dest_lob IN OUT, src_clob, amount, dest_offset IN OUT, src_offset IN OUT, blob_csid, lang_context IN OUT, warning OUT) | n/a | Converts character data to binary. |
CONVERTTOCLOB(dest_lob IN OUT, src_blob, amount, dest_offset IN OUT, src_offset IN OUT, blob_csid, lang_context IN OUT, warning OUT) | n/a | Converts binary data to character. |
COPY(dest_lob IN OUT, src_lob, amount [, dest_offset [, src_offset ]]) | n/a | Copies one large object to another. |
ERASE(lob_loc IN OUT, amount IN OUT [, offset ]) | n/a | Erases a large object. |
FILECLOSE(file_loc IN OUT) | n/a | Closes an open BFILE. |
FILECLOSEALL | n/a | Closes all open BFILEs. |
FILEEXISTS(file_loc) | NUMBER | Determines whether a BFILE exists. |
FILEGETNAME(file_loc, dir_alias OUT, filename OUT) | n/a | Determines directory alias and filename for BFILE locator. |
FILEOPEN(file_loc IN OUT, open_mode) | n/a | Opens a read-only BFILE. |
FILEISOPEN(file_loc) | NUMBER | Determines whether a BFILE was opened with a FILE locator. |
GET_STORAGE_LIMIT(lob_loc) | NUMBER | Gets the storage limit for large objects. |
GETLENGTH(lob_loc | file_loc) | NUMBER | Gets the length of the large object or BFILE. |
INSTR(lob_loc | file_loc, pattern [, offset [, nth ]]) | NUMBER | Gets the position of the nth occurrence of a pattern in a large object or BFILE starting at offset. |
ISOPEN(file_loc) | NUMBER | Determines whether a BFILE was opened with a FILE locator. |
LOADBLOBFROMFILE(dest_lob IN OUT, src_bfile, amount, dest_offset IN OUT, src_offset IN OUT) | n/a | Loads data from BFILE to internal BLOB. |
LOADCLOBFROMFILE(dest_lob IN OUT, src_bfile, amount, dest_offset IN OUT, src_offset IN OUT, bfile_csid, lang_context IN OUT, warning OUT) | n/a | Loads data from BFILE to internal CLOB. |
OPEN(file_loc IN OUT, open_mode) | n/a | Opens a read-only BFILE. |
READ(lob_loc | file_loc, amount IN OUT, offset, buffer OUT) | n/a | Reads a large object or BFILE. |
SUBSTR(lob_loc | file_loc [, amount [, offset ]]) | RAW, VARCHAR2 | Gets part of a large object or BFILE. |
TRIM(lob_loc IN OUT, newlen) | n/a | Trims a large object to the specified length. |
WRITE(lob_loc IN OUT, amount, offset, buffer) | n/a | Writes data to a large object. |
WRITEAPPEND(lob_loc IN OUT, amount, buffer) | n/a | Writes data from the buffer to the end of a large object. |
The following table lists the public variables available in the package.
| Public variables | Data type | Value |
|---|---|---|
compress off | NUMBER | 0 |
compress_on | NUMBER | 1 |
deduplicate_off | NUMBER | 0 |
deduplicate_on | NUMBER | 4 |
default_csid | NUMBER | 0 |
default_lang_ctx | NUMBER | 0 |
encrypt_off | NUMBER | 0 |
encrypt_on | NUMBER | 1 |
file_readonly | NUMBER | 0 |
lobmaxsize | NUMBER | 1073741823 |
lob_readonly | NUMBER | 0 |
lob_readwrite | NUMBER | 1 |
no_warning | NUMBER | 0 |
opt_compress | NUMBER | 1 |
opt_deduplicate | NUMBER | 4 |
opt_encrypt | NUMBER | 2 |
warn_inconvertible_char | NUMBER | 1 |
If the large objects are BLOB, lengths and offsets are measured in bytes. If the large objects are CLOB, lengths and offsets are measured in characters.
append compare converttoblob converttoclob copy erase get_storage_limit getlength instr read substr trim write writeappend