The DBMS_LOB.SUBSTR function in Oracle is a part of the DBMS_LOB package, is specifically used to extract a substring from a LOB (Large Object) value, which can be either a BLOB (Binary Large Object), CLOB (Character Large Object), NCLOB (National Character Large Object), or BFILE. This function is particularly useful when you need to manipulate or display parts of a LOB without needing to work with the entire LOB, which can be large and cumbersome to handle in its entirety.
Syntax
The basic syntax of the DBMS_LOB.SUBSTR function is:
DBMS_LOB.SUBSTR( lob_loc, amount, offset )
Parameters
lob_loc: This is a LOB locator of type BLOB, CLOB, NCLOB, or BFILE. It points to the LOB from which you want to extract the substring.
amount: This specifies the length of the substring to extract. For CLOBs and NCLOBs, this length is in characters. For BLOBs, the length is in bytes.
offset: This is the starting point from which the substring will be extracted. The first position in the LOB is 1. This value specifies where in the LOB to begin extraction.
Return Value
The function returns a substring of the specified LOB. The type of the returned value matches the type of the input LOB, so for a CLOB or NCLOB input, the return value is a character string. For a BLOB input, the return value is a raw string.
Examples
Extracting from a CLOB
SELECT DBMS_LOB.SUBSTR(clob_column, 300, 1) AS clob_substring FROM my_table WHERE id = 1;
This example extracts the first 300 characters from the clob_column of my_table where the id is 1.
Extracting from a BLOB
SELECT DBMS_LOB.SUBSTR(blob_column, 1024, 1) AS blob_substring FROM my_table WHERE id = 2;
This example extracts the first 1024 bytes from the blob_column of my_table where the id is 2.
Usage Considerations
When working with LOBs, especially large ones, operations can consume significant resources. Extracting only the needed parts of a LOB can help mitigate this.
The DBMS_LOB.SUBSTR function works similarly to the standard SUBSTR function but is designed to handle the peculiarities and requirements of LOB data types.
Remember to consider the character set of your LOB data. For example, when working with multibyte character sets in CLOBs or NCLOBs, the amount of data you extract in characters may represent a different number of bytes than you might expect.
The DBMS_LOB.SUBSTR function is a powerful tool for handling large objects in Oracle databases, allowing for efficient manipulation and retrieval of portions of these large data types.