rem -----------------------------------------------------------------------
rem Filename:   ctxthes.sql
rem Purpose:    Create a CTX Thesaurus to create work synonyms
rem Author:     Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

set serveroutput on

declare
  ths_exists number := 0;
begin
  -- Create a DEFAULT thesaurus if it doesn't already exists
  select 1 into ths_exists from CTX_USER_THESAURI where THS_NAME = 'DEFAULT';
  if ths_exists = 0 then
     ctx_thes.create_thesaurus('DEFAULT', FALSE);
  end if;

  -- Relate COLOR and COLOUR...
  ctx_thes.create_relation('DEFAULT','colour','SYN','color'); 
  -- Relate ORGANISE with ORGANIZE...
  ctx_thes.create_relation('DEFAULT','organise','SYN','organize'); 
end;
/

-- Create a table and populate with values...
drop table emp_cv;
create table emp_cv (id number, cv clob);
insert into emp_cv values (1, 'The color of the sky is blue');
insert into emp_cv values (1, 'The colour of the sky is blue');
commit;

-- Create a CTX index...
create index cv_idx on emp_cv(cv) indextype is ctxsys.context;

-- Query the table via the text index...
select * from emp_cv where contains(cv, 'SYN(colour)') > 0
/