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.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only MySQL mode.
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 |
| lob_loc | The locator of the LOB to read. For more information, see DBMS_LOB. |
| 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
| Return value | Description |
|---|
| 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. amountis less than 1.amountexceeds 32767 bytes (or the equivalent number of characters).offsetis less than 1.offsetexceedsLOBMAXSIZE.
Considerations
The
VARCHAR2buffer must match the format of theCLOBparameter. In other words, if the inputLOBparameter is of typeCLOB, the buffer must containCHARdata.When you call
DBMS_LOB.SUBSTRfrom a client, the returned buffer contains data in the client's character set. Before returning the buffer to the user, the database converts theLOBvalue from the server's character set to the client's character set.DBMS_LOB.SUBSTRreturns 8191 or more characters based on the characters stored in theLOB. If not all characters are returned due to the byte size of the characters exceeding the available buffer, you can callDBMS_LOB.SUBSTRagain with a new offset to read the remaining characters, or call the procedure repeatedly until all data is extracted.SUBSTRacquires theLOBbefore reading it.