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/procedureReturn typeDescription
APPEND(dest_lob IN OUT, src_lob)n/aAppends one large object to another.
CLOSE(file_loc IN OUT)n/aCloses an open BFILE.
COMPARE(lob_1, lob_2 [, amount [, offset_1 [, offset_2 ]]])NUMBERCompares 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.
FILECLOSE(file_loc IN OUT)n/aCloses an open BFILE.
FILECLOSEALLn/aCloses all open BFILEs.
FILEEXISTS(file_loc)NUMBERDetermines whether a BFILE exists.
FILEGETNAME(file_loc, dir_alias OUT, filename OUT)n/aDetermines directory alias and filename for BFILE locator.
FILEOPEN(file_loc IN OUT, open_mode)n/aOpens a read-only BFILE.
FILEISOPEN(file_loc)NUMBERDetermines whether a BFILE was opened with a FILE locator.
GET_STORAGE_LIMIT(lob_loc)NUMBERGets the storage limit for large objects.
GETLENGTH(lob_loc | file_loc)NUMBERGets the length of the large object or BFILE.
INSTR(lob_loc | file_loc, pattern [, offset [, nth ]])NUMBERGets the position of the nth occurrence of a pattern in a large object or BFILE starting at offset.
ISOPEN(file_loc)NUMBERDetermines 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/aLoads 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/aLoads data from BFILE to internal CLOB.
OPEN(file_loc IN OUT, open_mode)n/aOpens a read-only BFILE.
READ(lob_loc | file_loc, amount IN OUT, offset, buffer OUT)n/aReads a large object or BFILE.
SUBSTR(lob_loc | file_loc [, amount [, offset ]])RAW, VARCHAR2Gets part of a large object or BFILE.
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 offNUMBER0
compress_onNUMBER1
deduplicate_offNUMBER0
deduplicate_onNUMBER4
default_csidNUMBER0
default_lang_ctxNUMBER0
encrypt_offNUMBER0
encrypt_onNUMBER1
file_readonlyNUMBER0
lobmaxsizeNUMBER1073741823
lob_readonlyNUMBER0
lob_readwriteNUMBER1
no_warningNUMBER0
opt_compressNUMBER1
opt_deduplicateNUMBER4
opt_encryptNUMBER2
warn_inconvertible_charNUMBER1

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