Oracle9i Supplied PL/SQL Packages and Types Reference Release 1 (9.0.1) Part Number A89852-02 |
|
DBMS_LOB , 17 of 26
This function returns the matching position of the nth occurrence of the pattern in the LOB
, starting from the offset you specify.
The form of the VARCHAR2
buffer (the pattern
parameter) must match the form of the CLOB
parameter. In other words, if the input LOB
parameter is of type NCLOB
, then the buffer must contain NCHAR
data. Conversely, if the input LOB
parameter is of type CLOB
, then the buffer must contain CHAR
data.
For BFILEs
, the file must be already opened using a successful FILEOPEN
operation for this operation to succeed.
Operations that accept RAW
or VARCHAR2
parameters for pattern matching, such as INSTR
, do not support regular expressions or special matching characters (as in the case of SQL LIKE
) in the pattern parameter or substrings.
DBMS_LOB.INSTR ( lob_loc IN BLOB, pattern IN RAW, offset IN INTEGER := 1, nth IN INTEGER := 1) RETURN INTEGER; DBMS_LOB.INSTR ( lob_loc IN CLOB CHARACTER SET ANY_CS, pattern IN VARCHAR2 CHARACTER SET lob_loc%CHARSET, offset IN INTEGER := 1, nth IN INTEGER := 1) RETURN INTEGER; DBMS_LOB.INSTR ( file_loc IN BFILE, pattern IN RAW, offset IN INTEGER := 1, nth IN INTEGER := 1) RETURN INTEGER;
pragma restrict_references(INSTR, WNDS, WNPS, RNDS, RNPS);
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;
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|