Search using like in a clob column

From: Octavian Rasnita <orasnita_at_gmail.com>
Date: Mon, 30 Aug 2010 19:56:45 +0300
Message-ID: <90E32F2994E246E897D3C7503B06F383_at_teddy>



Hello,

I have tried to search in a clob column using the like operator, but I can see that it doesn't work as expected.

Is there a way of using a longer kind of varchar2 column, say... one that contains up to 200 KB to be able to search using the like operator?

I am asking this because I can see that the clob columns have too many limitations and nowadays 200 KB doesn't mean a too "long" object.

I see that I can search in a clob column using the like operator, but not case insensitively and I also found this search pretty strange, because if I use sqlplus I can search using something like:

select * from table_name where clob_column like '%text%';

It works, as well as:
select * from table_name where CLOB_COLUMN like '%text%';

...but when I use the DBIx::Class ORM I can use the second line only (although the table name and the column were created without using quotes for forcing a certain column name case).

I used:

alter session set NLS_COMP='LINGUISTIC'; alter session set NLS_SORT='BINARY_AI';

(I have also tried to set only case insesitively and not accent insensitively using BINARY_CI, but with no good effect).

If I have the same text in a varchar2 column, I can do the case and accent insensitive search, but not in the clob column because it returns the records only if they contain the exact match in that clob column.

The reason I hope there is a way of avoiding to use Oracle Text is that the syntax I need to use is too specific to Oracle and I don't even know how easy would be to use it with the ORM I use.

Thanks.

Octavian

--
http://www.freelists.org/webpage/oracle-l
Received on Mon Aug 30 2010 - 11:56:45 CDT

Original text of this message