The DBMS_LOB package can read and modify binary large objects (BLOBs) and character large objects (CLOBs). It provides most of the large object (LOB) operations.
Privileges
You must create this package as a SYS user. However, the operations that the package provides are performed under the current caller instead of the SYS user.
DBMS_LOB subprograms called from an anonymous procedural language (PL) block are executed under the current user, whereas DBMS_LOB subprograms called from a stored procedure are executed by the owner of the stored procedure.
When you create a subprogram, you can set the AUTHID parameter to specify whether the authorization of the subprogram definer or the caller is required. Example:
CREATE PROCEDURE proc1 AUTHID DEFINER ...
or
CREATE PROCEDURE proc1 AUTHID CURRENT_USER ...
Data types
| Data type | Description |
|---|---|
| BLOB | The source or destination binary LOB. |
| RAW | The source or destination RAW buffer. It is used together with BLOB. |
| CLOB | The source or destination character LOB. |
| VARCHAR2 | The source or destination character buffer. It is used together with CLOB. |
| INTEGER | The size of a buffer or LOB, the offset into a LOB, or the amount of data to access. |
Usage notes
All DBMS_LOB subprograms rely on the LOB locator to work. For the successful execution of DBMS_LOB subprograms, you must pass a locator that identifies a LOB that already exists in the database tablespace or external file system.
All DBMS_LOB APIs support operations on remote LOB locators. All APIs that take in two locators must have both LOBs collocated in the same database.
Subprograms
The following table describes the DBMS_LOB subprograms supported by the current OceanBase Database version.
| Subprogram | Description |
|---|---|
| APPEND | Appends the content of a source LOB to a destination LOB. |
| CLOSE | Closes an internal or external LOB that was previously opened. |
| COPY | Copies all or part of a source internal LOB to a destination internal LOB. |
| COMPARE | Compares two LOBs in full or in part. |
| CREATETEMPORARY | Creates a temporary BLOB or CLOB and a corresponding index in the default temporary tablespace. |
| FREETEMPORARY | Releases temporary BLOBs or CLOBs in the default temporary tablespace. |
| ERASE | Erases all or some LOBs. |
| GETLENGTH | Obtains the length of a LOB. |
| ISOPEN | Checks whether a LOB is already opened by an input locator. |
| INSTR | Returns the matching position of the nth occurrence of a pattern in a LOB. |
| OPEN | Opens an internal, external, or temporary LOB in a specified mode. |
| READ | Reads data from a LOB starting from the specified offset. |
| SUBSTR | Returns part of a LOB starting from the specified offset. |
| TRIM | Trims a LOB to the specified length. |
| WRITE | Writes data to a LOB starting from the specified offset. |
| WRITEAPPEND | Appends a buffer to the end of a LOB. |