Oracle Database - DBMS_LOB Package

> Database > Oracle Database


2.1 - About

This function returns the matching position of an occurrence of a pattern in the LOB. If the function returns 0, it signify that the pattern was not found.

2.2 - Syntax

SELECT DBMS_LOB.INSTR(myBlob, 'myPattern', myStartOffset, myStartOccurenceNumber),
FROM myTableWithALob


  • myBlob is of a CLOB of a BLOB column
  • myPattern is a character string (CLOBs) or a RAW bytes (BLOBs)
  • myStartOffset is the absolute offset in characters (CLOBs) or bytes (BLOBs) at which the pattern matching is to start. (default: 1)
  • myStartOccurenceNumber is the occurrence number (default:1)

2.3 - Example

The following SQL

SELECT DBMS_LOB.INSTR(physical_log.query_blob, 'DI_OBIEE_AIRLINE_AGG.SA_'),
FROM s_nq_db_acct physical_log

return the position of the first occurrence of DI_OBIEE_AIRLINE_AGG.SA_ :