SUBSTR

The SUBSTR function returns a specified amount of bytes or characters from a LOB, starting from an absolute offset position relative to the beginning of the LOB.

For a CLOB with a fixed length of n bytes, if the input amount for SUBSTR is greater than (32767/n), SUBSTR returns a value of length (32767/n) or the length of the CLOB, whichever is smaller. For CLOBs in a varying-width character set, n is the maximum byte width of the CLOBs.

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 values

Considerations

• The form of the VARCHAR2 buffer must match the form of the CLOB parameter. That is, if the type of the input LOB parameter is CLOB, the data in the buffer must be of the CHAR type.

• When you call DBMS_LOB.SUBSTR from the client, returned data in the buffer must use the client’s character set. Before the database returns the buffered data to the user, it converts the value of the LOB from the character set of the server to that of the client.

• DBMS_LOB.SUBSTR will return 8,191 or more characters based on the characters stored in the LOB. If all characters are not returned because the character byte size exceeds the available buffer, you can either call DBMS_LOB.SUBSTR with a new offset to read the remaining characters, or call the subprogram in a loop until all the data is extracted.

• When necessary, SUBSTR obtains the LOB before it reads data.