Oracle full-text search for CLOB Fields

The SELECT statement can be used to search the full text of the CLOB field. However, if you must use the dbms_lob.instr () function when using the WHERE clause, the command is as follows:

First, create a TEST table:
SQL> create table TEST3 (a clob, B INT, C INT );
Insert data to the table:
SQL> INSERT INTO TEST3 VALUES ('test clob', 11111,22222 );

1 row created.

SQL> commit;

Commit complete.

Search tables:

SQL> SELECT * FROM TEST3 T WHERE dbms_lob.instr (T. A, 'test', 1, 1)> 0;

A B C
----------------------------------------------------------------------------------------------------
Test clob 11111 22222