# Oracle 19c CLOB Search: DBMS_LOB.INSTR and SQL Examples

- Link: https://ultimasolution.pl/oracle-clob-search-string/
- Published: 2017-09-14T17:43:07+02:00
- Author: Karol Preiskorn

## Clobs

Not many tables demand the use of `CLOBs` (_**C**haracter **L**arge **Ob**jects_).`
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:

    ```EnlighterJSRAW
    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](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

    ```EnlighterJSRAW
    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

    ```EnlighterJSRAW
    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

    ```EnlighterJSRAW
    select * from my_table where dbms_lob.instr(product_details,'NEW.PRODUCT_NO')>=1;
    ```

Other Functions and Operators on LOBs are [here](http://docs.oracle.com/cd/B19306_01/appdev.102/b14249/adlob_sql_semantics.htm#g1016221).

    ```EnlighterJSRAW
    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](https://www.ultimasolution.pl/category/programming/database/plsql/) (`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](https://ultimasolution.pl/oracle-clob-search-string/#readLOB_proc)
is not part of the `DBMS_LOB` package: reading data from lob.

    ```EnlighterJSRAW
    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

    ```EnlighterJSRAW
    SELECT a.*, dbms_lob.substr( sql_text, 4000, 1) sql_text_vc FROM dba_hist_sqltext a;
    ```

### Convert string to CLOB

    ```EnlighterJSRAW
    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

    ```EnlighterJSRAW
    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

    ```EnlighterJSRAW
    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

    ```EnlighterJSRAW
    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.

    ```EnlighterJSRAW
    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.

    ```EnlighterJSRAW
    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.

    ```EnlighterJSRAW
    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`.

    ```EnlighterJSRAW
    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`.

    ```EnlighterJSRAW
    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.

    ```EnlighterJSRAW
    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.

    ```EnlighterJSRAW
    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.

    ```EnlighterJSRAW
    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.INSTR` for a complete CLOB search.
 * Use `DBMS_LOB.SUBSTR` only 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. |
