Searching words in VARCHAR2 & CLOB

[yashendra chandrakar]

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]

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]

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]

are you running 9i or 8.x ?
the 'contains' syntax should cover all your advanced search queries.

[yashendra chandrakar]

Thanks for reply

i m using Oracle 8.x

[SJ Adnams]

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]

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

create or replace FUNCTION SEARCHANDREPLACE4(src_clob CLOB)
RETURN CLOB
AS
        ret_clob CLOB;

        temp_clob CLOB;
      last_weird_posNUMBER;
        last_copied_pos NUMBER;
        copy_chunk_size NUMBER;

INVALID_CHAR CHAR := 'f';
BEGIN
IF (src_clob is not null) THEN
-- init the temp_clob to be an empty one
temp_clob := empty_clob();
                last_copied_pos := 1;
last_weird_pos := DBMS_LOB.INSTR(src_clob,INVALID_CHAR,1, 1); -- for now assume the weird char is 'f'

WHILE(last_weird_pos  <> 0 ) LOOP

copy_chunk_size := (last_weird_pos - last_copied_pos);

DBMS_LOB.COPY(temp_clob,src_clob,copy_chunk_size,1,last_copied_pos);
DBMS_LOB.APPEND(ret_clob,temp_clob);

--reset values
temp_clob := empty_clob();
last_weird_pos := DBMS_LOB.INSTR(src_clob,INVALID_CHAR,(last_weird_pos+1), 1);
END LOOP;

END IF;
return ret_clob;
END;
/

SQL> update patient_note
  2  set note = SEARCHANDREPLACE4(NOTE)
  3  where patient_note_key = 1390;
set note = SEARCHANDREPLACE4(NOTE)
           *
ERROR at line 2:
ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified:
ORA-22275
ORA-06512: at "SYS.DBMS_LOB", line 347
ORA-06512: at "R40_DEV_BETA.SEARCHANDREPLACE4", line 32

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 ]