Text indexes

CREATE TABLE TECH_ORACLE_TEXT (
  COGNOME      VARCHAR2(50 BYTE),
  NOME         VARCHAR2(50 BYTE),
  EMAIL        VARCHAR2(50 BYTE),
  VARCHAR4000  VARCHAR2(4000 BYTE),
  DESCR_CLOB   CLOB,
  DESCR_BLOB   BLOB,
  BLOB_TYPE    VARCHAR2(20 BYTE),
  ref_file     BFILE
);

Load the table with one row

declare
	   lobd BLOB;
	   directoryName varchar2(50) := 'WEB_DIR';
	   fileName varchar2(50) := 'index.html';
	   fils BFILE;
BEGIN
	 fils := BFILENAME(directoryName, fileName);
	 --Create a temporary blob, not in cache only for call duration 
	 dbms_lob.createtemporary(lobd, false, 2);
	 dbms_lob.open(fils, dbms_lob.lob_readonly);
	 dbms_lob.loadfromfile(lobd, fils, DBMS_LOB.GETLENGTH(fils));
	 INSERT INTO ALDO.TECH_ORACLE_TEXT (COGNOME, NOME, EMAIL, VARCHAR4000, DESCR_CLOB, DESCR_BLOB, blob_type, ref_file) 
	 VALUES ('fadalti', 'aldo', 'aldo@fadalti.com', 'uno due tre quattro', 'cinque sei sette', lobd, fileName, fils);
	 dbms_lob.close(fils);
end;

Create a context index

create index myCognome on tech_oracle_text(cognome)
indextype is ctxsys.context
parameters ('DATASTORE CTXSYS.DEFAULT_DATASTORE');

create index myDESCR_BLOB on tech_oracle_text(DESCR_BLOB)
indextype is ctxsys.context
parameters ('DATASTORE CTXSYS.DEFAULT_DATASTORE');

Select using context index

select * from tech_oracle_text
where CONTAINS(cognome, 'about(fadalti)', 1) > 0;

Please, load another row into the table as above

Perform the select again, only one row will be displayed

See for any pending dml over the index with

--!! run this query with ALDO, not CTXSYS !!
SELECT pnd_index_name, pnd_rowid, 
to_char(pnd_timestamp, 'dd-mon-yyyy hh24:mi:ss') timestamp 
FROM ctx_user_pending;

Resync all pending indexes

begin
	ctx_ddl.sync_index();
end;

Resync only few indexes

begin
	ctx_ddl.sync_index('MYDESCR_BLOB');
	ctx_ddl.sync_index('MYCOGNOME');
end;