If you want to enable free text search of any column, you can do so using Oracle Text. But the process is a little convoluted.
You need to create a user datastore. This requires a procedure that returns XML of the data you wish to search.
To generate this XML, you need to create a holding table. This stores links to the primary keys of the tables you want to search. You then create the text index on this holding table and search on this table.
Here's an example using the HR schema. This assumes you want to search for strings any where in
EMPLOYEES - first_name, last_name, job_id
DEPARTEMENTS - department_name
LOCATIONS - street_address, city, state_province
Text_tab stores the IDs for each of these these and the table it can from. Then, based on the rowid from text_tab in your index, it looks up the corresponding row in the real table. From this it generates XML of the columns you're indexing:
create table text_tab (
id int,
tab varchar2(30),
dummy char(1)
);
insert into text_tab
select employee_id, 'EMPLOYEES', 'X' from employees;
insert into text_tab
select department_id, 'DEPARTMENTS', 'X' from departments;
insert into text_tab
select location_id, 'LOCATIONS', 'X' from locations;
commit;
create or replace procedure user_ds (
rid in rowid,
tlob in out nocopy clob
) is
v_id int;
v_tab varchar2(30);
begin
select id, tab
into v_id, v_tab
from text_tab
where rowid = rid;
case v_tab
when 'EMPLOYEES' then
select '<first_name>' || first_name || '</first_name><last_name>' ||
last_name || '</last_name><job_id>' || job_id || '</job_id>'
into tlob
from employees e, text_tab tt
where tt.rowid = rid
and e.employee_id = tt.id;
when 'DEPARTMENTS' then
select '<department_name>' || department_name || '</department_name>'
into tlob
from departments d, text_tab tt
where tt.rowid = rid
and d.department_id = tt.id;
when 'LOCATIONS' then
select '<street_address>' || street_address || '</street_address><city>' ||
city || '</city><state_province>' || state_province || '</state_province>'
into tlob
from locations l, text_tab tt
where tt.rowid = rid
and l.location_id = tt.id;
end case;
end user_ds;
/
exec ctx_ddl.create_section_group( 'my_sg', 'auto_section_group' );
exec ctx_ddl.create_preference( 'my_uds', 'user_datastore' );
exec ctx_ddl.set_attribute( 'my_uds', 'procedure', 'user_ds' );
exec ctx_ddl.create_policy( 'my_pol', 'ctxsys.auto_filter' );
create index text_index on text_tab ( dummy )
indextype is ctxsys.context
parameters( 'datastore my_uds section group my_sg' )
/
With this in place, you can then use the contains operator to find text in any of these columns:
select e.first_name, e.last_name, e.job_id,
d.department_name, l.street_address,
l.city, l.state_province
from text_tab tt
left join employees e
on tt.id = e.employee_id
and tt.tab = 'EMPLOYEES'
left join departments d
on tt.id = d.department_id
and tt.tab = 'DEPARTMENTS'
left join locations l
on tt.id = l.location_id
and tt.tab = 'LOCATIONS'
where contains(dummy, 'King') > 0 ;
FIRST_NAME LAST_NAME JOB_ID DEPARTMENT_NAME STREET_ADDRESS CITY STATE_PROVINCE
Steven King AD_PRES
Janette King SA_REP
select e.first_name, e.last_name, e.job_id,
d.department_name, l.street_address,
l.city, l.state_province
from text_tab tt
left join employees e
on tt.id = e.employee_id
and tt.tab = 'EMPLOYEES'
left join departments d
on tt.id = d.department_id
and tt.tab = 'DEPARTMENTS'
left join locations l
on tt.id = l.location_id
and tt.tab = 'LOCATIONS'
where contains(dummy, 'O% OR V%') > 0 ;
FIRST_NAME LAST_NAME JOB_ID DEPARTMENT_NAME STREET_ADDRESS CITY STATE_PROVINCE
Neena Kochhar AD_VP
Lex De Haan AD_VP
Valli Pataballa IT_PROG
Shanta Vollman ST_MAN
TJ Olson ST_CLERK
Peter Vargas ST_CLERK
Christopher Olsen SA_REP
Oliver Tuvault SA_REP
Clara Vishney SA_REP
Lisa Ozer SA_REP
Vance Jones SH_CLERK
Donald OConnell SH_CLERK
1297 Via Cola di Rie Roma
Operations
93091 Calle della Testa Venice
147 Spadina Ave Toronto Ontario
Magdalen Centre, The Oxford Science Park Oxford Oxford
12-98 Victoria Street Sydney New South Wales
1298 Vileparle (E) Bombay Maharashtra
This is only a partial solution. It's modified from a complete solution shown at:
https://blogs.oracle.com/searchtech/entry/indexing_data_from_multiple_tables To finish it, you need triggers (or similar) to keep text_tab in sync with the tables you're searching.
You can also find further discussion (in German!) about this at:
http://oracle-text-de.blogspot.co.uk/2012/03/nochmal-userdatastore-ein-umfassendes.html