SUBSTR

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

For a fixed-width CLOB with width n bytes, if the amount parameter exceeds (32767/n), the SUBSTR function returns a substring of length (32767/n) or the CLOB length, whichever is smaller. The n value represents the maximum width of the CLOB in bytes.

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 be read from the BLOB value or the number of characters to be read from the CLOB value. | | offset | The offset in bytes (for the BLOB value) or in characters (for the CLOB value) at which to start reading the LOB value. The default value is 1. |

Return value

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

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

• Any input parameter is NULL.
• The amount parameter is less than 1.
• The amount parameter exceeds 32767 bytes (or the equivalent number of characters).
• The offset parameter is less than 1.
• The offset parameter 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 character set. Before returning the buffer to the user, the database converts the LOB value from the server character set to the client 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 buffer size being insufficient, you can call DBMS_LOB.SUBSTR with a new offset to read the remaining characters, or call the procedure repeatedly until all data is extracted.

• If necessary, SUBSTR retrieves the LOB before reading it.