DBE_LOB

Interface Description

Table 1 provides all interfaces supported by the DBE_LOB package.

• DBE_LOB.GET_LENGTH

  The stored procedure GET_LENGTH obtains and returns the specified length of a LOB.

  The function prototype of DBE_LOB.GET_LENGTH is as follows:

  1 2 3 4 5 6 7

  DBE_LOB.GET_LENGTH ( lob IN BLOB) RETURN INTEGER; DBE_LOB.GET_LENGTH ( lob IN CLOB) RETURN INTEGER;

  Table 2 DBE_LOB.GET_LENGTH interface parameters

  Parameter	Description
  lob	BLOB/CLOB whose length is to be obtained

• DBE_LOB.OPEN

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

  The function prototype of DBE_LOB.OPEN is as follows:

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

  DBE_LOB.OPEN ( lob INOUT BLOB ); DBE_LOB.OPEN ( lob INOUT CLOB ); DBE_LOB.OPEN ( bfile dbe_lob.bfile, open_mode text DEFAULT 'null'::text );

  Table 3 DBE_LOB.OPEN interface parameters

  Parameter	Description
  lob	BLOB or CLOB that is opened

• DBE_LOB.READ

  The stored procedure READ loads a part of LOB content to the buffer based on the specified length and initial position offset.

  The function prototype of DBE_LOB.READ is as follows:

  1 2 3 4 5 6 7 8 9 10 11

  DBE_LOB.READ ( lob IN BLOB, len IN INTEGER, start IN INTEGER, buffer OUT RAW); DBE_LOB.READ ( lob IN CLOB, len INOUT INTEGER, start IN INTEGER, buffer OUT VARCHAR2);

  Table 4 DBE_LOB.READ interface parameters

  Parameter	Description
  lob	BLOB/CLOB to be read
  len	Length of read content NOTE: If the length is negative, the error message "ERROR: argument 2 is null, invalid, or out of range." is displayed.
  start	Indicates where to start reading the LOB content, that is, the offset bytes to initial position of LOB content.
  buffer	Target buffer to store the read LOB content

• DBE_LOB.WRITE

  The stored procedure WRITE copies content in the buffer to LOB variables based on the specified length and initial position.

  The prototype of the DBE_LOB.WRITE function is as follows:

  1 2 3 4 5 6 7 8 9 10 11

  DBE_LOB.WRITE ( dest_lob INOUT BLOB, len IN INTEGER, start IN INTEGER, src_lob IN RAW); DBE_LOB.WRITE ( dest_lob INOUT CLOB, len IN INTEGER, start IN INTEGER, src_lob IN VARCHAR2);

  Table 5 DBE_LOB.WRITE interface parameters

  Parameter	Description
  dest_lob	BLOB/CLOB to be written
  len	Length of written content NOTE: If the length of written content is shorter than 1 or longer than the length of content to be written, an error is reported.
  start	Indicates where to start writing the LOB content, that is, the offset bytes to initial position of LOB content. NOTE: If the offset value is less than 1, an error is reported. If the offset value is greater than the maximum length of LOB type contents, no error is reported.
  src_lob	Content to be written

• DBE_LOB.WRITE_APPEND

  The stored procedure WRITE_APPEND copies content in the buffer to the end part of a LOB based on the specified length.

  The function prototype of DBE_LOB.WRITE_APPEND is as follows:

  1 2 3 4 5 6 7 8 9

  DBE_LOB.WRITE_APPEND ( dest_lob INOUT BLOB, len IN INTEGER, src_lob IN RAW); DBE_LOB.WRITE_APPEND ( dest_lob INOUT CLOB, len IN INTEGER, src_lob IN VARCHAR2);

  Table 6 DBE_LOB.WRITE_APPEND interface parameters

  Parameter	Description
  dest_lob	BLOB/CLOB to be written
  len	Length of written content NOTE: If the length of written content is shorter than 1 or longer than the length of content to be written, an error is reported.
  src_lob	Content to be written

• DBE_LOB.COPY

  The stored procedure COPY copies content in a BLOB to another BLOB based on the specified length and initial position offset.

  The function prototype of DBE_LOB.COPY is as follows:

  1 2 3 4 5 6

  DBE_LOB.COPY ( dest_lob INOUT BLOB, src_lob IN BLOB, len IN INTEGER, dest_start IN INTEGER DEFAULT 1, src_start IN INTEGER DEFAULT 1);

  Table 7 DBE_LOB.COPY interface parameters

  Parameter	Description
  dest_lob	BLOB to be pasted
  src_lob	BLOB to be copied
  len	Length of copied content NOTE: If the length of copied content is shorter than 1 or longer than the maximum length of BLOB, an error is reported.
  dest_start	Indicates where to start pasting the BLOB content, that is, the offset bytes to initial position of BLOB content. NOTE: If the offset is shorter than 1 or longer than the maximum length of BLOB, an error is reported.
  src_start	Indicates where to start copying the BLOB content, that is, the offset bytes to initial position of BLOB content. NOTE: If the offset is shorter than 1 or longer than the length of source BLOB, an error is reported.

• DBE_LOB.ERASE

  The stored procedure ERASE deletes content in BLOB based on the specified length and initial position offset.

  The prototype of the DBE_LOB.ERASE function is as follows:

  1 2 3 4

  DBE_LOB.ERASE ( lob INOUT BLOB, len INOUT INTEGER, start IN INTEGER DEFAULT 1);

  Table 8 DBE_LOB.ERASE interface parameters

  Parameter	Description
  lob	BLOB whose content is to be deleted
  len	Length of content to be deleted NOTE: If the length of deleted content is shorter than 1 or longer than the maximum length of BLOB, an error is reported.
  start	Indicates where to start deleting the BLOB content, that is, the offset bytes to initial position of BLOB content. NOTE: If the offset is shorter than 1 or longer than the maximum length of BLOB, an error is reported.

• DBE_LOB.CLOSE

  The stored procedure CLOSE closes the LOB descriptor that has been opened.

  The prototype of the DBE_LOB.CLOSE function is as follows:

  1 2 3 4 5 6 7 8

  DBE_LOB.CLOSE( lob IN BLOB); DBE_LOB.CLOSE ( lob IN CLOB); DBE_LOB.CLOSE ( file IN INTEGER);

  Table 9 DBE_LOB.CLOSE interface parameters

  Parameter	Description
  lob	BLOB/CLOB to be disabled

• DBE_LOB.MATCH

  This function returns the Nth occurrence position of pattern in a LOB. NULL is returned for any of the following conditions: offset < 1 or offset > LOBMAXSIZE; nth < 1 or nth > LOBMAXSIZE

  The function prototype of DBE_LOB.MATCH is as follows:

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

  DBE_LOB.MATCH ( lob IN BLOB, pattern IN RAW, start_index IN INTEGER DEFAULT 1, match_index IN INTEGER DEFAULT 1) RETURN INTEGER; DBE_LOB.MATCH ( lob IN CLOB, pattern IN VARCHAR2 , start_index IN INTEGER DEFAULT 1, match_index IN INTEGER DEFAULT 1) RETURN INTEGER;

  Table 10 DBE_LOB.MATCH interface parameters

  Parameter	Description
  lob	BLOB/CLOB descriptor to be searched for
  pattern	Matched pattern. It is RAW for BLOB and TEXT for CLOB.
  start_index	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.
  match_index	Number of pattern matching times. The minimum value is 1.

• DBE_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 LOB is smaller than the second, -1 is returned. If the first LOB is larger than the second, 1 is returned.
  • If any of the len, start1, and start2 parameters is invalid, NULL is returned. The valid offset range is 1 to LOBMAXSIZE.

  The function prototype of DBE_LOB.COMPARE is as follows:

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

  DBE_LOB.COMPARE ( lob1 IN BLOB, lob2 IN BLOB, len IN INTEGER DEFAULT DBE_LOB.LOBMAXSIZE, start1 IN INTEGER DEFAULT 1, start2 IN INTEGER DEFAULT 1) RETURN INTEGER; DBE_LOB.COMPARE ( lob1 IN CLOB, lob2 IN CLOB, len IN INTEGER DEFAULT DBE_LOB.LOBMAXSIZE, start1 IN INTEGER DEFAULT 1, start2 IN INTEGER DEFAULT 1) RETURN INTEGER;

  Table 11 DBE_LOB.COMPARE interface parameters

  Parameter	Description
  lob1	First BLOB/CLOB to be compared
  lob2	Second BLOB/CLOB to be compared
  len	Number of characters or bytes to be compared. The maximum value is DBE_LOB.LOBMAXSIZE.
  start1	Offset of the first LOB descriptor. The initial position is 1.
  start2	Offset of the second LOB descriptor. The initial position is 1.

• DBE_LOB.SUBSTR

  This function reads the substring of a LOB and returns the number of read bytes or the number of characters. NULL is returned for any of the following conditions: amount > 1 or amount < 32767; offset < 1 or offset > LOBMAXSIZE

  The prototype of the DBE_LOB.SUBSTR function is as follows:

  1 2 3 4 5 6 7 8 9 10 11

  DBE_LOB.SUBSTR ( lob IN BLOB, len IN INTEGER DEFAULT 32767, start IN INTEGER DEFAULT 1) RETURN RAW; DBE_LOB.SUBSTR ( lob IN CLOB, len IN INTEGER DEFAULT 32767, start IN INTEGER DEFAULT 1) RETURN VARCHAR2;

  Table 12 DBE_LOB.SUBSTR interface parameters

  Parameter	Description
  lob	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.
  len	Number of bytes or characters to be read.
  start	Number of characters or bytes offset from the start position.

• DBE_LOB.STRIP

  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 the LOB, an exception occurs.

  The prototype of the DBE_LOB.STRIP function is as follows:

  1 2 3 4 5 6 7

  DBE_LOB.STRIP ( lob IN OUT BLOB, newlen IN INTEGER); DBE_LOB.STRIP ( lob IN OUT CLOB, newlen IN INTEGER);

  Table 13 DBE_LOB.STRIP interface parameters

  Parameter	Description
  lob	BLOB 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.

• DBE_LOB.CREATE_TEMPORARY

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

  The function prototype of DBE_LOB.CREATE_TEMPORARY is as follows:

  1 2 3 4 5 6 7 8 9

  DBE_LOB.CREATE_TEMPORARY ( locator INOUT BLOB, cache IN BOOLEAN, keep_alive_time IN INTEGER); DBE_LOB.CREATE_TEMPORARY ( locator INOUT CLOB, cache IN BOOLEAN, keep_alive_time IN INTEGER);

  Table 14 DBE_LOB.CREATE_TEMPORARY interface parameters

  Parameter	Description
  locator	LOB descriptor
  cache	Used only for syntax compatibility.
  keep_alive_time	Used only for syntax compatibility.

• DBE_LOB.APPEND

  The stored procedure APPEND loads a part of BLOB content to the buffer based on the specified length and initial position offset.

  The function prototype of DBE_LOB.APPEND is as follows:

  1 2 3 4 5 6 7

  DBE_LOB.APPEND ( dest_lob INOUT BLOB, src_lob IN BLOB); DBE_LOB.APPEND ( dest_lob INOUT CLOB, src_lob IN CLOB);

  Table 15 DBE_LOB.APPEND interface parameters

  Parameter	Description
  dest_lob	BLOB/CLOB to be written
  src_lob	BLOB/CLOB to be read

• DBE_LOB.FREETEMPORARY

  The stored procedure is used to release LOB files created by CREATE_TEMPORARY.

  The DBE_LOB.FREETEMPORARY function prototype is as follows:

  1 2 3 4 5

  DBE_LOB.FREETEMPORARY ( lob_loc INOUT BLOB); DBE_LOB.FREETEMPORARY ( lob_loc INOUT CLOB);

  Table 16 DBE_LOB.FREETEMPORARY interface parameters

  Parameter	Description
  lob_loc	BLOB or CLOB to be released.

• DBE_LOB.FILEOPEN

  This function is used to open a database-external file of the BFILE type and return the file descriptor corresponding to the file.

  The BFILE type is defined as follows:

  1 2 3

  DBE_LOB.BFILE ( directory text, filename text);

  The DBE_LOB.FILEOPEN function prototype is as follows:

  1 2 3 4

  DBE_LOB.FILEOPEN ( file IN DBE_LOB.BFILE, open_mode IN text) RETURN integer;

  Table 17 DBE_LOB.FILEOPEN parameters

  Parameter	Description
  file	Specifies the database-external file to be opened. The BFILE type contains the file path and file name.
  open_mode	Specifies the file open mode (w, r, or a).

• DBE_LOB.FILECLOSE

  This function is used to close an external BFILE file.

  The DBE_LOB.FILECLOSE function prototype is as follows:

  1 2	DBE_LOB.FILECLOSE ( file IN integer);

  Table 18 DBE_LOB.FILECLOSE interface parameters

  Parameter	Description
  file	Specifies the database-external file to be closed (the file descriptor is returned by FILEOPEN).

• DBE_LOB.LOADFROMFILE

  This is used to read an external BFILE file to a BLOB file.

  The prototype of the DBE_LOB.LOADFROMFILE function is as follows:

  1 2 3 4 5 6 7

  DBE_LOB.LOADFROMFILE ( dest_lob IN BLOB, src_file IN INTEGER, amount IN INTEGER, dest_offset IN INTEGER, src_offset IN INTEGER) RETURN raw;

  Table 19 DBE_LOB.LOADFROMFILE interface parameters

  Parameter	Description
  dest_lob	Target BLOB file. The BFILE file will be read to this file.
  src_bfile	Source BFILE file to be read.
  amount	Size of a BLOB file. If the size of a file exceeds this threshold, the file will not be saved to the BLOB file.
  dest_offset	Offset length of the BLOB file. If dest_offset is set to 1, data is loaded from the start position of the file. The rest may be deduced by analogy.
  src_offset	Offset length of the BFILE file. If src_offset is set to 1, data is read from the start position of the file. The rest may be deduced by analogy.

• DBE_LOB.LOADBLOBFROMFILE

  This is used to read an external BFILE file to a BLOB file.

  The prototype of the DBE_LOB.LOADBLOBFROMFILE function is as follows:

  1 2 3 4 5 6 7

  DBE_LOB.LOADBLOBFROMFILE ( dest_lob IN BLOB, src_file IN INTEGER, amount IN INTEGER, dest_offset IN INTEGER, src_offset IN INTEGER) RETURN raw;

  Table 20 DBE_LOB.LOADBLOBFROMFILE interface parameters

  Parameter	Description
  dest_lob	Target BLOB file. The BFILE file will be read to this file.
  src_bfile	Source BFILE file to be read.
  amount	Size of a BLOB file. If the size of a file exceeds this threshold, the file will not be saved to the BLOB file.
  dest_offset	Offset length of the BLOB file. If dest_offset is set to 1, data is loaded from the start position of the file. The rest may be deduced by analogy.
  src_offset	Offset length of the BFILE file. If src_offset is set to 1, data is read from the start position of the file. The rest may be deduced by analogy.

• DBE_LOB.LOADCLOBFROMFILE

  This is used to read an external BFILE file to a CLOB file.

  The prototype of the DBE_LOB.LOADCLOBFROMFILE function is as follows:

  1 2 3 4 5 6 7

  DBE_LOB.LOADCLOBFROMFILE ( dest_lob IN CLOB, src_file IN INTEGER, amount IN INTEGER, dest_offset IN INTEGER, src_offset IN INTEGER) RETURN raw;

  Table 21 DBE_LOB.LOADCLOBFROMFILE interface parameters

  Parameter	Description
  dest_lob	Target CLOB file. The BFILE file will be read to this file.
  src_bfile	Source BFILE file to be read.
  amount	Size of a CLOB file. If the size of a file exceeds this threshold, the file will not be saved to the CLOB file.
  dest_offset	Offset length of the CLOB file. If dest_offset is set to 1, data is loaded from the start position of the file. The rest may be deduced by analogy.
  src_offset	Offset length of the BFILE file. If src_offset is set to 1, data is read from the start position of the file. The rest may be deduced by analogy.

• DBE_LOB.CONVERTTOBLOB

  This function is used to convert a CLOB file to a BLOB file.

  The prototype of the DBE_LOB.CONVERTTOBLOB function is as follows:

  1 2 3 4 5 6 7

  DBE_LOB.CONVERTTOBLOB( dest_blob IN BLOB, src_clob IN CLOB, amount IN INTEGER default 32767, dest_offset IN INTEGER default 1, src_offset IN INTEGER default 1) RETURN raw;

  Table 22 DBE_LOB.CONVERTTOBLOB interface parameters

  Parameter	Description
  dest_lob	Target BLOB file, which is converted from a CLOB file.
  src_bfile	Source CLOB file to be read.
  amount	Size of a BLOB file. If the size of a file exceeds this threshold, the file will not be saved to the BLOB file.
  dest_offset	Offset length of the BLOB file. If dest_offset is set to 1, data is loaded from the start position of the file. The rest may be deduced by analogy.
  src_offset	Offset length of the CLOB file. If src_offset is set to 1, data is read from the start position of the file. The rest may be deduced by analogy.

• DBE_LOB.CONVERTTOCLOB

  This function is used to convert a BLOB file to a CLOB file.

  The prototype of the DBE_LOB.CONVERTTOCLOB function is as follows:

  1 2 3 4 5 6 7

  DBE_LOB.CONVERTTOCLOB( dest_clob IN CLOB, src_blob IN BLOB, amount IN INTEGER default 32767, dest_offset IN INTEGER default 1, src_offset IN INTEGER default 1) RETURN text;

  Table 23 DBE_LOB.CONVERTTOCLOB interface parameters

  Parameter	Description
  dest_lob	Target CLOB file, which is converted from a BLOB file.
  src_bfile	Source BLOB file to be read.
  amount	Size of a CLOB file. If the size of a file exceeds this threshold, the file will not be saved to the CLOB file.
  dest_offset	Offset length of the CLOB file. If dest_offset is set to 1, data is loaded from the start position of the file. The rest may be deduced by analogy.
  src_offset	Offset length of the BLOB file. If src_offset is set to 1, data is read from the start position of the file. The rest may be deduced by analogy.

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 a string.
SELECT DBE_LOB.GET_LENGTH('12345678');

DECLARE
myraw  RAW(100);
amount INTEGER :=2;
buffer INTEGER :=1;
begin
DBE_LOB.READ('123456789012345',amount,buffer,myraw);
dbe_output.print_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 := dbe_raw.cast_from_varchar2_to_raw(str);
amount := dbe_raw.get_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;

DBE_LOB.WRITE(dest, amount, 1, source);
DBE_LOB.WRITE_APPEND(dest, amount, source);

DBE_LOB.ERASE(dest, a, 1);
DBE_OUTPUT.PRINT_LINE(a);
DBE_LOB.COPY(copyto, dest, amount, 10, 1);
perform DBE_LOB.CLOSE(dest);
RETURN;
END;
/

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