DBMS_LOB v16

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/procedureReturn typeDescription
APPEND(dest_lob IN OUT, src_lob)n/aAppends one large object to another.
COMPARE(lob_1, lob_2 [, amount [, offset_1 [, offset_2 ]]])INTEGERCompares 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/aConverts 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/aConverts binary data to character.
COPY(dest_lob IN OUT, src_lob, amount [, dest_offset [, src_offset ]])n/aCopies one large object to another.
ERASE(lob_loc IN OUT, amount IN OUT [, offset ])n/aErases a large object.
GET_STORAGE_LIMIT(lob_loc)INTEGERGets the storage limit for large objects.
GETLENGTH(lob_loc)INTEGERGets the length of the large object.
INSTR(lob_loc, pattern [, offset [, nth ]])INTEGERGets the position of the nth occurrence of a pattern in the large object starting at offset
READ(lob_loc, amount IN OUT, offset, buffer OUT)n/aReads a large object.
SUBSTR(lob_loc [, amount [, offset ]])RAW, VARCHAR2Gets part of a large object.
TRIM(lob_loc IN OUT, newlen)n/aTrims a large object to the specified length.
WRITE(lob_loc IN OUT, amount, offset, buffer)n/aWrites data to a large object.
WRITEAPPEND(lob_loc IN OUT, amount, buffer)n/aWrites data from the buffer to the end of a large object.

The following table lists the public variables available in the package.

Public variablesData typeValue
compress offINTEGER0
compress_onINTEGER1
deduplicate_offINTEGER0
deduplicate_onINTEGER4
default_csidINTEGER0
default_lang_ctxINTEGER0
encrypt_offINTEGER0
encrypt_onINTEGER1
file_readonlyINTEGER0
lobmaxsizeINTEGER1073741823
lob_readonlyINTEGER0
lob_readwriteINTEGER1
no_warningINTEGER0
opt_compressINTEGER1
opt_deduplicateINTEGER4
opt_encryptINTEGER2
warn_inconvertible_charINTEGER1

Lengths and offsets are measured in bytes if the large objects are BLOB. Lengths and offsets are measured in characters if the large objects are CLOB.

append compare converttoblob converttoclob copy erase get_storage_limit getlength instr read substr trim write writeappend