DBMS_LOB

Related Interfaces

Table 1 provides all interfaces supported by the DBMS_LOB package.

• DBMS_LOB.GETLENGTH

  Specifies the length of a LOB type object obtained and returned by the stored procedure GETLENGTH.

  The function prototype of DBMS_LOB.GETLENGTH is:

  1 2 3 4 5 6 7

  DBMS_LOB.GETLENGTH ( lob_loc IN BLOB) RETURN INTEGER; DBMS_LOB.GETLENGTH ( lob_loc IN CLOB) RETURN INTEGER;

  Table 2 DBMS_LOB.GETLENGTH interface parameters

  Parameter	Description
  lob_loc	LOB type object whose length is to be obtained

• DBMS_LOB.OPEN

  A stored procedure opens a LOB and returns a LOB descriptor. This process is used only for compatibility.

  The function prototype of DBMS_LOB.OPEN is:

  1 2 3 4 5 6 7

  DBMS_LOB.LOB ( lob_loc INOUT BLOB, open_mode IN BINARY_INTEGER); DBMS_LOB.LOB ( lob_loc INOUT CLOB, open_mode IN BINARY_INTEGER);

  Table 3 DBMS_LOB.OPEN interface parameters

  Parameter	Description
  lob_loc	BLOB or CLOB descriptor that is opened
  open_mode IN BINARY_INTEGER	Open mode (currently, DBMS_LOB.LOB_READWRITE is supported)

• DBMS_LOB.READ

  The stored procedure READ loads a part of LOB contents to BUFFER according to the specified length and initial position offset.

  The function prototype of DBMS_LOB.READ is:

  1 2 3 4 5 6 7 8 9 10 11

  DBMS_LOB.READ ( lob_loc IN BLOB, amount IN INTEGER, offset IN INTEGER, buffer OUT RAW); DBMS_LOB.READ ( lob_loc IN CLOB, amount IN OUT INTEGER, offset IN INTEGER, buffer OUT VARCHAR2);

  Table 4 DBMS_LOB.READ interface parameters

  Parameter	Description
  lob_loc	LOB type object to be loaded
  amount	Load data length NOTE: If the read length is negative, the error message "ERROR: argument 2 is null, invalid, or out of range." is displayed.
  offset	Indicates where to start reading the LOB contents, that is, the offset bytes to initial position of LOB contents.
  buffer	Target buffer to store the loaded LOB contents

• DBMS_LOB.WRITE

  The stored procedure WRITE copies contents in BUFFER to LOB variables according to the specified length and initial position offset.

  The function prototype of DBMS_LOB.WRITE is:

  1 2 3 4 5 6 7 8 9 10 11

  DBMS_LOB.WRITE ( lob_loc IN OUT BLOB, amount IN INTEGER, offset IN INTEGER, buffer IN RAW); DBMS_LOB.WRITE ( lob_loc IN OUT CLOB, amount IN INTEGER, offset IN INTEGER, buffer IN VARCHAR2);

  Table 5 DBMS_LOB.WRITE interface parameters

  Parameter	Description
  lob_loc	LOB type object to be written
  amount	Write data length NOTE: If the write data is shorter than 1 or longer than the contents to be written, an error is reported.
  offset	Indicates where to start writing the LOB contents, that is, the offset bytes to initial position of LOB contents. NOTE: If the offset is shorter than 1 or longer than the maximum length of LOB type contents, an error is reported.
  buffer	Content to be written

• DBMS_LOB.WRITEAPPEND

  The stored procedure WRITEAPPEND copies contents in BUFFER to the end part of LOB according to the specified length.

  The function prototype of DBMS_LOB.WRITEAPPEND is:

  1 2 3 4 5 6 7 8 9

  DBMS_LOB.WRITEAPPEND ( lob_loc IN OUT BLOB, amount IN INTEGER, buffer IN RAW); DBMS_LOB.WRITEAPPEND ( lob_loc IN OUT CLOB, amount IN INTEGER, buffer IN VARCHAR2);

  Table 6 DBMS_LOB.WRITEAPPEND interface parameters

  Parameter	Description
  lob_loc	LOB type object to be written
  amount	Write data length NOTE: If the write data is shorter than 1 or longer than the contents to be written, an error is reported.
  buffer	Content to be written

• DBMS_LOB.COPY

  The stored procedure COPY copies contents in BLOB to another BLOB according to the specified length and initial position offset.

  The function prototype of DBMS_LOB.COPY is:

  1 2 3 4 5 6

  DBMS_LOB.COPY ( dest_lob IN OUT BLOB, src_lob IN BLOB, amount IN INTEGER, dest_offset IN INTEGER DEFAULT 1, src_offset IN INTEGER DEFAULT 1);

  Table 7 DBMS_LOB.COPY interface parameters

  Parameter	Description
  dest_lob	BLOB type object to be pasted
  src_lob	BLOB type object to be copied
  amount	Replication length. NOTE: If the copied data is shorter than 1 or longer than the maximum length of BLOB type contents, an error is reported.
  dest_offset	Indicates where to start pasting the BLOB contents, that is, the offset bytes to initial position of BLOB contents. NOTE: If the offset is shorter than 1 or longer than the maximum length of BLOB type contents, an error is reported.
  src_offset	Indicates where to start copying the BLOB contents, that is, the offset bytes to initial position of BLOB contents. NOTE: If the offset is shorter than 1 or longer than the length of source BLOB, an error is reported.

• DBMS_LOB.ERASE

  The stored procedure ERASE deletes contents in BLOB according to the specified length and initial position offset.

  The function prototype of DBMS_LOB.ERASE is:

  1 2 3 4

  DBMS_LOB.ERASE ( lob_loc IN OUT BLOB, amount IN OUT INTEGER, offset IN INTEGER DEFAULT 1);

  Table 8 DBMS_LOB.ERASE interface parameters

  Parameter	Description
  lob_loc	BLOB type object whose contents are to be deleted
  amount	Length of contents to be deleted NOTE: If the deleted data is shorter than 1 or longer than the maximum length of BLOB type contents, an error is reported.
  offset	Indicates where to start deleting the BLOB contents, that is, the offset bytes to initial position of BLOB contents. NOTE: If the offset is shorter than 1 or longer than the maximum length of BLOB type contents, an error is reported.

• DBMS_LOB.CLOSE

  The procedure CLOSE disables the enabled contents of LOB according to the specified length and initial position offset.

  The function prototype of DBMS_LOB.CLOSE is:

  1 2 3 4 5

  DBMS_LOB.CLOSE( src_lob IN BLOB); DBMS_LOB.CLOSE ( src_lob IN CLOB);

  Table 9 DBMS_LOB.CLOSE interface parameters

  Parameter	Description
  src_loc	LOB type object to be disabled

• DBMS_LOB.INSTR

  This function returns the Nth occurrence position in LOB. If invalid values are entered, NULL is returned. The invalid values include offset < 1 or offset > LOBMAXSIZE, nth < 1, and nth > LOBMAXSIZE.

  The function prototype of DBMS_LOB.INSTR is:

  1 2 3 4 5 6 7 8 9 10 11 12 13

  DBMS_LOB.INSTR ( lob_loc IN BLOB, pattern IN RAW, offset IN INTEGER := 1, nth IN INTEGER := 1) RETURN INTEGER; DBMS_LOB.INSTR ( lob_loc IN CLOB, pattern IN VARCHAR2 , offset IN INTEGER := 1, nth IN INTEGER := 1) RETURN INTEGER;

  Table 10 DBMS_LOB.INSTR interface parameters

  Parameter	Description
  lob_loc	LOB descriptor to be searched for
  pattern	Matched pattern. It is RAW for BLOB and TEXT for CLOB.
  offset	For BLOB, the absolute offset is in the unit of byte. For CLOB, the offset is in the unit of character. The matching start position is 1.
  nth	Number of pattern matching times. The minimum value is 1.

• DBMS_LOB.COMPARE

  This function compares two LOBs or a certain part of two LOBs.

  • If the two parts are equal, 0 is returned. Otherwise, a non-zero value is returned.
  • If the first CLOB is smaller than the second, -1 is returned. If the first CLOB is larger than the second, 1 is returned.
  • If any of the amount, offset_1, and offset_2 parameters is invalid, NULL is returned. The valid offset range is 1 to LOBMAXSIZE.

  The function prototype of DBMS_LOB.READ is:

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

  DBMS_LOB.COMPARE ( lob_1 IN BLOB, lob_2 IN BLOB, amount IN INTEGER := DBMS_LOB.LOBMAXSIZE, offset_1 IN INTEGER := 1, offset_2 IN INTEGER := 1) RETURN INTEGER; DBMS_LOB.COMPARE ( lob_1 IN CLOB, lob_2 IN CLOB, amount IN INTEGER := DBMS_LOB.LOBMAXSIZE, offset_1 IN INTEGER := 1, offset_2 IN INTEGER := 1) RETURN INTEGER;

  Table 11 DBMS_LOB.COMPARE interface parameters

  Parameter	Description
  lob_1	First LOB descriptor to be compared
  lob_2	Second LOB descriptor to be compared
  amount	Number of characters or bytes to be compared. The maximum value is DBMS_LOB.LOBMAXSIZE.
  offset_1	Offset of the first LOB descriptor. The initial position is 1.
  offset_2	Offset of the second LOB descriptor. The initial position is 1.

• DBMS_LOB.SUBSTR

  This function reads the substring of a LOB and returns the number of read bytes or the number of characters. If amount > 1, amount < 32767, offset < 1, or offset > LOBMAXSIZE, NULL is returned.

  The function prototype of DBMS_LOB.SUBSTR is:

  1 2 3 4 5 6 7 8 9 10 11

  DBMS_LOB.SUBSTR ( lob_loc IN BLOB, amount IN INTEGER := 32767, offset IN INTEGER := 1) RETURN RAW; DBMS_LOB.SUBSTR ( lob_loc IN CLOB, amount IN INTEGER := 32767, offset IN INTEGER := 1) RETURN VARCHAR2;

  Table 12 DBMS_LOB.SUBSTR interface parameters

  Parameter	Description
  lob_loc	LOB descriptor of the substring to be read. For BLOB, the return value is the number of read bytes. For CLOB, the return value is the number of characters.
  offset	Number of bytes or characters to be read.
  buffer	Number of characters or bytes offset from the start position.

• DBMS_LOB.TRIM

  This stored procedure truncates the LOB of a specified length. After this stored procedure is executed, the length of the LOB is set to the length specified by the newlen parameter. If an empty LOB is truncated, no execution result is displayed. If the specified length is longer than the length of LOB, an exception occurs.

  The function prototype of DBMS_LOB.TRIM is:

  1 2 3 4 5 6 7

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

  Table 13 DBMS_LOB.TRIM interface parameters

  Parameter	Description
  lob_loc	BLOB type object to be read
  newlen	After truncation, the new LOB length for BLOB is in the unit of byte and that for CLOB is in the unit of character.

• DBMS_LOB.CREATETEMPORARY

  This stored procedure creates a temporary BLOB or CLOB and is used only for syntax compatibility.

  The function prototype of DBMS_LOB.CREATETEMPORARY is:

  1 2 3 4 5 6 7 8 9

  DBMS_LOB.CREATETEMPORARY ( lob_loc IN OUT BLOB, cache IN BOOLEAN, dur IN INTEGER); DBMS_LOB.CREATETEMPORARY ( lob_loc IN OUT CLOB, cache IN BOOLEAN, dur IN INTEGER);

  Table 14 DBMS_LOB.CREATETEMPORARY interface parameters

  Parameter	Description
  lob_loc	LOB descriptor
  cache	This parameter is used only for syntax compatibility.
  dur	This parameter is used only for syntax compatibility.

• DBMS_LOB.APPEND

  The stored procedure READ loads a part of BLOB contents to BUFFER according to the specified length and initial position offset.

  The function prototype of DBMS_LOB.APPEND is:

  1 2 3 4 5 6 7

  DBMS_LOB.APPEND ( dest_lob IN OUT BLOB, src_lob IN BLOB); DBMS_LOB.APPEND ( dest_lob IN OUT CLOB, src_lob IN CLOB);

  Table 15 DBMS_LOB.APPEND interface parameters

  Parameter	Description
  dest_lob	LOB descriptor to be written
  src_lob	LOB descriptor to be read

Examples

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52

-- Obtain the length of the character string.
SELECT DBMS_LOB.GETLENGTH('12345678');

DECLARE
myraw  RAW(100);
amount INTEGER :=2;
buffer INTEGER :=1;
begin
DBMS_LOB.READ('123456789012345',amount,buffer,myraw);
dbms_output.put_line(myraw);
end;
/

CREATE TABLE blob_Table (t1 blob) DISTRIBUTE BY REPLICATION;
CREATE TABLE blob_Table_bak (t2 blob) DISTRIBUTE BY REPLICATION;
INSERT INTO blob_Table VALUES('abcdef');
INSERT INTO blob_Table_bak VALUES('22222');

DECLARE
str varchar2(100) := 'abcdef';
source raw(100);
dest blob;
copyto blob;
amount int;
PSV_SQL varchar2(100);
PSV_SQL1 varchar2(100);
a int :=1;
len int;
BEGIN
source := utl_raw.cast_to_raw(str);
amount := utl_raw.length(source);

PSV_SQL :='select * from blob_Table for update';
PSV_SQL1 := 'select * from blob_Table_bak for update';

EXECUTE IMMEDIATE PSV_SQL into dest;
EXECUTE IMMEDIATE PSV_SQL1 into copyto;

DBMS_LOB.WRITE(dest, amount, 1, source);
DBMS_LOB.WRITEAPPEND(dest, amount, source);

DBMS_LOB.ERASE(dest, a, 1);
DBMS_OUTPUT.PUT_LINE(a);
DBMS_LOB.COPY(copyto, dest, amount, 10, 1);
DBMS_LOB.CLOSE(dest);
RETURN;
END;
/

--Delete the table.
DROP TABLE blob_Table;
DROP TABLE blob_Table_bak;