rem -----------------------------------------------------------------------
rem Filename:   ctxindex.sql
rem Purpose:    Create a demo table and context index. Show how to 
rem             re-sync the index anfer inserts (any DML)
rem Author:     Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

connect scott/tiger

exec CTX_OUTPUT.START_LOG('/tmp/frank');  

-- 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, 'My CV, Frank Naude');
commit;

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

-- Insert more values into table...
insert into emp_cv values (2, 'Second CV, Larry Ellison');
commit;

-- Note the last insert is NOT visible...
select id from emp_cv where contains(cv, 'Larry') > 0;

-- Now, sync the index...
alter index cv_ind rebuild parameters ('SYNC');
select * from ctx_user_index_errors;

-- Note the last insert is now visible...
select id from emp_cv where contains(cv, 'Larry') > 0;