You don't mention what version of Oracle you're using. I haven't had opportunity to play around with them, but are Regular Expressions an option available to you? Regards, Gus On Mon, Aug 30, 2010 at 12:56 PM, Octavian Rasnita <orasnita@xxxxxxxxx>wrote: > 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 > > -- > //www.freelists.org/webpage/oracle-l > > >