sql - Oracle text search across multiple tables and columns -

sql - Oracle text search across multiple tables and columns -

in java web application want set "global search" text box search against multiple tables (different columns) using "oracle text"?

e.g.

table pupil {name}

table teacher {name}

table department{name, description}

table subject{name, title}

now if user come in "chem" word should searched against student:name, teacher:name, department:name, department:description, subject:name, subject:title column , sort result according oracle text score?

can advise best way accomplish this? not sure how search column across multiple tables single query using "contains" oracle text function?

also, please assume no relation between tables. in advance!

you this:

with search_fields ( select 'student' table, 'name' field, name text pupil union select 'teacher' table, 'name' field, name text pupil union ... ) select * search_fields text contains ...

you might have add together if need link original table in way.

sql oracle plsql oracle10g oracle11g