lob_loc Locator for the LOB to be examined. pattern Pattern to be tested for. The pattern is a group of RAW bytes for BLOBs, and a character string (VARCHAR2) for CLOBs.The maximum size of the pattern is 16383 bytes.
offset Absolute offset in bytes (BLOBs) or characters (CLOBs) at which the pattern matching is to start. (origin: 1)
nth Occurrence number, starting at 1. Examples CREATE OR REPLACE PROCEDURE Example_12a IS lobd CLOB; pattern VARCHAR2 := 'abcde'; position INTEGER := 10000; BEGIN -- get the LOB locator SELECT b_col INTO lobd FROM lob_table WHERE key_value = 21; position := DBMS_LOB.INSTR(lobd, pattern, 1025, 6); IF position = 0 THEN dbms_output.put_line('Pattern not found'); ELSE dbms_output.put_line('The pattern occurs at ' || position); END IF; END;CREATE OR REPLACE PROCEDURE Example_12b IS DECLARE fil BFILE; pattern VARCHAR2; pos INTEGER; BEGIN -- initialize pattern -- check for the 6th occurrence starting from 1025th byte SELECT f_lob INTO fil FROM lob_table WHERE key_value = 12; dbms_lob.fileopen(fil, dbms_lob.file_readonly); pos := dbms_lob.instr(fil, pattern, 1025, 6); dbms_lob.fileclose(fil); END;
pattern Pattern to be tested for. The pattern is a group of RAW bytes for BLOBs, and a character string (VARCHAR2) for CLOBs.The maximum size of the pattern is 16383 bytes.
offset Absolute offset in bytes (BLOBs) or characters (CLOBs) at which the pattern matching is to start. (origin: 1)
nth Occurrence number, starting at 1.
Examples
CREATE OR REPLACE PROCEDURE Example_12a IS
lobd CLOB;
pattern VARCHAR2 := 'abcde';
position INTEGER := 10000;
BEGIN
-- get the LOB locator
SELECT b_col INTO lobd
FROM lob_table
WHERE key_value = 21;
position := DBMS_LOB.INSTR(lobd,
pattern, 1025, 6);
IF position = 0 THEN
dbms_output.put_line('Pattern not found');
ELSE
dbms_output.put_line('The pattern occurs at '
|| position);
END IF;
END;CREATE OR REPLACE PROCEDURE Example_12b IS
DECLARE
fil BFILE;
pattern VARCHAR2;
pos INTEGER;
BEGIN
-- initialize pattern
-- check for the 6th occurrence starting from 1025th byte
SELECT f_lob INTO fil FROM lob_table WHERE key_value = 12;
dbms_lob.fileopen(fil, dbms_lob.file_readonly);
pos := dbms_lob.instr(fil, pattern, 1025, 6);
dbms_lob.fileclose(fil);
END;