Overview of DBMS_LOB

The DBMS_LOB system package provides most LOB operations for reading and modifying BLOB and CLOB data.

DBMS_LOB Privileges

This system package must be created under SYS. However, the operations provided by this system package are executed under the current calling user, not under SYS.

Any DBMS_LOB subprogram called from an anonymous PL block is executed with the privileges of the current user. Any DBMS_LOB subprogram called from a stored subprogram is executed with the privileges of the owner of the stored subprogram.

When creating a subprogram, you can set AUTHID to specify whether the privileges of the definer or caller are required. As shown in the following example:

CREATE PROCEDURE proc1 AUTHID DEFINER ...

CREATE PROCEDURE proc1 AUTHID CURRENT_USER ...

Usage instructions for DBMS_LOB

| Data types | Description |

| RAW | Source or TargetRAWbuffer pool (andBLOB(used together). | | CLOB | Source or Target CharacterLOB. | | VARCHAR2 | Source or destination character buffer (andCLOB(used together). | | INTEGER | Specify the buffer orLOBthe size of the,LOBThe offset or the number of elements to access. | All DBMS_LOB APIs now support operations on remote LOB locators. All APIs that use two locators must place the two LOBs in the same database.

Overview of DBMS_LOB Subprograms

The following table lists the DBMS_LOB subprograms supported by the current version of OceanBase Database and provides brief descriptions.

Overview of DBMS_LOB Subprograms

The following table lists the DBMS_LOB subprograms supported by the current version of OceanBase Database and provides brief descriptions.

| Subprogram | Description |

| CLOSE | Close any previously opened internal or external files.LOB. |

| COPY | Move the source internalLOBof all or part of the data to the internal storage of the destination database.LOB. | | CREATETEMPORARY | Create a temporary table in the default temporary tablespace.BLOBorCLOBand its corresponding index. | | ERASE | Erase All or Part of the DataLOB. | | FREETEMPORARY | Release temporary files in the default temporary tablespace.BLOBorCLOB. | | GETLENGTH | GetLOBThe length of the value. | | ISOPEN | Check whether it has already been opened with the input locator.LOB. | | INSTR | Return inLOBIn the mode, the firstnThe next occurrence of the match. | | ISTEMPORARY | Check whether the locator points to a temporary LOB. | | OPEN | Open in the specified mode.LOB(Internal, external, or temporary). | | READ | Read from the specified offsetLOBdata. | | SUBSTR | Return a portion starting from the specified offsetLOBValue. | | TRIM | toLOBThe value is trimmed to a specified shorter length. | | WRITE | Write data starting from the specified offset.LOB. | | WRITEAPPEND | Write buffer toLOBat the end of the line. | | SUBSTR | Return a portion starting from the specified offset.LOBValue. | | TRIM | toLOBThe value is trimmed to a specified shorter length. | | WRITE | Write data starting from the specified offset.LOB. | | WRITEAPPEND | Write buffer toLOBat the end of the line. |