Win a copy of Learn Spring Security (video course) this week in the Spring forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Searching words in VARCHAR2 & CLOB

 
yashendra chandrakar
Greenhorn
Posts: 22
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 925
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 22
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 925
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
are you running 9i or 8.x ?
the 'contains' syntax should cover all your advanced search queries.
 
yashendra chandrakar
Greenhorn
Posts: 22
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for reply

i m using Oracle 8.x
 
SJ Adnams
Ranch Hand
Posts: 925
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1467
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic