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
explorer
Messages: 2
Registered: March 2008
Location: Singapore
Junior Member

Hi,

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: 8635
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  /

CLOB_COLUMN
--------------------------------------------------------------------------------
some visual basic script

SCOTT@orcl_11g> 

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: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Always post your Oracle version (4 decimals).

The answer depends on your version.

Regards
Michel
Previous Topic: How do create a job to run my add my keys & indexes?
Next Topic: Urgent Help Required!! PL/SQL Table Doubt
Goto Forum:
  


Current Time: Fri Dec 09 01:43:24 CST 2016

Total time taken to generate the page: 0.16419 seconds