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 value of n bytes, if the amount parameter exceeds 32767/n, the SUBSTR function returns a value of length 32767/n or the length of the CLOB value, whichever is smaller. n is the maximum width in bytes of the CLOB value.
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 value to be read. For more information, see Use DBMS_LOB. |
| 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, from which to start reading the BLOB value or the offset, in characters, from which to start reading the CLOB value. The default value is 1. |
Return value
| Return value | Description |
|---|---|
| RAW | The function overload that contains the BLOB parameter. |
| VARCHAR2 | The CLOB version. |
| NULL | The return value is NULL in the following cases:
|
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 character set. Before returning the buffer to the user, the database converts theLOBvalue from the server character set to the client character set.DBMS_LOB.SUBSTRreturns 8191 or more characters based on the characters stored in theLOBvalue. If not all characters are returned because the number of bytes required for the characters exceeds the available buffer size, you can callDBMS_LOB.SUBSTRagain with a new offset to read the remaining characters, or call the procedure repeatedly until all data is extracted.SUBSTRretrieves theLOBvalue before reading it.