Home » SQL & PL/SQL » SQL & PL/SQL » How to search text in CLOB column
How to search text in CLOB column [message #307433] Tue, 18 March 2008 20:28 Go to next message
Messages: 2
Registered: March 2008
Location: Singapore
Junior Member


There is table in which many VB are scripts in CLOB column.
I want to find the particular VB script containing specific text.
Could anyone tell me how to achieve this?

I tried following but failed!

SQL> select * from clob_table where clob_column like '%search text%';
select * from clob_table where clob_column like '%search text%'
ERROR at line 1:
ORA-00932: inconsistent datatypes

Thanks in adv.

Re: How to search text in CLOB column [message #307454 is a reply to message #307433] Tue, 18 March 2008 23:04 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8737
Registered: November 2002
Location: California, USA
Senior Member
Oracle Text context indexes and contains queries are ideal for this sort of thing:

SCOTT@orcl_11g> CREATE TABLE clob_table (clob_column  CLOB)
  2  /

Table created.

SCOTT@orcl_11g> INSERT INTO clob_table VALUES ('some visual basic script')
  2  /

1 row created.

SCOTT@orcl_11g> CREATE INDEX clob_index ON clob_table (clob_column) INDEXTYPE IS CTXSYS.CONTEXT
  2  /

Index created.

SCOTT@orcl_11g> VARIABLE search_text VARCHAR2 (60)
SCOTT@orcl_11g> EXEC :search_text := 'visual basic'

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> SELECT * FROM clob_table WHERE CONTAINS (clob_column, :search_text) > 0
  2  /

some visual basic script


Re: How to search text in CLOB column [message #307498 is a reply to message #307433] Wed, 19 March 2008 00:43 Go to previous message
Michel Cadot
Messages: 65144
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Always post your Oracle version (4 decimals).

The answer depends on your version.

Previous Topic: how to know any table is being used by any mviews
Next Topic: How to compare the Varchar and Number in oracle10g
Goto Forum:

Current Time: Sun Aug 20 23:51:08 CDT 2017

Total time taken to generate the page: 0.04798 seconds