Manage and operate on large objects using DBMS_LOB - PolarDB

The DBMS_LOB package provides subprograms that operate on binary large objects (BLOBs), character large objects (CLOBs), and national character large objects (NCLOBs). Use these subprograms to access and manipulate part or all of a large object (LOB).

Variables

Variable	Data type	Value	Description
CALL	BINARY_INTEGER	12	Creates a temporary LOB with call duration.
DEFAULT_CSID	INTEGER	0	The default character set ID.
DEFAULT_LANG_CTX	INTEGER	0	The default language context.
LOB_READONLY	BINARY_INTEGER	0	Opens the LOB in read-only mode.
LOB_READWRITE	BINARY_INTEGER	1	Opens the LOB in read/write mode.
BLOBMAXSIZE	INTEGER	16777216 (16 MB)	The maximum BLOB size, in bytes.
CLOBMAXSIZE	INTEGER	4194304 (4 MB)	The maximum CLOB size, in bytes.
NO_WARNING	INTEGER	0	Indicates the program ran without errors. No warning is returned.
SESSION	BINARY_INTEGER	10	Creates a temporary LOB with session duration.
TRANSACTION	BINARY_INTEGER	11	Creates a temporary LOB with transaction duration.
WARN_INCONVERTIBLE_CHAR	INTEGER	1	Indicates that a conversion function encountered characters that could not be converted.

Note

In TimesTen, a temporary LOB's duration cannot exceed that of a transaction. The LOB contents are destroyed when the locator becomes invalid at the end of the transaction.

Subprograms

Subprogram	Type	Return type	Description
COPY	Procedure	—	Copies all or part of the source LOB to the destination LOB.
ERASE	Procedure	—	Erases all or part of a LOB.
SUBSTR	Function	BYTEA or TEXT	Returns a portion of a LOB value, starting at a specified offset.
GETLENGTH	Function	INT4	Returns the length of a LOB value. For BLOBs, length is in bytes. For CLOBs, length is in characters.
INSTR	Function	INTEGER	Returns the position of the nth occurrence of a pattern in the LOB.
COMPARE	Function	INT4	Compares two LOBs or portions of two LOBs.
APPEND	Procedure	—	Appends the source LOB to the destination LOB.
READ	Procedure	—	Reads data from a LOB into a buffer, starting at a specified offset.
WRITE	Procedure	—	Writes data from a buffer to a LOB at a specified offset.
WRITEAPPEND	Procedure	—	Appends buffer data to the end of a LOB.
TRIM	Procedure	—	Truncates a LOB to a specified length.
GET_STORAGE_LIMIT	Function	INTEGER	Returns the maximum storage size for the specified LOB.

COPY

Copies data from the source LOB into the destination LOB, starting at the given offsets.

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 OUT  TEXT,
  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 number of bytes (BLOB) or characters (CLOB) to copy from the source LOB.
dest_offset	The 1-based offset in the destination LOB where writing begins. Default: 1.
src_offset	The 1-based offset in the source LOB where reading begins. Default: 1.

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

Erases data from a LOB, starting at the specified offset.

Syntax

DBMS_LOB.ERASE (
  lob_loc   IN 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 LOB to erase data from.
amount	The number of bytes (BLOB) or characters (CLOB) to erase.
p_offset	The 1-based offset where erasing begins. Default: 1.

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

Returns a substring extracted from a LOB, starting at the specified offset.

Returns BYTEA for BLOB input, TEXT for CLOB input.

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 source LOB.
amount	The number of bytes (BLOB) or characters (CLOB) to extract. Default: 32767.
p_offset	The 1-based offset where extraction begins. Default: 1.

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

Returns the length of a LOB value. For BLOBs, the length is in bytes. For CLOBs, the length is in characters.

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 LOB to measure.

Examples

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

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

INSTR

Returns the byte or character position of the nth occurrence of a pattern in a LOB, starting the search at p_offset.

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 LOB to search.
pattern	The byte sequence (BLOB) or substring (CLOB) to search for.
p_offset	The 1-based offset where the search begins. Default: 1.
nth	The occurrence number to find. Default: 1 (first occurrence).

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

Compares two LOBs or portions of 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	The first LOB.
lob_2	The second LOB.
amount	The number of bytes (BLOB) or characters (CLOB) to compare. Default: 2147483647.
offset_1	The 1-based offset in `lob_1` where the comparison begins. Default: 1.
offset_2	The 1-based offset in `lob_2` where the comparison begins. Default: 1.

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

Appends the contents of the source LOB to the end of 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 to append to.
src_lob	The source LOB to append from.

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

Reads data from a LOB into a buffer, starting at the specified offset.

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
lob_loc	The LOB to read from.
amount	The number of bytes (BLOB) or characters (CLOB) to read. Updated on output to reflect the actual number read.
p_offset	The 1-based offset where reading begins.
buffer	The variable that receives the data read from the LOB.

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

Writes data from a buffer into a LOB, starting at the specified offset.

Syntax

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

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

Parameters

Parameter	Description
lob_loc	The LOB to write to.
amount	The number of bytes (BLOB) or characters (CLOB) to write from the buffer.
p_offset	The 1-based offset in the LOB where writing begins.
buffer	The buffer containing the data to write.

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

Writes 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 append to.
amount	The number of bytes (BLOB) or characters (CLOB) to write from the buffer.
buffer	The buffer data to append.

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

Truncates a LOB to the specified length.

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 LOB to truncate.
newlen	The new length, in bytes (BLOB) or characters (CLOB). Data beyond this length is removed.

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

Returns the maximum storage size for the specified LOB.

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 LOB to query.

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