PL/SQL DBMS_LOB.INSTR

The DBMS_LOB.INSTR function in Oracle Database is a part of the DBMS_LOB package, which provides a collection of procedures and functions for manipulating large objects (LOBs). LOBs in Oracle include Binary Large Objects (BLOBs), Character Large Objects (CLOBs), National Character Large Objects (NCLOBs), and External Binary Files (BFILEs). The DBMS_LOB.INSTR function is specifically used to search for a substring within a LOB and return the position at which it is found.

Syntax

The basic syntax of DBMS_LOB.INSTR is as follows:

DBMS_LOB.INSTR (
   lob_loc    IN BLOB | CLOB | NCLOB | BFILE,
   pattern    IN RAW | VARCHAR2,
   offset     IN INTEGER DEFAULT 1,
   nth        IN INTEGER DEFAULT 1
) RETURN INTEGER;

lob_loc: The LOB (BLOB, CLOB, NCLOB, BFILE) in which you are searching for the pattern.
pattern: The substring you are looking for. For BLOBs and BFILEs, this is expressed in RAW; for CLOBs and NCLOBs, it is expressed in VARCHAR2.
offset: The position in the LOB from which the search should start. The first position in the LOB is 1. This is optional; if omitted, the search starts at the beginning of the LOB.
nth: Specifies which occurrence of the pattern you are looking for. For example, a value of 2 means it will return the position of the second occurrence of the pattern. This is optional; if omitted, the function searches for the first occurrence.

Return Value

The function returns an integer indicating the position of the nth occurrence of the pattern in the LOB, starting from the specified offset. If the pattern is not found, the function returns 0.

Examples

Example 1: Searching in a CLOB

DECLARE
   pos INTEGER;
   clob_data CLOB;
BEGIN
   -- Assuming clob_data is already initialized and contains data
   pos := DBMS_LOB.INSTR(clob_data, 'Oracle', 1, 1);
   DBMS_OUTPUT.PUT_LINE('Position: ' || pos);
END;

This example searches for the first occurrence of the string ‘Oracle’ in a CLOB starting from the beginning.

Example 2: Searching for a Second Occurrence in a BLOB

DECLARE
   pos INTEGER;
   blob_data BLOB;
BEGIN
   -- Assuming blob_data is already initialized and contains data
   pos := DBMS_LOB.INSTR(blob_data, HEXTORAW('010203'), 1, 2);
   DBMS_OUTPUT.PUT_LINE('Position: ' || pos);
END;

This example searches for the second occurrence of the byte sequence represented by ‘010203’ in a BLOB.

Use Cases

The DBMS_LOB.INSTR function is useful in scenarios where you need to find the position of specific data within a LOB, such as when processing or extracting data from large text or binary files stored in a database. It is particularly useful for text processing, data migration, or data transformation tasks involving large objects.