SUBSTR

The SUBSTR function returns amount bytes or characters from the specified LOB starting at the absolute offset of the LOB.

For a fixed-width CLOB of n bytes, if the input amount exceeds 32767/n, SUBSTR returns a substring of length 32767/n or the length of the CLOB (whichever is smaller). Since CLOB is a variable-width character set, n represents the maximum byte width of the CLOB.

Syntax

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

DBMS_LOB.SUBSTR (
lob_loc     IN    CLOB CHARACTER SET ANY_CS,
amount      IN    INTEGER := 32767,
offset      IN    INTEGER := 1)
RETURN VARCHAR2;

Parameters

| Parameter | Description |

| amount | The number of bytes to read for a BLOB or the number of characters to read for a CLOB. | | offset | The offset (in bytes for a BLOB or in characters for a CLOB) at which to start reading the LOB. The default value is 1. |

Return value

| RAW | The function overload that contains a BLOB parameter. | | VARCHAR2 | The CLOB version. |

| NULL | The return value is NULL in the following cases:

• Any input parameter is NULL.
• amount is less than 1.
• amount exceeds 32767 bytes (or the equivalent number of characters).
• offset is less than 1.
• offset exceeds LOBMAXSIZE.

Considerations

• The VARCHAR2 buffer must match the format of the CLOB parameter. In other words, if the input LOB parameter is of type CLOB, the buffer must contain CHAR data.

• When you call DBMS_LOB.SUBSTR from a client, the returned buffer contains data in the client's character set. Before returning the buffer to the user, the database converts the LOB value from the server's character set to the client's character set.

• DBMS_LOB.SUBSTR returns 8191 or more characters based on the characters stored in the LOB. If not all characters are returned due to the byte size of the characters exceeding the available buffer, you can call DBMS_LOB.SUBSTR again with a new offset to read the remaining characters, or call the procedure repeatedly until all data is extracted.

• SUBSTR acquires the LOB before reading it.