Learn how to search Oracle 19c CLOB columns with DBMS_LOB.INSTR, DBMS_LOB.SUBSTR, JSON operators, Oracle Text, match offsets, case-insensitive patterns, and safe update examples.

Clobs
Not many tables demand the use of CLOBs (Character Large Objects). CLOB data type columns can store up to 4 GB of text. When designing a new application, developers face a dilemma – I need a column where I need to store a large block of text. Should I use multiple VARCHAR2â columns or CLOB?
The decision to use VARCHAR2 instead of CLOB is usually driven by the fear of the unknown. One should always use CLOB when it comes to deciding about multiple VARCHAR2 columns vs. CLOB to store a single logical piece of textual data.
Example use string in Oracle CLOB column:
create table aTable(
id number,
aClobColumn clob
);
insert into aTable values (1,'value' );
insert into aTable values (1,'values are like this' );
select * from aTable where dbms_lob.substr( aClobColumn , 4000, 1 )='value';
Source, try it online: http://sqlfiddle.com/#!4/1878f6/164
Accessing a CLOB as a VARCHAR2 in PL/SQL
The following example illustrates the way CLOB manage.
Length of CLOB
declare myStoryBuf VARCHAR2(4001); BEGIN SELECT ad_sourcetext INTO myStoryBuf FROM print_media WHERE ad_id = 12001; -- Display Story by printing myStoryBuf directly END; /
Assigning a CLOB to a VARCHAR2 in PL/SQL
declare myLOB CLOB;
BEGIN
SELECT 'ABCDE' INTO myLOB FROM print_media WHERE ad_id = 11001;
-- myLOB is a temporary LOB.
-- Use myLOB as a lob locator DBMS_OUTPUT.PUT_LINE('Is temp? '||DBMS_LOB.ISTEMPORARY(myLOB));
END;
/
Find in CLOB string
select * from my_table where dbms_lob.instr(product_details,'NEW.PRODUCT_NO')>=1;
Other Functions and Operators on LOBs are here.
select dbms_lob.getlength(ad_sourcetext) from Print_media where product_id=3106 and ad_id = 13001; / declare clob1 clob; amt number:=10; BEGIN -- select a clob column into a clob, no implicit convesion SELECT ad_sourcetext INTO clob1 FROM Print_media WHERE product_id=3106 and ad_id=13001 FOR UPDATE; dbms_lob.trim(clob1, amt); -- Trim the selected lob to 10 bytes END; /
Read from CLOB column
PL/SQL (DBMS_LOB Package): Reading Data from a LOB. This file is installed in the following path when you install the database: $ORACLE_HOME/rdbms/demo/lobs/plsql/lread.sql. Procedure readLOB_proc is not part of the DBMS_LOB package: reading data from lob.
CREATE OR REPLACE PROCEDURE readLOB_proc (Lob_loc IN OUT BLOB) IS
/* Note: Lob_loc can be a persistent or a temporary LOB */
Buffer RAW(32767);
Amount BINARY_INTEGER := 32767;
Position INTEGER := 2;
BEGIN
DBMS_OUTPUT.PUT_LINE('------------ LOB READ EXAMPLE ------------');
/* Opening the LOB is optional: */
DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READONLY);
/* Read data from the LOB: */
DBMS_LOB.READ (Lob_loc, Amount, Position, Buffer);
/* Closing the LOB is mandatory if you have opened it: */
DBMS_LOB.CLOSE (Lob_loc);
DBMS_OUTPUT.PUT_LINE(RAWTOHEX(substr(Buffer, 1, 200)));
END;
/
SUBSTR
SELECT a.*, dbms_lob.substr( sql_text, 4000, 1) sql_text_vc FROM dba_hist_sqltext a;
Convert string to CLOB
declare
c1 clob := to_clob('abc');
c2 clob;
begin
case c1
when to_clob('abc') then dbms_output.put_line('abc');
when to_clob('def') then dbms_output.put_line('def');
end case;
c2 := case c1
when to_clob('abc') then 'abc'
when to_clob('def') then 'def'
end;
dbms_output.put_line(c2);
end;
/
select dbms_lob.substr( x, 4000, 1 ) from T;
/
Update CLOB columns
declare
v_lob clob;
begin
for r_lob in (select cfrom foo for update)
loop
dbms_lob.write(r_lob.c, 6, 16, 'phrase'); -- ie write at offset 16, 6 bytes
end loop;
end;
Replace in CLOB column
select to_clob(replace(' and ','and', 'or')) from dual;
TO_CLOB(REPLACE('AND','AND','OR'))
SELECT regexp_replace('I need to replace this Everything can be here ', '.*','') FROM dual;
REGEXP_REPLACE
REGEXP_REPLACE( string, pattern [, replacement_string [, start_position [, nth_appearance [, match_parameter ] ] ] ] )
Parameters or Arguments
The string to search. It can be CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The regular expression matching information.
Parameters can be a combination of the :
Oracle 19c CLOB search examples
For Oracle Database 19c, use DBMS_LOB.INSTR when the text may be larger than the SQL VARCHAR2 limit. It searches the CLOB directly and returns the character position of the match, or 0 when the string is not found.
SELECT id,
DBMS_LOB.INSTR(aClobColumn, 'ORA-600') AS match_position
FROM aTable
WHERE DBMS_LOB.INSTR(aClobColumn, 'ORA-600') > 0;
Search from a specific offset or find the second occurrence
The third argument is the starting character offset and the fourth argument selects the occurrence. This is useful when a CLOB contains repeated tokens or when a scan should continue after a previous match.
SELECT DBMS_LOB.INSTR(aClobColumn, 'ERROR', 1, 2) AS second_match FROM aTable WHERE id = 1; SELECT DBMS_LOB.INSTR(aClobColumn, 'ERROR', 5001, 1) AS match_after_offset FROM aTable WHERE id = 1;
Case-insensitive CLOB search
DBMS_LOB.INSTR is case-sensitive. For a small search window, normalize the extracted text with DBMS_LOB.SUBSTR. Do not use this pattern as a replacement for a full-CLOB scan because it only examines the requested window.
SELECT id FROM aTable WHERE INSTR(UPPER(DBMS_LOB.SUBSTR(aClobColumn, 4000, 1)), 'ERROR') > 0;
Search a CLOB larger than 4,000 characters
In SQL, DBMS_LOB.SUBSTR returns only a bounded piece of the CLOB. Use DBMS_LOB.INSTR for a complete search rather than converting the whole value to VARCHAR2.
SELECT id FROM aTable WHERE DBMS_LOB.INSTR(aClobColumn, 'customer_reference=ABC-123') > 0;
Read a matching CLOB fragment
After locating a match, return a bounded preview around the position. The result remains suitable for SQL clients that expect a VARCHAR2.
SELECT id,
DBMS_LOB.SUBSTR(
aClobColumn,
200,
GREATEST(DBMS_LOB.INSTR(aClobColumn, 'ORA-600') - 80, 1)
) AS context_text
FROM aTable
WHERE DBMS_LOB.INSTR(aClobColumn, 'ORA-600') > 0;
Update a matching CLOB safely
For a simple replacement, REPLACE is convenient only when the complete value can be handled within the SQL character limits. For larger documents, use a temporary CLOB and write the result in controlled chunks, or use an application-side LOB API.
UPDATE aTable SET aClobColumn = REPLACE(aClobColumn, 'old-token', 'new-token') WHERE DBMS_LOB.INSTR(aClobColumn, 'old-token') > 0;
Always test the predicate first, take a backup or use a transaction, and verify DBMS_LOB.INSTR after the update. A successful update count does not prove that every intended occurrence was replaced.
Search JSON stored in a CLOB
When the CLOB contains valid JSON, prefer Oracle 19c JSON operators for property-aware searches instead of searching for an arbitrary text fragment. This avoids false positives caused by values in unrelated properties.
SELECT id FROM aTable WHERE JSON_EXISTS(aClobColumn, '$?(@.status == "ERROR")'); SELECT id FROM aTable WHERE JSON_VALUE(aClobColumn, '$.customer.id' RETURNING NUMBER) = 42;
When to use Oracle Text
For frequent searches across many large CLOB rows, repeated DBMS_LOB.INSTR scans can be expensive. Evaluate an Oracle Text CONTEXT index and query with CONTAINS. Measure indexing cost, commit synchronization, language settings, and query selectivity before changing production workloads.
CREATE INDEX aTable_clob_text_ix ON aTable(aClobColumn) INDEXTYPE IS CTXSYS.CONTEXT; SELECT id FROM aTable WHERE CONTAINS(aClobColumn, '"ORA-600"') > 0;
Oracle 19c CLOB search checklist
- Use
DBMS_LOB.INSTRfor a complete CLOB search. - Use
DBMS_LOB.SUBSTRonly when a bounded text fragment is sufficient. - Remember that offsets and lengths are character-based for CLOB data.
- Use JSON operators for structured JSON documents stored in CLOB columns.
- Consider Oracle Text for repeated full-text searches over large datasets.
- Test execution plans, logical I/O, CPU, and concurrency with representative data.
| Value | Description |
|---|---|
| ^ | Matches the beginning of a string. If used with a match_parameter of ‘m’, it matches the start of a line anywhere within the expression. |
| $ | Matches the end of a string. If used with a match_parameter of ‘m’, it matches the end of a line anywhere within the expression. |
| * | Matches zero or more occurrences. |
| + | Matches one or more occurrences. |
| ? | Matches zero or one occurrence. |
| . | Matches any character except NULL. |
| | | Used like an “OR” to specify more than one alternative. |
| [ ] | Used to specify a matching list where you are trying to match any one of the characters in the list. |
| [^ ] | Used to specify a non-matching list where you are trying to match any character except for the ones in the list. |
| ( ) | Used to group expressions as a sub-expression. |
| {m} | Matches m times. |
| {m,} | Matches at least m times. |
| {m,n} | Matches at least m times, but no more than n times. |
| n | Matches the nth sub-expression found within “()” before encountering ‘n’. When n is a number between 1 and 9. |
| [..] | Matches one collation element that can be more than one character. |
| [::] | Matches character classes. |
| [==] | Matches equivalence classes. |
| d | Matches a digit character. |
| D | Matches a nondigit character. |
| w | Matches a word character. |
| W | Matches a nonword character. |
| s | Matches a whitespace character. |
| S | matches a non-whitespace character. |
| A | Matches the beginning of a string or matches at the end of a string before a newline character. |
| Z | Matches at the end of a string. |
| *? | Matches the preceding pattern zero or more occurrences. |
| +? | Matches the preceding pattern one or more occurrences. |
| ?? | Matches the preceding pattern, zero or one occurrence. |
| {n}? | Matches the preceding pattern n times. |
| {n,}? | Matches the preceding pattern at least n times. |
| {n,m}? | Matches the preceding pattern at least n times, but not more than m times. |
