-------------------------------------------------------------------
--Create a table with some data containing state names
-------------------------------------------------------------------
drop TABLE test_tab;
CREATE TABLE test_tab (state VARCHAR2(60) primary key);

insert into test_tab VALUES ('deep in the heart of texas');
insert into test_tab VALUES ('I heart new york');
commit;

-------------------------------------------------------------------
--Create a THESAURUS that relates the state to it's abbreviation 
-------------------------------------------------------------------
EXEC CTX_THES.drop_THESAURUS ('STATE_ABBR');
begin
  CTX_THES.CREATE_THESAURUS ('STATE_ABBR');
  CTX_THES.CREATE_RELATION ('STATE_ABBR', 'TX', 'SYN', 'TEXAS');
  CTX_THES.CREATE_RELATION ('STATE_ABBR', 'NY', 'SYN', 'NEW YORK');
end;
/

-------------------------------------------------------------------
--Create a datastore
-------------------------------------------------------------------
exec ctx_ddl.drop_preference('state_ds');
begin
  ctx_ddl.create_preference('state_ds','MULTI_COLUMN_DATASTORE'); 
  ctx_ddl.set_attribute('state_ds', 'COLUMNS', 'state'); 
end;
/

-------------------------------------------------------------------
--Create a name group section
-------------------------------------------------------------------
exec ctx_ddl.drop_section_group('state_ng');
begin
  ctx_ddl.create_section_group('state_ng', 'BASIC_SECTION_GROUP');
  ctx_ddl.add_ndata_section('state_ng', 'state', 'state');
end;
/

-------------------------------------------------------------------
--Create the word list
-------------------------------------------------------------------
exec ctx_ddl.drop_preference('state_wl');
begin
  ctx_ddl.create_preference('state_wl', 'BASIC_WORDLIST');
  ctx_ddl.set_attribute('state_wl', 'NDATA_THESAURUS', 'STATE_ABBR');
end;
/

-------------------------------------------------------------------
--Create the index
-------------------------------------------------------------------
drop INDEX test_idx;

CREATE INDEX test_idx on test_tab (state)
  INDEXTYPE IS CTXSYS.CONTEXT
  parameters('datastore state_ds wordlist state_wl section group state_ng')
;

-------------------------------------------------------------------
--Run some tests
-------------------------------------------------------------------

--This correctly finds "texas"
select score(1) SCORE, state from test_tab
where contains(state,'ndata(state,tx)',1)>0 order by score(1) desc;

--This should find "new york", but it doesn't!
select score(1) SCORE, state from test_tab
where contains(state,'ndata(state,ny)',1)>0 order by score(1) desc;