Oracle Database - DBMS_LOB Package

Card Puncher Data Processing



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.


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)


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_ :


Discover More
Card Puncher Data Processing
Oracle - LOB Datatype

LOB in Oracle In Oracle, a LOB is simply a pointer that points to an index that is stored in a LOBINDEX segment. The index points to the chunks that make up the LOB that are stored in LOBSEGMENT segment....

Share this page:
Follow us:
Task Runner