Oracle text search across multiple tables and columns

Question

In my java web application want to put "global search" text box that would search against multiple tables (different columns) using "Oracle Text"?

e.g.

Table Student {name}

Table Teacher {name}

Table Department{name, description}

Table subject{name, title}

Now what I would like to do is that if user enter "Chem" that this word should be searched against Student:name, Teacher:name, Department:name, Department:description, Subject:name, Subject:title column and it would sort the result according to its Oracle Text Score?

Can anyone advise what the best way to achieve this? Not sure how to search all those column across multiple tables with single query using "Contains" oracle text function?

Also, please assume no relation between tables. Thanks in advance!

Unknown Unknown · Accepted Answer · 2013-02-20T12:17:16

You could do something like this:

with search_fields as (
    select 'student' as table,
           'name' as field,
           name as text
       from student
    union all 
    select 'teacher' as table,
           'name'    as field,
           name as text
       from student 
    union all
    ...
)
select * from search_fields where text contains ...

You might have to add to this if you need to link back to the original table in some way.

Oracle text search across multiple tables and columns

3 Answers