Oracle Text
-- install oracle text
  CREATE USER ot1 IDENTIFIED BY ot1; 
  GRANT connect,resource, ctxapp TO ot1;
 
-- create table
  CREATE TABLE files ( 
  id NUMBER PRIMARY KEY, 
  issue_id NUMBER, 
  path VARCHAR(255) UNIQUE, 
  ot_format VARCHAR(6) 
  ); 
 
-- insert data
  INSERT INTO files VALUES (1, 1, '/tmp/oracletext/found1.txt', NULL); 
  INSERT INTO files VALUES (2, 2, '/tmp/oracletext/found2.doc', NULL); 
  INSERT INTO files VALUES (3, 2, '/tmp/oracletext/notfound.txt', 'IGNORE');
 
-- create index
  CREATE INDEX file_index ON files(path) INDEXTYPE IS ctxsys.context 
  PARAMETERS ('datastore ctxsys.file_datastore format column ot_format');
 
-- meta data example
  CREATE TABLE issues ( 
  id NUMBER, 
  summary VARCHAR(120), 
  description CLOB, 
  author VARCHAR(80), 
  ot_version VARCHAR(10) 
  );
  INSERT INTO issues VALUES (1, 'Jane', 'Text does not make tea', 
  'Oracle Text is unable to make morning tea', 1); 
  INSERT INTO issues VALUES (2, 'John', 'It comes in the wrong color', 
  'I want to have Text in pink', 1);
 
--
 
  -- declare indexing procedure 
  CREATE PACKAGE ot_search AS 
  PROCEDURE issue_filter(rid IN ROWID, tlob IN OUT NOCOPY CLOB); 
  END ot_search; 
  / 
  -- define indexing procedure 
  CREATE PACKAGE BODY ot_search AS 
  PROCEDURE issue_filter(rid IN ROWID, tlob IN OUT NOCOPY CLOB) IS 
  BEGIN 
  FOR c1 IN (SELECT author, summary, description FROM issues WHERE rowid = rid) 
  LOOP 
  dbms_lob.writeappend(tlob, LENGTH(c1.summary)+1, c1.summary || ' '); 
  dbms_lob.writeappend(tlob, LENGTH(c1.author)+1, c1.author || ' '); 
  dbms_lob.writeappend(tlob, LENGTH(c1.description), c1.description); 
  END LOOP; 
  END issue_filter; 
  END ot_search; 
  / 
  -- define datastore preference for issues 
  BEGIN 
  ctx_ddl.create_preference('issue_store', 'user_datastore'); 
  ctx_ddl.set_attribute('issue_store', 'procedure', 'ot_search.issue_filter'); 
  ctx_ddl.set_attribute('issue_store', 'output_type', 'CLOB'); 
  END; 
  / 
  -- index issues 
  CREATE INDEX issue_index ON issues(ot_version) INDEXTYPE IS ctxsys.context 
  PARAMETERS ('datastore issue_store');
 
-- search
  SELECT id FROM issues WHERE CONTAINS(ot_version, 'color AND pink', 1) > 0; 
  SELECT id FROM issues WHERE CONTAINS(ot_version, 'jane OR john', 1) > 0;
 
--sync
  EXECUTE ctx_ddl.sync_index('issue_index', '2M');
 
  #!/bin/sh 
  export ORACLE_SID=orcl 
  export ORAENV_ASK=NO 
  source /usr/local/bin/oraenv 
  sqlplus ot1/ot1@XE > synch.log <  
  WHENEVER SQLERROR EXIT 5; 
  EXECUTE ctx_ddl.sync_index('issue_index', '2M'); 
  EOF
 
-- force update index again
  UPDATE files SET path=path WHERE id = 4;