SUBSTR v17

The SUBSTR operation returns a portion of a large object or BFILE starting at the specified offset. The data returned is raw data.

<data> { RAW | VARCHAR2 } SUBSTR(<lob_loc> { BLOB | CLOB }
   [, <amount> NUMBER [, <offset> NUMBER ]])

<data> { RAW } SUBSTR(<file_loc> IN { BFILE }[, <amount> IN NUMBER, <offset> IN NUMBER])

Parameters

lob_loc

Large object locator of the large object to read.

file_loc

File locator for the BFILE to read.

amount

Number of bytes/characters to return. Default is 32,767.

offset

Position in the large object to begin returning data. The first byte/character is position 1. The default is 1.

data

Returned portion of the large object to read. If lob_loc is a BLOB or file_loc is a BFILE, the return data type is RAW. If lob_loc is a CLOB, the return data type is VARCHAR2.

Example

DECLARE
   src_loc      BFILE := BFILENAME('ANOTHER_DIR', 'a.txt');
   Position     NUMBER := 1;
   Buffer       RAW(1024);
   Amount       NUMBER := 1024;
BEGIN
  /* Opening the source BFILE is mandatory */
   DBMS_LOB.OPEN(src_loc, DBMS_LOB.LOB_READONLY);

   Buffer := DBMS_LOB.SUBSTR(src_loc, Amount, position);

   /* Close the BFILE: */
   DBMS_LOB.FILECLOSE(src_loc);
   COMMIT;
END;