DBMS_LOB v11

The DBMS_LOB package provides the capability to operate on large objects. The following table lists the supported functions and procedures:

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/aErase a large object.
GET_STORAGE_LIMIT(lob_loc)INTEGERGet the storage limit for large objects.
GETLENGTH(lob_loc)INTEGERGet the length of the large object.
INSTR(lob_loc, pattern [, offset [, nth ]])INTEGERGet 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/aRead a large object.
SUBSTR(lob_loc [, amount [, offset ]])RAW, VARCHAR2Get part of a large object.
TRIM(lob_loc IN OUT, newlen)n/aTrim a large object to the specified length.
WRITE(lob_loc IN OUT, amount, offset, buffer)n/aWrite data to a large object.
WRITEAPPEND(lob_loc IN OUT, amount, buffer)n/aWrite data from the buffer to the end of a large object.

Advanced Server's implementation of DBMS_LOB is a partial implementation when compared to Oracle's version. Only those functions and procedures listed in the table above are supported.

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

In the following sections, lengths and offsets are measured in bytes if the large objects are BLOBs. Lengths and offsets are measured in characters if the large objects are CLOBs.

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