DBMS_LOB - PolarDB - Alibaba Cloud Documentation Center

The DBMS_LOB package provides subprograms that can operate on binary large objects (BLOBs), character large objects (CLOBs), and national character large objects (NCLOBs). You can use the DBMS_LOB package to access and manage the specific parts of a large object (LOB) or the entire LOB.

Variables

Variable	Data type	Variable value	Description
CALL	BINARY_INTEGER	12	Creates a temporary LOB with call duration.
DEFAULT_CSID	INTEGER	0	The ID of the default character set.
DEFAULT_LANG_CTX	INTEGER	0	The default language context.
LOB_READONLY	BINARY_INTEGER	0	Opens the specified LOB in read-only mode.
LOB_READWRITE	BINARY_INTEGER	1	Opens the specified LOB in read/write mode.
BLOBMAXSIZE	INTEGER	16777216 (16 MB)	The maximum size of a BLOB. Unit: bytes.
CLOBMAXSIZE	INTEGER	4194304 (4 MB)	The maximum size of a CLOB. Unit: bytes.
NO_WARNING	INTEGER	0	This variable indicates that the program runs as expected and no warning message is returned.
SESSION	BINARY_INTEGER	10	Creates a temporary LOB with session duration. Note In TimesTen, the duration of the temporary LOB cannot be greater than that of a transaction. The contents of the temporary LOB are destroyed when the corresponding locator becomes invalid at the end of the transaction.
TRANSACTION	BINARY_INTEGER	11	Creates a temporary LOB with transaction duration.
WARN_INCONVERTIBLE_CHAR	INTEGER	1	This variable is used by a conversion function to indicate that characters that cannot be converted exist.

Subprograms

Subprogram	Description
COPY PROCEDURE	Copies all or part of the source LOB to the destination LOB.
ERASE PROCEDURE	Delete all or part of a LOB.
SUBSTR FUNCTION	Returns a portion of a LOB value from a specified offset.
GETLENGTH FUNCTION	Returns the length of a LOB value. The length of a BLOB value is measured in bytes. The length of a CLOB value is measured in characters.
INSTR FUNCTION	Returns the matching position of the nth occurrence of the pattern in the LOB
COMPARE FUNCTION	Compares two entire LOBs or parts of these LOBs.
APPEND PROCEDURE	Appends the contents of the source LOB to the destination LOB.
READ PROCEDURE	Reads data from the specified offset in a LOB.
WRITE PROCEDURE	Writes data to the LOB from a specified offset.
WRITEAPPEND PROCEDURE	Appends the buffer to the end of a LOB.
TRIM PROCEDURE	Modifies a LOB value to a specified length.
GET_STORAGE_LIMIT FUNCTION	Returns the LOB storage limit on a specified LOB.

COPY

This subprogram is used to copy the source LOB to the destination LOB from a specified position.

Syntax

DBMS_LOB.COPY (
  dest_lob        IN OUT 	BYTEA,
  src_lob         IN        BYTEA, 
  amount          IN        INTEGER, 
  dest_offset     IN        INTEGER := 1, 
  src_offset      IN        INTEGER := 1);

DBMS_LOB.COPY (
  dest_lob        IN        OUTTEXT,
  src_lob         IN        TEXT,
  amount          IN        INTEGER,
  dest_offset     IN        INTEGER := 1, 
  src_offset      IN        INTEGER := 1);

Parameters

Parameter	Description
dest_lob	The destination LOB.
src_lob	The source LOB.
amount	The length of data that you want to copy from the source LOB.
dest_offset	The offset of the destination LOB.
src_offset	The offset of the source LOB.

Examples

DECLARE
  dest_lob BLOB;
BEGIN
  dest_lob:='11223344'::raw::BLOB;
  DBMS_LOB.COPY(dest_lob,'AABBCCDDEEFF'::raw::BLOB,6,6,1);
  DBMS_OUTPUT.PUT_LINE(dest_lob);
END;
\x3131323233414142424343

DECLARE
  dest_lob CLOB;
BEGIN
  dest_lob:='11223344';
  DBMS_LOB.COPY(dest_lob,'AABBCCDDEEFF',6,6,1);
  DBMS_OUTPUT.PUT_LINE(dest_lob);
END;
11223AABBCC

ERASE

This subprogram is used to delete some data from a specified position from LOB data.

Syntax

DBMS_LOB.ERASE (
    lob_loIN            OUT         BYTEA,
    amount              IN OUT      INTEGER, 
    p_offset            IN          INTEGER := 1);

DBMS_LOB.ERASE (
    lob_loc             IN OUT      TEXT,
    amount              IN OUT      INTEGER, 
    p_offset            IN          INTEGER := 1);

Parameters

Parameter	Description
lob_loc	The destination LOB.
amount	The length of LOB data that you want to delete.
p_offset	The offset.

Examples

DECLARE
  dest_lob BLOB;
  amount integer;
BEGIN
  dest_lob:='E6B58BE8AF95'::raw::BLOB;
  amount := 2;
  DBMS_LOB.ERASE(dest_lob,amount,3);
  DBMS_OUTPUT.PUT_LINE(dest_lob);
END;
\x4536000038424538414639

DECLARE
  dest_lob CLOB;
  amount integer;
BEGIN
  dest_lob:='E6B58BE8AF95'::CLOB;
  amount := 2;
  DBMS_LOB.ERASE(dest_lob,amount,3);
  DBMS_OUTPUT.PUT_LINE(dest_lob);
END;
E6  8BE8AF9

SUBSTR

This subprogram is used to return substrings that are extracted from LOB data.

Syntax

DBMS_LOB.SUBSTR (
    lob_loc             IN OUT      BYTEA,
    amount              IN OUT	    INTEGER := 32767, 
    p_offset            IN          INTEGER := 1)
RETURNS BYTEA;

DBMS_LOB.SUBSTR (
    lob_loc             IN OUT      TEXT,
    amount              IN OUT      INTEGER := 32767, 
    p_offset            IN          INTEGER := 1)
RETURNS TEXT;

Parameters

Parameter	Description
lob_loc	The destination LOB.
amount	The length of substrings that you want to extract.
p_offset	The offset.

Examples

SELECT DBMS_LOB.SUBSTR('\x112233445566778899'::RAW::BLOB,2,2);
 SUBSTR 
--------
 \x2233
(1 row)

SELECT DBMS_LOB.SUBSTR('AABBCCDD'::CLOB,2,2);
 SUBSTR 
--------
 AB
(1 row)

GETLENGTH

This subprogram is used to return the length of the specified LOB data.

Syntax

DBMS_LOB.GETLENGTH (
    lob_loc             IN OUT 	BYTEA)
RETURNS INT4;

DBMS_LOB.GETLENGTH (
    lob_loc             IN OUT 	TEXT)
RETURNS INT4;

Parameters

Parameter	Description
lob_loc	The destination LOB.

Examples

SELECT DBMS_LOB.GETLENGTH('AABBCCDD'::RAW::BLOB);
 GETLENGTH 
-----------
         8
(1 row)

SELECT DBMS_LOB.GETLENGTH('AABBCCDD'::CLOB);
 GETLENGTH 
-----------
         8
(1 row)

INSTR

This subprogram is used to determine the position of substrings from LOB data.

Syntax

DBMS_LOB.INSTR (
    lob_loc             IN          BYTEA,
    pattern             IN          BYTEA,
    p_offset            IN          INT4 := 1,
    nth                 IN          INT4 := 1)
RETURNS INTEGER;

DBMS_LOB.INSTR (
    lob_loc             IN          TEXT,
    pattern             IN          TEXT,
    p_offset            IN          INT4 := 1,
    nth                 IN          INT4 := 1)
RETURNS INTEGER;

Parameters

Parameter	Description
lob_loc	The destination LOB.
pattern	The child LOB.
p_offset	The offset.
nth	The number of substring occurrences.

Examples

SELECT DBMS_LOB.INSTR('\x112233445566778899'::RAW::BLOB,'\x22'::RAW,1,1);
 INSTR 
-------
     2
(1 row)

SELECT DBMS_LOB.INSTR('112233445566778899'::CLOB,'22'::CLOB,1,1);
 INSTR 
-------
     3
(1 row)

COMPARE

This subprogram is used to compare two LOBs.

Syntax

DBMS_LOB.COMPARE (
    lob_1           IN          BYTEA,
    lob_2           IN          BYTEA,
    amount          IN          INT4 := 2147483647,
    offset_1        IN          INTEGER := 1,
    offset_2        IN          INTEGER := 1)
RETURNS INT4;

DBMS_LOB.COMPARE (
    lob_1           IN          TEXT,
    lob_2           IN          TEXT,
    amount          IN          INT4 := 2147483647,
    offset_1        IN          INTEGER := 1,
    offset_2        IN          INTEGER := 1)
RETURNS INT4;

Parameters

Parameter	Description
lob_1	LOB 1.
lob_2	LOB 2.
amount	The length of LOB data that you want to compare.
offset_1	The offset of LOB 1.
offset_2	The offset of LOB 2.

Examples

SELECT DBMS_LOB.COMPARE('\x112233445566778899'::BLOB,'\x111133445566778899'::BLOB,1,2,2);
 COMPARE 
---------
       1
(1 row)


SELECT DBMS_LOB.COMPARE('E6'::CLOB,'E6B58BE8AF95'::CLOB,6,1,1);
 COMPARE 
---------
      -1
(1 row)

APPEND

This subprogram is used to append data in the specified child LOB to the destination LOB.

Syntax

DBMS_LOB.APPEND (
    dest_lob            IN          BYTEA,
    src_lob             IN          BYTEA);

DBMS_LOB.APPEND (
    dest_lob            IN          TEXT,
    src_lob             IN          TEXT);

Parameters

Parameter	Description
dest_lob	The destination LOB.
src_lob	The child LOB.

Examples

DECLARE
  a BLOB:='\x41414242'::RAW::BLOB;
BEGIN
  DBMS_LOB.APPEND(a,'\x'::RAW::BLOB);
  DBMS_OUTPUT.PUT_LINE(a);
END;
\x41414242

DECLARE
  a CLOB:='AABB'::CLOB;
BEGIN
  DBMS_LOB.APPEND(a,'CCDD'::CLOB);
  DBMS_OUTPUT.PUT_LINE(a);
END;
AABBCCDD

READ

This subprogram is used to read data of a specified length from LOB data to a buffer or a variable.

Syntax

DBMS_LOB.READ (
    lob_loc             IN          BYTEA,
    amount              IN OUT      INTEGER,
    p_offset            IN          INTEGER,
    buffer              OUT         BYTEA);

DBMS_LOB.READ (
    lob_loc             IN          TEXT,
    amount              IN OUT      INTEGER,
    p_offset            IN          INTEGER,
    buffer              OUT         TEXT);

Parameters

Parameter	Description
dest_lob	The destination LOB.
amount	The length of data that you want to write.
p_offset	The offset.
buffer	The object to which you want to write data.

Examples

DECLARE
  dest_lob BLOB;
  a raw;
  m int;
BEGIN
  m:=2;
  dest_lob:='E6B58BE8AF95'::raw::BLOB;
  DBMS_LOB.READ(dest_lob,m,4,a);
  DBMS_OUTPUT.PUT_LINE(a || ',' || m);
END;
\x3538,2

DECLARE
  dest_lob CLOB;
  a text;
  m int;
BEGIN
  m:=20;
  dest_lob:='E6B58BE8AF95'::CLOB;
  DBMS_LOB.READ(dest_lob,m,3,a);
  DBMS_OUTPUT.PUT_LINE(a || ',' || m);
END;
B58BE8AF95,10

WRITE

This subprogram is used to write the specified amount of data to a LOB.

Syntax

DBMS_LOB.WRITE (
    lob_loc             IN OUT      BYTEA,
    amount              IN          INTEGER,
    p_offset            IN          INTEGER,
    buffer              OUT         BYTEA);

DBMS_LOB.WRITE (
    lob_lo              IN OUT      TEXT,
    amount              IN	        INTEGER,
    p_offset            IN          INTEGER,
    buffer              OUT         TEXT);

Parameters

Parameter	Description
dest_lob	The destination LOB.
amount	The length of data that you want to write.
p_offset	The offset.
buffer	The buffer variable.

Examples

DECLARE
  dest_lob BLOB;
BEGIN
  dest_lob:='\x112233445566778899'::raw::BLOB;
  DBMS_LOB.WRITE(dest_lob,2,4,'\xAABBCCDDEEFF'::raw::BLOB);
  DBMS_OUTPUT.PUT_LINE(dest_lob);
END;
\x112233aabb66778899

DECLARE
  dest_lob CLOB;
BEGIN
  dest_lob:='112233445566778899'::CLOB;
  DBMS_LOB.WRITE(dest_lob,2,4,'AABBCCDDEEFF'::CLOB);
  DBMS_OUTPUT.PUT_LINE(dest_lob);
END;
112AA3445566778899

WRITEAPPEND

This subprogram is used to write data from a buffer to the end of a LOB.

Syntax

DBMS_LOB.WRITEAPPEND (
    lob_loc             IN OUT      BYTEA,
    amount              IN          INTEGER,
    buffer              IN          BYTEA);

DBMS_LOB.WRITEAPPEND (
    lob_loc             IN OUT 	    TEXT,
    amount              IN          INTEGER,
    buffer              IN          TEXT);

Parameters

Parameter	Description
lob_loc	The LOB to which you want to write data.
amount	The length of data that you want to write.
buffer	The buffer data.

Examples

DECLARE
  lob_loc BLOB:='\x112233'::RAW::BLOB;
BEGIN
  DBMS_LOB.WRITEAPPEND(lob_loc,5,'\xAABBCCDDEE'::RAW);
  DBMS_OUTPUT.PUT_LINE(lob_loc);
END;
\x112233aabbccddee

DECLARE
  lob_loc CLOB:='112233'::CLOB;
BEGIN
  DBMS_LOB.WRITEAPPEND(lob_loc,10,'AABBCCDDEE'::text);
  DBMS_OUTPUT.PUT_LINE(lob_loc);
END;
112233AABBCCDDEE

TRIM

This subprogram is used to truncate some data of a specified length from the first position in LOB data.

Syntax

DBMS_LOB.TRIM (
    lob_loc             IN OUT 	    BYTEA,
    newlen              IN          INTEGER);

DBMS_LOB.TRIM (
    lob_loc             IN OUT      TEXT,
    newlen              IN          INTEGER);

Parameters

Parameter	Description
lob_loc	The destination LOB.
newlen	The length of data that you want to truncate.

Examples

DECLARE
  dest_lob BLOB;
  newlen int;
BEGIN
  newlen:=2;
  dest_lob:='\xE6B58BE8AF95'::raw::BLOB;
  DBMS_LOB.TRIM(dest_lob,newlen);
  DBMS_OUTPUT.PUT_LINE(dest_lob);
END;
  \xe6b5

DECLARE
  dest_lob CLOB;
  newlen int;
BEGIN
  newlen:=2;
  dest_lob:='E6B58BE8AF95'::CLOB;
  DBMS_LOB.TRIM(dest_lob,newlen);
  DBMS_OUTPUT.PUT_LINE(dest_lob);
END;
  E6

GET_STORAGE_LIMIT

This subprogram is used to return the maximum storage size of LOB data.

Syntax

DBMS_LOB.GET_STORAGE_LIMIT (
    lob_loc             IN OUT 	    BYTEA);

DBMS_LOB.GET_STORAGE_LIMIT (
    lob_loc             IN OUT      TEXT);

Parameters

Parameter	Description
lob_loc	The destination LOB.

Examples

DECLARE
  dest_lob BLOB;
BEGIN
  dest_lob:='\xE6B58BE8AF95'::raw::BLOB;
  DBMS_OUTPUT.PUT_LINE(DBMS_LOB.GET_STORAGE_LIMIT(dest_lob));
END;
2147483647

DECLARE
  dest_lob CLOB;
BEGIN
  dest_lob:='E6B58BE8AF95'::CLOB;
  DBMS_OUTPUT.PUT_LINE(DBMS_LOB.GET_STORAGE_LIMIT(dest_lob));
END;
2147483647