create table test_tab1
(
  eng_id                    number(10),    
  eng_name                  varchar2(100 byte),
  cli_short_name            varchar2(200 byte),
  entity_duns_name          varchar2(100 byte),
  ult_par_duns_name         varchar2(100 byte),
  concat                    char(1) /* placeholder for user datastore */    
);

select eng_name, cli_short_name, entity_duns_name, ult_par_duns_name 
from test_tab1 
where contains ( concat,
'((exxon or pepsi or tata motors) within the_eng_name) or 
((exxon or pepsi or tata motors) within the_cli_short_name)',1 ) > 0

---------------------------------
-- CREATE MULTI-COLUMN-DATASTORE
---------------------------------
connect ctxsys/<password>
grant execute on ctxsys.ctx_ddl to myschema;
-- only ctxsys can create MULTI_COLUMN preferences in Oracle 10g
begin
  ctx_ddl.drop_preference ( 'gcp_multi_column_datastore' );
end;
/
begin
  ctx_ddl.create_preference ( 'gcp_multi_column_datastore', 'multi_column_datastore' );
  ctx_ddl.set_attribute ( 'gcp_multi_column_datastore', 'columns', 'eng_name, cli_short_name, entity_duns_name, ult_par_duns_name' );
end;

connect myschema/<password>
-------------------
-- CREATE TABLE
------------------
drop table test_tab1;
create table test_tab1
(
  eng_id                    number(10),    
  eng_name                  varchar2(100 byte),
  cli_short_name            varchar2(200 byte),
  entity_duns_name          varchar2(100 byte),
  ult_par_duns_name         varchar2(100 byte),
  concat                    char(1) /* placeholder for user datastore */    
);


TRUNCATE TABLE test_tab1 ;
insert into test_tab1 values (1,'some text with pepsi in it, bytheway it also has exxon in it','test1','test1','test1','');
insert into test_tab1 values (2,'test2','some text with pepsi in it','test2','test2','');
insert into test_tab1 values (3,'test3','test3','some text with exxon in it','test3','');
insert into test_tab1 values (4,'test4','test4','some text with exxon in it','exxon','');

commit;

-----------------------
-- CREATE SECTION GROUP
-----------------------
begin
  ctx_ddl.drop_section_group
    (
       group_name => 'test1_section_group'
    );
end;

begin
  ctx_ddl.create_section_group
    (
       group_name => 'test1_section_group',
       group_type => 'basic_section_group'
    );
  ctx_ddl.add_field_section
    (
      group_name   => 'test1_section_group',
      section_name => 'the_eng_name',
      tag          => 'eng_name',
      visible      => true
    );
  ctx_ddl.add_field_section
    (
      group_name   => 'test1_section_group',
      section_name => 'the_cli_short_name',
      tag          => 'cli_short_name',
      visible      => true
    );
  ctx_ddl.add_field_section
    (
      group_name   => 'test1_section_group',
      section_name => 'the_entity_duns_name',
      tag          => 'entity_duns_name',
      visible      => true
    );
  ctx_ddl.add_field_section
    (
      group_name   => 'test1_section_group',
      section_name => 'the_ult_par_duns_name',
      tag          => 'ult_par_duns_name',
      visible      => true
    );
end;

/


---------------
-- CREATE INDEX
---------------

create index tt_idx1 on test_tab1 ( concat )
  indextype is ctxsys.context
  parameters ( '
    datastore      ctxsys.gcp_multi_column_datastore
    section group  test1_section_group           ' );