Re: Search using like in a clob column
- From: "Octavian Rasnita" <orasnita@xxxxxxxxx>
- To: "Tim Hall" <tim@xxxxxxxxxxxxxxx>, <gus.spier@xxxxxxxxx>
- Date: Tue, 31 Aug 2010 12:19:01 +0300
Thank you. I already read about Oracle Text, but The following part has a
non-standard syntax:
WHERE CONTAINS(doc, 'SQL Server', 1) > 0
because I don't know how to use it in DBIx::Class ORM.
It would have been much easier if it was
where doc CONTAINS 'SQL Server';
In the program I make, it was very easy to port most of the database queries
from MySQL to Oracle because I use this ORM, but now I find some problems with
the text fields because they can't be accessed as easy as the varchar2 fields.
--
Octavian
----- Original Message -----
From: Tim Hall
To: gus.spier@xxxxxxxxx
Cc: orasnita@xxxxxxxxx ; oracle-l@xxxxxxxxxxxxx
Sent: Tuesday, August 31, 2010 11:37 AM
Subject: Re: Search using like in a clob column
Hi.
Oracle Text allows you to index VARCHAR2s and CLOBs and the CONTAINS and
CATSEARCH operators allow you to perform the equivalent of LIKE efficiently on
them.
http://www.oracle-base.com/articles/9i/FullTextIndexingUsingOracleText9i.php
Remember, added more and complex indexes to a table increases the overhead on
DML, so they are not a universal cure-all.
Cheers
Tim...
On Tue, Aug 31, 2010 at 4:10 AM, Gus Spier <gus.spier@xxxxxxxxx> wrote:
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
__________ Information from ESET NOD32 Antivirus, version of virus signature
database 5410 (20100830) __________
The message was checked by ESET NOD32 Antivirus.
http://www.eset.com
__________ Information from ESET NOD32 Antivirus, version of virus signature
database 5411 (20100831) __________
The message was checked by ESET NOD32 Antivirus.
http://www.eset.com
Other related posts:
