Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Ahmed.

Asked: December 12, 2016 - 5:39 pm UTC

Last updated: December 14, 2016 - 10:10 am UTC

Version: 10g

Viewed 10K+ times! This question is

You Asked

Hi
Please I want your help
In database 10g
I want to search for a value (number or char) I don't know it's stored in any table of database
Like search for 'king' the result be table_name=employees , column_name=emp_id
Thanks for your efforts

and Chris said...

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

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here