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(DESCRIPTION) LIKE UPPER('%store%')
)
UNION
SELECT ID, TEXT
FROM (SELECT ID, (TITLE || ' ' || DESCRIPTION || ' ' || NOTES) TEXT
FROM B
WHERE UPPER(TITLE) LIKE UPPER('%store%')
OR UPPER(DESCRIPTION) 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]

Hi
Do the following
1)Please go to tahiti.oracle.com

2)register yourself registration is free

3)serach for intermedia.or oracle text
this is exactly what your looking for.

regards
Hrishy