aspose file tools*
The moose likes Oracle/OAS and the fly likes Searching words in VARCHAR2 & CLOB Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Searching words in VARCHAR2 & CLOB" Watch "Searching words in VARCHAR2 & CLOB" New topic
Author

Searching words in VARCHAR2 & CLOB

yashendra chandrakar
Greenhorn

Joined: Mar 22, 2002
Posts: 22
i m a java programmer,want to display search result for words specified by client,for that i need to search that words in content of column with datatype VARCHAR2 or CLOB,and get all the rows for coluimn with that words.
what query should i use for this?
SJ Adnams
Ranch Hand

Joined: Sep 28, 2001
Posts: 925
index the data first using intermedia. Then use the 'contains' keyword.
All the information you need on how to perform the index and 'contains' syntax can be found in the oracle documentation.
(http://docs.oracle.com)
Simon
yashendra chandrakar
Greenhorn

Joined: Mar 22, 2002
Posts: 22
i tried as u said but couldn't get.
suppose i have table REPLY as follows
TOPIC_ID VARCHAR2(5) PRIMARY KEY
REPLY_TEXT CLOB NOT NULL
POSTEDBY VARCHAR(20) NOT NULL
i want to search word 'oracle datatypes' in REPLY_TEXT so i executed query as followes
SELECT * FROM REPLY WHERE CONTAINS(REPLY.REPLY_TEXT , 'oracle datatypes') >0;
but it shows error as follows:
ERROR at line 1:
ORA-00904: invalid column name
it is taking CONTAINS as column name.
even i tried it after creating intex on TOPIC_ID but in vain.
i would like to add one more quesion-
what should i do if i want to provide advance search(search within searched result)?should i store the previous search result in temp table,and provide search on that temp table?
SJ Adnams
Ranch Hand

Joined: Sep 28, 2001
Posts: 925
are you running 9i or 8.x ?
the 'contains' syntax should cover all your advanced search queries.
yashendra chandrakar
Greenhorn

Joined: Mar 22, 2002
Posts: 22
Thanks for reply

i m using Oracle 8.x
SJ Adnams
Ranch Hand

Joined: Sep 28, 2001
Posts: 925
for 8.x you need to use the context search cartridge which is not reliable & not free (i think?).
i'm afraid you will need to restrict your queries to 'like', or upgrade, or use use context or another 3rd party tool.
Simon
maha anna
Ranch Hand

Joined: Jan 31, 2000
Posts: 1467
Hi everyone,
Currently I am in middle of a production problem in my work and researching for a solution to do a 'search and replace' operation inside an Oracle 9i CLOB column.
Last week end we moved from Oracle 8i to 9i. Some of our user notes saved inside a CLOB column in oracle 8i now show a weird (upside down question mark) char in 9i. We could not know at what stage these weird chars got into database.
As a quick solutuon we decided to go for a search-and-replace operation to replace these special char with correct one.
I wrote a custom search and replace function also. But when I call this function from SQLPlus I get an ORA error. Please note that I am not an expert in PL/SQL. I am a Java person. But for this production problem I volunteered to work.
Could anyone help with this situation? Thanks a lot for any pointers/help.
Regards,
Maha Anna


The error occurs at this line:
DBMS_LOB.COPY(temp_clob,src_clob,copy_chunk_size,1,last_copied_pos);
Thanks
Maha
[ April 07, 2004: Message edited by: maha anna ]
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Searching words in VARCHAR2 & CLOB