How to implement 'search' similar to Google

[javaq]

I am using Oracle9iR2 / JDBC. I need to implement a search facility
similar to google.com on the database I have. There are 20 tables in
the database and around 30000 records in total. I have to search only
in VARCHAR2 and CLOB fields. Each table has an ID (PK) column. How to
write a single query that search these tables for given string.

Here is a sample for quick understanding.. .
............... ............... ..............

CREATE TABLE A (ID NUMBER, TITLE VARCHAR2(200), DESCRIPTION CLOB);
CREATE TABLE B (ID NUMBER, TITLE VARCHAR2(200), DESCRIPTION CLOB,
NOTES VARCHAR2(500));

INSERT INTO A VALUES(101, 'Best online shop', 'Amazon.com is one of
the best online book store');
INSERT INTO A VALUES(102, 'Movie of the week', 'Beautiful Mind
featuring...');
INSERT INTO B VALUES(201, 'Page available', 'The page is currently
unavailable', 'no notes');
INSERT INTO B VALUES(202, 'Address bar', 'type the page address in the
Address bar', 'store');

Here is what I am trying... (Please correct)
............... ............... ..............
SELECT ID, TEXT
FROM (SELECT ID, (TITLE || ' ' || DESCRIPTION) TEXT
FROM A
WHERE UPPER(TITLE) LIKE UPPER('%store%' )
OR UPPER(DESCRIPTI ON) LIKE UPPER('%store%' )
)
UNION
SELECT ID, TEXT
FROM (SELECT ID, (TITLE || ' ' || DESCRIPTION || ' ' || NOTES) TEXT
FROM B
WHERE UPPER(TITLE) LIKE UPPER('%store%' )
OR UPPER(DESCRIPTI ON) LIKE UPPER('%store%' )
OR UPPER(NOTES) LIKE UPPER('%store%' )
)

When I run the individual part of above query it runs fine but after
UNION it gives
SELECT ID, TEXT
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CLOB

How to solve the problem. ****OR*** Please suggest RIGHT way of doing
the search (similar to google).

Thanks

[hrishy]

Re: How to implement 'search' similar to Google

Hi
Do the following


1)Please go to tahiti.oracle.c om

2)register yourself registration is free

3)serach for intermedia.or oracle text


this is exactly what your looking for.

regards
Hrishy