SUBSTR

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

For a fixed-width CLOB of n bytes, if the input to SUBSTR exceeds 32767/n, SUBSTR returns a value of length 32767/n or the CLOB length, whichever is smaller. n is 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

Return value

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 can be returned due to the buffer size, you can call DBMS_LOB.SUBSTR again with a new offset to read the remaining characters or loop through the procedure until all data is extracted.

• SUBSTR retrieves the LOB before reading it.