How to implement 'search' similar to Google

From: javaq <nougain_at_hotmail.com>
Date: 26 Oct 2003 21:50:43 -0800
Message-ID: <e9af93aa.0310262150.16a72758_at_posting.google.com>

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 Received on Mon Oct 27 2003 - 06:50:43 CET