DBE_LOB

API Description

Table 1 lists all APIs supported by the DBE_LOB package.

• In database A, the byte content of the space is 00. However, in GaussDB, the byte content corresponding to the space is ASCII code 32.
• In a centralized environment, the maximum size of a CLOB, BLOB, and BFILE is 32 TB.
• LOBMAXSIZE supports a maximum of 1073741771 bytes.

• DBE_LOB.GET_LENGTH

  Obtains and returns the size of a specified LOB. The maximum size of the object is 2 GB.

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

  1 2 3 4 5 6 7

  DBE_LOB.GET_LENGTH ( blob_obj IN BLOB) RETURN INTEGER; DBE_LOB.GET_LENGTH ( clob_obj IN CLOB) RETURN INTEGER;

  Table 2 DBE_LOB.GET_LENGTH parameters

  Parameter	Description
  blob_obj/clob_obj	BLOB/CLOB whose length is to be obtained

• DBE_LOB.LOB_GET_LENGTH

  Obtains and returns the length of a specified LOB or BFILE file. The maximum size of the object is 32 TB.

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

  1 2 3 4 5 6 7 8 9 10 11

  DBE_LOB.LOB_GET_LENGTH ( blob_obj IN BLOB) RETURN BIGINT; DBE_LOB.LOB_GET_LENGTH ( clob_obj IN CLOB) RETURN BIGINT; DBE_LOB.LOB_GET_LENGTH ( bfile IN DBE_LOB.BFILE) RETURN BIGINT;

  Table 3 DBE_LOB.LOB_GET_LENGTH parameters

  Parameter	Description
  blob_obj/clob_obj/bfile	BLOB/CLOB/BFILE whose length is to be obtained

• DBE_LOB.OPEN

  Opens a LOB and returns a LOB descriptor. This procedure is meaningless and is used only for compatibility.

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

  1 2 3 4 5 6 7 8 9

  DBE_LOB.OPEN ( lob INOUT BLOB); DBE_LOB.OPEN ( lob INOUT CLOB); DBE_LOB.OPEN ( bfile INOUT DBE_LOB.BFILE, open_mode IN TEXT DEFAULT 'null');

  Table 4 DBE_LOB.OPEN parameters

  Parameter	Description
  lob/bfile	Opened BLOB, CLOB or BFILE.
  open_mode	Operation mode. Currently, the range is [R,W,A,RB,WB,AB].

• DBE_LOB.READ

  Reads a part of LOB content to the output buffer based on the specified length and initial position offset.

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

  1 2 3 4 5 6 7 8 9 10 11

  DBE_LOB.READ ( blob_obj IN BLOB, amount IN INTEGER, off_set IN INTEGER, out_put OUT RAW); DBE_LOB.READ ( clob_obj IN CLOB, amount IN INTEGER, off_set IN INTEGER, out_put OUT VARCHAR2);

  Table 5 DBE_LOB.READ parameters

  Parameter	Description
  blob_obj/clob_obj	BLOB/CLOB to be read
  amount	Length of read content NOTE: If the length to read is less than 1 or greater than 32767, an error is reported.
  off_set	Start position for reading the LOB content, that is, the offset bytes to initial position of the LOB content. If the offset is less than 1 or greater than the LOB length, an error is reported. The initial position is 1.
  out_put	Target buffer for storing the read LOB content

• DBE_LOB.LOB_READ

  Reads a part of LOB/BFILE content to the output buffer based on the specified length and initial position offset.

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

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

  DBE_LOB.LOB_READ( blob_obj IN BLOB, amount INOUT BIGINT, off_set IN BIGINT, out_put OUT RAW); DBE_LOB.LOB_READ( clob_obj IN CLOB, amount INOUT BIGINT, off_set IN BIGINT, out_put OUT VARCHAR2); DBE_LOB.LOB_READ( bfile IN DBE_LOB.BFILE, amount INOUT BIGINT, off_set IN BIGINT, out_put OUT RAW);

  Table 6 DBE_LOB.LOB_READ parameters

  Parameter	Description
  blob_obj/clob_obj/bfile	BLOB/CLOB/BFILE object (can be greater than 1 GB) to be read
  amount	Length to read as the IN parameter, or actual read length as the OUT parameter. NOTE: If the length to read is less than 1 or greater than 32767, an error is reported.
  off_set	Start position for reading the LOB content, that is, the offset bytes to initial position of the LOB content. If the offset is less than 1 or greater than the LOB length, an error is reported. The initial position is 1.
  out_put	Target buffer for storing the read LOB content

• DBE_LOB.WRITE

  Writes content in the source to a LOB 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 ( blob_obj INOUT BLOB, amount IN INTEGER, off_set IN INTEGER, source IN RAW); DBE_LOB.WRITE ( clob_obj INOUT CLOB, amount IN INTEGER, off_set IN INTEGER, source IN VARCHAR2);

  Table 7 DBE_LOB.WRITE parameters

  Parameter	Description
  blob_obj/clob_obj	BLOB/CLOB to which the buffer content is written
  amount	Length to write, up to 32767 characters. NOTE: If the length to write is less than 1 or greater than the length of the content to be written, an error is reported.
  off_set	Start position for writing content to the target LOB, that is, the offset bytes to the initial position of LOB content. NOTE: If the offset is less than 1 or greater than the value of LOBMAXSIZE, an error is reported. The initial position is 1, and the maximum value is the maximum length of the LOB type.
  source	Content to be written

• DBE_LOB.WRITE_APPEND

  Writes content in the source object to the end part of a LOB based on the specified length.

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

  1 2 3 4 5 6 7 8 9

  DBE_LOB.WRITE_APPEND ( blob_obj INOUT BLOB, amount IN INTEGER, source_obj IN RAW); DBE_LOB.WRITE_APPEND ( clob_obj INOUT CLOB, amount IN INTEGER, source_obj IN VARCHAR2);

  Table 8 DBE_LOB.WRITE_APPEND parameters

  Parameter	Description
  blob_obj/clob_obj	BLOB/CLOB to which the buffer content is written
  amount	Length to write, up to 32767 characters. NOTE: If the length to write is less than 1 or greater than the length of the content to be written, an error is reported.
  source_obj	Content to be written

• DBE_LOB.LOB_WRITE_APPEND

  Writes content in the source object to the end part of a LOB based on the specified length.

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

  1 2 3 4 5 6 7 8 9

  DBE_LOB.LOB_WRITE_APPEND( blob_obj INOUT BLOB, amount IN INTEGER, source_obj IN RAW); DBE_LOB.LOB_WRITE_APPEND ( clob_obj INOUT CLOB, amount IN INTEGER, source_obj IN VARCHAR2);

  Table 9 DBE_LOB.LOB_WRITE_APPEND parameters

  Parameter	Description
  blob_obj/clob_obj	BLOB/CLOB to which the buffer content is written
  amount	Length to write, up to 32767 characters. NOTE: If the length to write is less than 1 or greater than the length of the content to be written, an error is reported.
  source_obj	Content to be written

• DBE_LOB.COPY

  Copies content in a LOB to another LOB based on the specified length and initial position offset.

  The prototype of the DBE_LOB.COPY function 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 10 DBE_LOB.COPY parameters

  Parameter	Description
  dest_lob	LOB to which the buffer content is to be pasted.
  src_lob	LOB from which the buffer content is to be copied.
  len	Length of the copied data.
  dest_start	Start position for pasting the buffer content to the target LOB (dest_lob), that is, the offset bytes to the initial position of LOB content.
  src_start	Start position for copying the buffer content from the source LOB (src_lob), that is, the offset bytes to the initial position of LOB content.

• DBE_LOB.LOB_COPY

  Copies content in a LOB to another LOB based on the specified length and initial position offset.

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

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

  DBE_LOB.LOB_COPY( blob_obj INOUT BLOB, source_obj IN BLOB, amount IN BIGINT, dest_offset IN BIGINT DEFAULT 1, src_offset IN BIGINT DEFAULT 1); DBE_LOB.LOB_COPY( clob_obj INOUT CLOB, source_obj IN CLOB, amount IN BIGINT, dest_offset IN BIGINT DEFAULT 1, src_offset IN BIGINT DEFAULT 1);

  Table 11 DBE_LOB.LOB_COPY parameters

  Parameter	Description
  blob_obj/clob_obj	LOB to which the buffer content is to be pasted.
  source_obj	LOB from which the buffer content is to be copied.
  amount	Length of the copied data. NOTE: If the length to copy is less than 1 or greater than the value of LOBMAXSIZE, an error is reported.
  dest_offset	Start position for pasting the buffer content to the target LOB, that is, the offset bytes/characters to the initial position of LOB content. The unit is byte for BLOB and character for CLOB. NOTE: If the offset is less than 1 or greater than the value of LOBMAXSIZE, an error is reported.
  src_offset	Start position for copying the content from the source object, that is, the offset bytes to the initial position of LOB content. The unit is byte for BLOB and character for CLOB. NOTE: If the offset is less than 1, an error is reported.

• DBE_LOB.ERASE

  Deletes the content in a BLOB (not greater than 1 GB) based on the specified length and initial position offset. The bytes of the deleted part in the BLOB are filled with 0.

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

  1 2 3 4

  DBE_LOB.ERASE ( blob_obj INOUT BLOB, amount INOUT INTEGER, off_set IN INTEGER DEFAULT 1);

  Table 12 DBE_LOB.ERASE parameters

  Parameter	Description
  blob_obj	LOB whose content is to be deleted as the IN parameter, or LOB with specified content deleted as the OUT parameter. If this parameter is left empty, an error is reported.
  amount	Length (in bytes for BLOBs) to delete as the IN parameter, or actual length deleted as the OUT parameter. NOTE: If the length to delete is less than 1 or this parameter is left empty, an error is reported.
  off_set	Start position from which LOB content is to be deleted, that is, the number of offset bytes to the initial position of BLOB content. The value cannot be greater than 1 GB. NOTE: If the offset is less than 1 or this parameter is left empty, an error is reported.

• DBE_LOB.LOB_ERASE

  Deletes the content in the LOB based on the specified length and initial position offset. The bytes of the deleted part in the BLOB are filled with 0, and the characters of the deleted part in the CLOB are filled with spaces. The LOB can be greater than 1 GB and the maximum size is 32 TB.

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

  1 2 3 4 5 6 7 8 9

  DBE_LOB.LOB_ERASE ( blob_obj INOUT BLOB, amount INOUT BIGINT, off_set IN BIGINT DEFAULT 1); DBE_LOB.LOB_ERASE ( clob_obj INOUT CLOB, amount INOUT BIGINT, off_set IN BIGINT DEFAULT 1);

  Table 13 DBE_LOB.LOB_ERASE parameters

  Parameter	Description
  blob_obj/clob_obj	LOB whose content is to be deleted as the IN parameter, or LOB with specified content deleted as the OUT parameter. If this parameter is left empty, an error is reported.
  amount	Length (BLOB in bytes and CLOB in characters) to delete as the IN parameter, or actual length deleted as the OUT parameter. NOTE: If the length to delete is less than 1 or this parameter is left empty, an error is reported.
  off_set	Start position from which the LOB content is to be deleted, that is, the number of bytes relative to the start position of the BLOB content or the number of characters relative to the start position of the CLOB content. NOTE: If the offset is less than 1 or this parameter is left empty, an error is reported.

• DBE_LOB.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 14 DBE_LOB.CLOSE parameters

  Parameter	Description
  lob/file	BLOB/CLOB/File object whose LOB descriptor is to be closed

• DBE_LOB.MATCH

  Returns the Nth occurrence position of a string in a LOB or BFILE file. NULL is returned for an invalid input. The LOB or BFILE file can be greater than 1 GB, up to 32 TB.

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

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

  DBE_LOB.MATCH( blob_obj IN BLOB, blob_obj2 IN RAW, beg_index IN BIGINT DEFAULT 1, occur_index IN BIGINT DEFAULT 1) RETURN BIGINT; DBE_LOB.MATCH( clob_obj IN CLOB, clob_obj2 IN VARCHAR2, beg_index IN BIGINT DEFAULT 1, occur_index IN BIGINT DEFAULT 1) RETURN BIGINT; DBE_LOB.MATCH( bfile IN DBE_LOB.BFILE, blob_obj2 IN RAW, beg_index IN BIGINT DEFAULT 1, occur_index IN BIGINT DEFAULT 1) RETURN BIGINT;

  Table 15 DBE_LOB.MATCH parameters

  Parameter	Description
  blob_obj/clob_obj/bfile	BLOB/CLOB descriptor to be searched for, or BFILE (which must be opened using DBE_LOB.BFILEOPEN first). If this parameter is left blank, null is returned.
  blob_obj2/clob_obj2	Pattern to match. It is RAW for BLOB/BFILE objects and VARCHAR2 for CLOBs. If this parameter is left empty, null is returned.
  beg_index	Absolute offset (in bytes) for BLOB/BFILE objects, or offset (in characters) for CLOBs. The start position for matching is 1. NOTE: The value ranges from 1 to LOBMAXSIZE. If a value out of the range is input, null is returned.
  occur_index	Number of pattern matching times. The minimum value is 1. NOTE: If the value is greater than the maximum number of times that the pattern string can be matched in the LOB, 0 is returned. If the value is out of the range from 1 to LOBMAXSIZE, null is returned.

• DBE_LOB.COMPARE

  Compares LOBs or BFILE objects.

  • If the compared objects 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.
  • If both the values of start_pos1 and start_pos2 exceed the LOB/BFILE length, 0 is returned.
  The prototype of the DBE_LOB.COMPARE function 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 BIGINT DEFAULT 1073741312, start_pos1 IN BIGINT DEFAULT 1, start_pos2 IN BIGINT DEFAULT 1) RETURN INTEGER; DBE_LOB.COMPARE ( lob1 IN CLOB, lob2 IN CLOB, len IN BIGINT DEFAULT 1073741312, start_pos1 IN BIGINT DEFAULT 1, start_pos2 IN BIGINT DEFAULT 1) RETURN INTEGER;

  1 2 3 4 5 6 7

  DBE_LOB.COMPARE ( file1 IN DBE_LOB.BFILE, file2 IN DBE_LOB.BFILE, len IN BIGINT DEFAULT 1073741312, start_pos1 IN BIGINT DEFAULT 1, start_pos2 IN BIGINT DEFAULT 1) RETURN INTEGER;

  Table 16 DBE_LOB.COMPARE parameters

  Parameter	Description
  lob1/file1	First BLOB/CLOB/BFILE to be compared (which must be opened using DBE_LOB.BFILEOPEN first).
  lob2/file2	Second BLOB/CLOB/BFILE to be compared (which must be opened using DBE_LOB.BFILEOPEN first).
  len	Number of characters or bytes to be compared. The default value is 1073741312.
  start_pos1	Offset of the first LOB descriptor. The initial position is 1, and the maximum value is the maximum length of the LOB content.
  start_pos2	Offset of the second LOB descriptor. The initial position is 1, and the maximum value is the maximum length of the LOB content.

• DBE_LOB.SUBSTR

  Reads a LOB substring and returns the read substring.

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

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

  DBE_LOB.SUBSTR( lob_loc IN BLOB, amount IN INTEGER DEFAULT 32767, off_set IN INTEGER DEFAULT 1) RETURN RAW; DBE_LOB.SUBSTR( lob_loc IN CLOB, amount IN INTEGER DEFAULT 32767, off_set IN INTEGER DEFAULT 1) RETURN VARCHAR2;

  Table 17 DBE_LOB.SUBSTR parameters

  Parameter	Description
  lob_loc	LOB descriptor whose substring is to be read. For BLOBs, the return value is of the RAW type. For CLOBs, the return value is of the VARCHAR2 type.
  amount	Number of bytes or characters to be read. NOTE: The value ranges from 1 to 32767. If the value exceeds the range, null is returned.
  off_set	Number of characters or bytes offset from the start position. NOTE: The value ranges from 1 to LOBMAXSIZE. If the value exceeds the range, null is returned.

• DBE_LOB.LOB_SUBSTR

  Reads a LOB or BFILE substring and returns the read substring. The LOB or BFILE file can be greater than 1 GB, up to 32 TB.

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

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

  DBE_LOB.LOB_SUBSTR( lob_loc IN BLOB, amount IN INTEGER DEFAULT 32767, off_set IN BIGINT DEFAULT 1) RETURN RAW; DBE_LOB.LOB_SUBSTR( lob_loc IN CLOB, amount IN INTEGER DEFAULT 32767, off_set IN BIGINT DEFAULT 1) RETURN VARCHAR2; DBE_LOB.LOB_SUBSTR( bfile IN DBE_LOB.BFILE, amount IN INTEGER DEFAULT 32767, off_set IN BIGINT DEFAULT 1) RETURN RAW;

  Table 18 DBE_LOB.LOB_SUBSTR parameters

  Parameter	Description
  lob_loc/bfile	LOB descriptor or BFILE file whose substring is to be read. The file must be opened by DBE__LOB.BFILEOPEN first. For BLOBs/BFILE files, the return value is of the RAW type. For CLOBs, the return value is of the VARCHAR2 type.
  amount	Number of bytes or characters to be read. NOTE: The value ranges from 1 to 32767. If the value exceeds the range, null is returned.
  off_set	Number of characters or bytes offset from the start position. NOTE: The value ranges from 1 to LOBMAXSIZE. If the value exceeds the range, null is returned.

• DBE_LOB.STRIP

  Truncates a LOB based on a specified length. After this stored procedure is executed, the length of the LOB is set to the length specified by the newlen parameter.

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

  1 2 3 4 5 6 7

  DBE_LOB.STRIP( lob_loc INOUT BLOB, newlen IN INTEGER); DBE_LOB.STRIP( lob_loc INOUT CLOB, newlen IN INTEGER);

  Table 19 DBE_LOB.STRIP parameters

  Parameter	Description
  lob_loc	LOB to read as the IN parameter, or truncated object as the OUT parameter. If this parameter is left empty, an error is reported.
  newlen	New length after truncation, in bytes for BLOBs or in characters for CLOBs.

• DBE_LOB.LOB_STRIP

  Truncates a LOB based on a specified length. After this stored procedure is executed, the length of the LOB is set to the length specified by the newlen parameter. The LOB can be greater than 1 GB, up to 32 TB.

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

  1 2 3 4 5 6 7

  DBE_LOB.LOB_STRIP( lob_loc INOUT BLOB, newlen IN BIGINT); DBE_LOB.LOB_STRIP( lob_loc INOUT CLOB, newlen IN BIGINT);

  Table 20 DBE_LOB.LOB_STRIP parameters

  Parameter	Description
  lob_loc	LOB to read as the IN parameter, or truncated object as the OUT parameter. If this parameter is left empty, an error is reported.
  newlen	New length after truncation, in bytes for BLOBs or in characters for CLOBs. NOTE: If the value is less than 1, null is returned. If the value is greater than the LOB length, an error is reported.

• DBE_LOB.CREATE_TEMPORARY

  Creates a temporary BLOB or CLOB. This API is used only for syntax compatibility.

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

  1 2 3 4 5 6 7 8 9

  DBE_LOB.CREATE_TEMPORARY ( lob_loc INOUT BLOB, cache IN BOOLEAN, dur IN INTEGER DEFAULT 10); DBE_LOB.CREATE_TEMPORARY ( lob_loc INOUT CLOB, cache IN BOOLEAN, dur IN INTEGER DEFAULT 10);

  Table 21 DBE_LOB.CREATE_TEMPORARY parameters

  Parameter	Description
  lob_loc	LOB descriptor
  cache	Used only for syntax compatibility.
  dur	Used only for syntax compatibility.

• DBE_LOB.APPEND

  Appends source_obj to the end of the target LOB.

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

  1 2 3 4 5 6 7

  DBE_LOB.APPEND ( blob_obj INOUT BLOB, source_obj IN BLOB); DBE_LOB.APPEND ( clob_obj INOUT CLOB, source_obj IN CLOB);

  Table 22 DBE_LOB.APPEND parameters

  Parameter	Description
  blob_obj/clob_obj	BLOB/CLOB to which the buffer content is to be written
  source_obj	BLOB/CLOB from which the buffer content is to be read

• DBE_LOB.LOB_APPEND

  Appends source_obj to the end of the target LOB.

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

  1 2 3 4 5 6 7

  DBE_LOB.LOB_APPEND( blob_obj INOUT BLOB, source_obj IN BLOB); DBE_LOB.LOB_APPEND( clob_obj INOUT CLOB, source_obj IN CLOB);

  Table 23 DBE_LOB.LOB_APPEND parameters

  Parameter	Description
  blob_obj/clob_obj	BLOB/CLOB to which the buffer content is to be written
  source_obj	BLOB/CLOB from which the buffer content is to be read

• DBE_LOB.FREETEMPORARY

  Frees LOB files created by CREATE_TEMPORARY.

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

  1 2 3 4 5

  DBE_LOB.FREETEMPORARY ( blob INOUT BLOB); DBE_LOB.FREETEMPORARY ( clob INOUT CLOB);

  Table 24 DBE_LOB.FREETEMPORARY parameters

  Parameter	Description
  blob/clob	BLOB/CLOB to be freed.

• DBE_LOB.FILEOPEN

  Opens an external database BFILE file and returns its file descriptor. A maximum of 10 BFILE files can be opened in a session. The BFILE type is defined as follows:

  1 2 3 4

  DBE_LOB.BFILE ( directory TEXT, filename TEXT, fd INTEGER);

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

  1 2 3 4

  DBE_LOB.FILEOPEN ( bfile IN DBE_LOB.BFILE, open_mode IN TEXT) RETURN INTEGER;

  Table 25 DBE_LOB.FILEOPEN parameters

  Parameter	Description
  bfile	External database file to be opened. For a BFILE file, this parameter specifies the file path , file name, and file descriptor. NOTE: The file variable contains the location of the file directory directory and the file name filename. Location of the file directory, which needs to be added to system catalog 20.2.57-PG_DIRECTORY. If the input path does not match the path in 20.2.57-PG_DIRECTORY, an error indicating that the path does not exist is reported. When the GUC parameter safe_data_path is enabled, you can only use an advanced package to read and write files in the file path specified by safe_data_path. File name with an extension (file type), excluding the path name. The path contained in the file name will be ignored by OPEN. In Unix, the file name cannot end with the combination of a slash and a period (/.).
  open_mode	File open mode, which can only be r (that is, read). An error is reported in other modes.

• DBE_LOB.FILECLOSE

  Closes an external BFILE file.

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

  1 2	DBE_LOB.FILECLOSE ( file IN INTEGER);

  Table 26 DBE_LOB.FILECLOSE parameters

  Parameter	Description
  file	External database file to be closed (that is opened by FILEOPEN).

• DBE_LOB.BFILEOPEN

  Opens an external database BFILE file. A maximum of 10 BFILE files can be opened in a session.

  The prototype of DBE_LOB.BFILEOPEN is as follows:

  1 2 3

  DBE_LOB.BFILEOPEN ( bfile INOUT DBE_LOB.BFILE, open_mode IN TEXT DEFAULT 'R');

  Table 27 DBE_LOB.BFILEOPEN parameters

  Parameter	Description
  bfile	Opened database BFILE file as the INOUT parameter. NOTE: The bfile variable contains the location of the file directory directory and the file name filename. Location of the file directory, which needs to be added to system catalog 20.2.57-PG_DIRECTORY. If the input path does not match the path in 20.2.57-PG_DIRECTORY, an error indicating that the path does not exist is reported. When the GUC parameter safe_data_path is enabled, you can only use an advanced package to read and write files in the file path specified by safe_data_path. File name with an extension (file type), excluding the path name. The path contained in the file name will be ignored by OPEN. In Unix, the file name cannot end with the combination of a slash and a period (/.).
  open_mode	File open mode, which can only be r (that is, read). An error is reported in other modes.

  Examples

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

  -- Obtain the substring of the BFILE (the file content is ABCD). DECLARE bfile dbe_lob.bfile; BEGIN bfile = DBE_LOB.BFILENAME(dir_name, file_name); -- Obtain the corresponding BFILE. DBE_LOB.bfileopen(bfile, 'r'); -- Open the BFILE. RAISE NOTICE 'res:%', DBE_LOB.lob_substr(bfile, 1, 1); -- Obtain and print the substring. DBE_LOB.bfileclose(bfile);-- Close the BFILE. END; / NOTICE: res:41 ANONYMOUS BLOCK EXECUTE

• DBE_LOB.BFILECLOSE

  Closes an external database BFILE file.

  The prototype of DBE_LOB.BFILECLOSE is as follows:

  1 2	DBE_LOB.BFILECLOSE ( bfile INOUT DBE_LOB.BFILE);

  Table 28 DBE_LOB.BFILECLOSE parameters

  Parameter	Description
  bfile	Closed database BFILE file as the INOUT parameter.

• DBE_LOB.LOADFROMFILE

  Loads an external BFILE file to a BLOB and returns the object of the RAW type.

  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 29 DBE_LOB.LOADFROMFILE parameters

  Parameter	Description
  dest_lob	Target BLOB. The BFILE file will be loaded to the specified offset position of the BLOB.
  src_bfile	Source BFILE file to be read.
  amount	Length of the content to be read from the BFILE file. NOTE: If the length is less than 1 or greater than 32767, an error is reported.
  dest_offset	Offset length of the BLOB. NOTE: If the offset is less than 1 or greater than the value of LOBMAXSIZE, an error is reported.
  src_offset	Offset length of the BFILE file. NOTE: If the offset is less than 1 or greater than the value of LOBMAXSIZE, an error is reported. If the sum of amount and src_offset is greater than the length of src_bfile plus 1, an error is reported.

• DBE_LOB.LOADFROMBFILE

  Loads an external BFILE file to a LOB.

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

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

  DBE_LOB.LOADFROMBFILE ( dest_lob INOUT BLOB, src_file IN DBE_LOB.BFILE, amount IN BIGINT, dest_offset IN BIGINT DEFAULT 1, src_offset IN BIGINT DEFAULT 1) RETURN BLOB; DBE_LOB.LOADFROMBFILE ( dest_lob INOUT CLOB, src_file IN DBE_LOB.BFILE, amount IN BIGINT, dest_offset IN BIGINT DEFAULT 1, src_offset IN BIGINT DEFAULT 1) RETURN CLOB;

  Table 30 DBE_LOB.LOADFROMBFILE parameters

  Parameter	Description
  dest_lob	Target LOB as the INOUT parameter, to which the BFILE file will be loaded. The file must be opened by DBE_LOB.BFILEOPEN first. The LOB can be greater than 1 GB, up to 32 TB.
  src_file	Source BFILE file to be read. The BFILE file can be greater than 1 GB, up to 32 TB.
  amount	Length of the content to be read from the BFILE file and to be written to the LOB. NOTE: If the length is less than 1 or greater than the value of LOBMAXSIZE, an error is reported.
  dest_offset	Offset length of the target LOB NOTE: If the offset is less than 1 or greater than the value of LOBMAXSIZE, an error is reported.
  src_offset	Offset length of the BFILE file. NOTE: If the offset is less than 1 or greater than the value of LOBMAXSIZE, an error is reported. If the sum of amount and src_offset is greater than the length of src_bfile plus 1, an error is reported.

• DBE_LOB.LOADBLOBFROMFILE

  Loads an external BFILE file to a BLOB and returns the object of the RAW type.

  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 31 DBE_LOB.LOADBLOBFROMFILE parameters

  Parameter	Description
  dest_lob	Target BLOB, to which the BFILE file will be loaded.
  src_file	Source BFILE file to be read.
  amount	Length of the target BLOB. If the length of a file exceeds this threshold, the file will not be saved to the BLOB. NOTE: If the length is less than 1 or greater than 32767, an error is reported.
  dest_offset	Offset length of the BLOB. NOTE: If the offset is less than 1 or greater than the value of LOBMAXSIZE, an error is reported.
  src_offset	Offset length of the BFILE file. NOTE: If the offset is less than 1 or greater than the value of LOBMAXSIZE, an error is reported. If the sum of amount and src_offset is greater than the length of src_bfile plus 1, an error is reported.

• DBE_LOB.LOADBLOBFROMBFILE

  Loads an external BFILE file to a BLOB.

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

  1 2 3 4 5 6

  DBE_LOB.LOADBLOBFROMFILE ( dest_lob INOUT BLOB, src_file IN DEB_LOB.BFILE, amount IN BIGINT, dest_offset INOUT BIGINT, src_offset INOUT BIGINT)

  Table 32 DBE_LOB.LOADBLOBFROMBFILE parameters

  Parameter	Description
  dest_lob	Target BLOB as the INOUT parameter, to which the BFILE file (which must be opened using DBE_LOB.BFILEOPEN first) is loaded. The BFILE file can be greater than 1 GB, up to 32 TB.
  src_file	Source BFILE file to be read. The BFILE file can be greater than 1 GB, up to 32 TB.
  amount	Length of the target BLOB. If the length of a file exceeds this threshold, the file will not be saved to the BLOB. NOTE: If the length is less than 1 or greater than the value of LOBMAXSIZE, an error is reported.
  dest_offset	Offset length of the BLOB. NOTE: If the offset is less than 1 or greater than the value of LOBMAXSIZE, an error is reported.
  src_offset	Offset length of the BFILE file. NOTE: If the offset is less than 1 or greater than the value of LOBMAXSIZE, an error is reported. If the sum of amount and src_offset is greater than the length of src_bfile plus 1, an error is reported.

• DBE_LOB.LOADCLOBFROMFILE

  Loads an external BFILE file to a CLOB and returns the object of the RAW type.

  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 33 DBE_LOB.LOADCLOBFROMFILE parameters

  Parameter	Description
  dest_lob	Target CLOB, to which the BFILE file will be loaded.
  src_file	Source BFILE file to be read.
  amount	Length of the CLOB. NOTE: If the length is less than 1 or greater than 32767, an error is reported.
  dest_offset	Offset length of the CLOB. NOTE: If the offset is less than 1 or greater than the value of LOBMAXSIZE, an error is reported.
  src_offset	Offset length of the BFILE file. NOTE: If the offset is less than 1 or greater than the value of LOBMAXSIZE, an error is reported. If the sum of amount and src_offset is greater than the length of src_bfile plus 1, an error is reported.

• DBE_LOB.LOADCLOBFROMBFILE

  Loads an external BFILE file to a CLOB.

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

  1 2 3 4 5 6

  DBE_LOB.LOADCLOBFROMBFILE ( dest_lob INOUT CLOB, src_file IN DEB_LOB.BFILE, amount IN BIGINT, dest_offset INOUT BIGINT, src_offset INOUT BIGINT)

  Table 34 DBE_LOB.LOADCLOBFROMBFILE parameters

  Parameter	Description
  dest_lob	Target CLOB as the INOUT parameter, to which the BFILE file (which must be opened using DBE_LOB.BFILEOPEN first) is loaded. The BFILE file can be greater than 1 GB, up to 32 TB.
  src_file	Source BFILE file to be read. The BFILE file can be greater than 1 GB, up to 32 TB.
  amount	Length of the target CLOB. If the length of a file exceeds this threshold, the file will not be saved to the CLOB. NOTE: If the length is less than 1 or greater than the value of LOBMAXSIZE, an error is reported.
  dest_offset	Offset length of the CLOB. NOTE: If the offset is less than 1 or greater than the value of LOBMAXSIZE, an error is reported.
  src_offset	Offset length of the BFILE file. NOTE: If the offset is less than 1 or greater than the value of LOBMAXSIZE, an error is reported. If the sum of amount and src_offset is greater than the length of src_bfile plus 1, an error is reported.

• DBE_LOB.CONVERTTOBLOB

  Converts a CLOB to a BLOB. The CLOB cannot be greater than 1 GB.

  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 35 DBE_LOB.CONVERTTOBLOB parameters

  Parameter	Description
  dest_blob	Target BLOB, which is converted from a CLOB.
  src_clob	Source CLOB to be read.
  amount	Length of the target CLOB. If the length of a file exceeds this threshold, the file will not be saved to the BLOB. If the length is less than 1 or greater than the value of LOBMAXSIZE, an error is reported.
  dest_offset	Offset length of the BLOB. 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. 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.LOB_CONVERTTOBLOB

  Converts a CLOB to a BLOB. The LOB can be greater than 1 GB.

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

  1 2 3 4 5 6

  DBE_LOB.LOB_CONVERTTOBLOB( dest_blob INOUT BLOB, src_clob IN CLOB, amount IN BIGINT, dest_offset INOUT BIGINT, src_offset INOUT BIGINT)

  Table 36 DBE_LOB.LOB_CONVERTTOBLOB parameters

  Parameter	Description
  dest_blob	Target BLOB, which is converted from a CLOB.
  src_clob	Source CLOB to be read.
  amount	Length of the target CLOB. If the length of a file exceeds this threshold, the file will not be saved to the BLOB. If the length is less than 1 or greater than the value of LOBMAXSIZE, an error is reported.
  dest_offset	Offset length of the BLOB. If dest_offset is set to 1, data is loaded from the start position of the file. The rest may be deduced by analogy. If the offset is less than 1 or greater than the value of LOBMAXSIZE, an error is reported.
  src_offset	Offset length of the CLOB. If src_offset is set to 1, data is read from the start position of the file. The rest may be deduced by analogy. If the offset is less than 1 or greater than the value of LOBMAXSIZE, an error is reported.

• DBE_LOB.CONVERTTOCLOB

  Converts a BLOB to a CLOB. The BLOB cannot be greater than 1 GB.

  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 37 DBE_LOB.CONVERTTOCLOB parameters

  Parameter	Description
  dest_clob	Target CLOB, which is converted from a BLOB.
  src_blob	Source BLOB to be read.
  amount	Length of the target BLOB. If the length of a file exceeds this threshold, the file will not be saved to the CLOB.
  dest_offset	Offset length of the CLOB. 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. 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.LOB_CONVERTTOCLOB

  Converts a BLOB to a CLOB. The LOB can be greater than 1 GB.

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

  1 2 3 4 5 6

  DBE_LOB.LOB_CONVERTTOCLOB( dest_clob INOUT CLOB, src_blob IN BLOB, amount IN BIGINT, dest_offset INOUT BIGINT, src_offset INOUT BIGINT)

  Table 38 DBE_LOB.LOB_CONVERTTOCLOB parameters

  Parameter	Description
  dest_clob	Target CLOB, which is converted from a BLOB.
  src_blob	Source BLOB to be read.
  amount	Length of the target BLOB. If the length of a file exceeds this threshold, the file will not be saved to the CLOB.
  dest_offset	Offset length of the CLOB. If dest_offset is set to 1, data is loaded from the start position of the file. The rest may be deduced by analogy. If the offset is less than 1 or greater than the value of LOBMAXSIZE, an error is reported.
  src_offset	Offset length of the BLOB. If src_offset is set to 1, data is read from the start position of the file. The rest may be deduced by analogy. If the offset is less than 1 or greater than the value of LOBMAXSIZE, an error is reported.

• DBE_LOB.GETCHUNKSIZE

  Returns TOAST_MAX_CHUNK_SIZE. When LOB data is stored in the database, TOAST is used internally.

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

  1 2 3 4 5 6 7

  DBE_LOB.GETCHUNKSIZE( lob_loc IN CLOB )RETURN INTEGER DBE_LOB.GETCHUNKSIZE( lob_loc IN BLOB )RETURN INTEGER

  Table 39 DBE_LOB.GETCHUNKSIZE parameters

  Parameter	Description
  lob_loc	Target CLOB/BLOB.

• DBE_LOB.LOB_WRITE

  Reads the specified length of the source object from the start position, writes the content to the specified offset position of the target LOB, overrides the original content, and returns the target LOB.

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

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

  DBE_LOB.LOB_WRITE( clob_obj INOUT CLOB, amount IN INTEGER, off_set IN BIGINT, source IN VARCHAR2 ) RETURN CLOB; DBE_LOB.LOB_WRITE( blob_obj INOUT BLOB, amount IN INTEGER, off_set IN BIGINT, source IN RAW ) RETURN BLOB;

  Table 40 DBE_LOB.LOB_WRITE parameters

  Parameter	Type	Input/Output Parameter	Can Be Empty	Description
  blob_obj/clob_obj	BLOB/CLOB	INOUT	No	Target LOB as the INOUT parameter, to which the content is to be written.
  amount	INTEGER	IN	No	Length of the data to be written (in bytes for BLOBs or in characters for CLOBs)
  off_set	BIGINT	IN	No	Offset position for writing data to blob_obj/clob_obj
  source	RAW/VARCHAR2	IN	No	Source object

• DBE_LOB.BFILENAME

  Constructs a BFILE based on the directory and file name.

  The prototype of DBE_LOB.BFILENAME is as follows:

  1 2 3 4

  DBE_LOB.BFILENAME( directory IN TEXT, filename IN TEXT) RETURN DBE_LOB.BFILE;

  Table 41 DBE_LOB.BFILENAME parameters

  Parameter	Description
  directory	File path NOTE: Location of the file directory, which needs to be added to system catalog 20.2.57-PG_DIRECTORY. If the input path does not match the path in 20.2.57-PG_DIRECTORY, an error indicating that the path does not exist is reported. When the GUC parameter safe_data_path is enabled, you can only use an advanced package to read and write files in the file path specified by safe_data_path. File name with an extension (file type), excluding the path name. The path contained in the file name will be ignored by OPEN. In Unix, the file name cannot end with the combination of a slash and a period (/.).
  filename	File name

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
53
54
55
56
57
58
59
60
61
62

-- Obtain the length of a string.
SELECT DBE_LOB.GET_LENGTH('12345678');
 get_length 
------------
          8
(1 row)

-- DBE_LOB.READ API
DECLARE
myraw  RAW(100);
amount INTEGER :=2;
buffer INTEGER :=1;
begin
DBE_LOB.READ('123456789012345',amount,buffer,myraw);
dbe_output.print_line(myraw);
end;
/
0123
ANONYMOUS BLOCK EXECUTE

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

-- Multiple DBE_LOB APIs
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;
/
1
ANONYMOUS BLOCK EXECUTE

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