Create a User and grant the previliges:
CREATE USER ora_text IDENTIFIED BY therap;
GRANT RESOURCE, CONNECT, CTXAPP TO ora_text;
GRANT EXECUTE ON CTXSYS.CTX_CLS TO ora_text;
GRANT EXECUTE ON CTXSYS.CTX_DDL TO ora_text;
GRANT EXECUTE ON CTXSYS.CTX_DOC TO ora_text;
GRANT EXECUTE ON CTXSYS.CTX_OUTPUT TO ora_text;
GRANT EXECUTE ON CTXSYS.CTX_QUERY TO ora_text;
GRANT EXECUTE ON CTXSYS.CTX_REPORT TO ora_text;
GRANT EXECUTE ON CTXSYS.CTX_THES TO ora_text;
GRANT EXECUTE ON CTXSYS.CTX_ULEXER TO ora_text;
Select Query using Oracle Text:
CONNECT ora_text;
CREATE TABLE docs (id NUMBER PRIMARY KEY, text VARCHAR2(200));
INSERT INTO docs VALUES(1, '<html>California is a state in the US.</html>');
INSERT INTO docs VALUES(2, '<html>Paris is a city in France.</html>');
INSERT INTO docs VALUES(3, '<html>France is in Europe.</html>');
Create the Index.
CREATE INDEX idx_docs ON docs(text)
INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS
('FILTER CTXSYS.NULL_FILTER SECTION GROUP CTXSYS.HTML_SECTION_GROUP');
Select the document.
COLUMN text FORMAT a40;
SELECT SCORE(1), id, text FROM docs WHERE CONTAINS(text, 'France', 1) > 0;
Highlight the result.
This PL/SQL example uses the in-memory version of CTX_DOC.MARKUP to highlight the word France in document 3.
set serverout on
DECLARE
mklob CLOB;
amt NUMBER := 40;
line VARCHAR2(80);
BEGIN
CTX_DOC.MARKUP('idx_docs','3','France', mklob);
DBMS_LOB.READ(mklob, amt, 1, line);
DBMS_OUTPUT.PUT_LINE('FIRST 40 CHARS ARE:'||line);
DBMS_LOB.FREETEMPORARY(mklob);
END;
/
Synchronize the Index After Data Manipulation:
INSERT INTO docs VALUES(4, '<html>Los Angeles is a city in California.</html>');
INSERT INTO docs VALUES(5, '<html>Mexico City is big.</html>');
SELECT SCORE(1), id, text FROM docs WHERE CONTAINS(text, 'city', 1) > 0;
It will show the previous result.Therefore, synchronize the index with 2Mb of memory, and rerun the query.
EXEC CTX_DDL.SYNC_INDEX('idx_docs', '2M');
SELECT SCORE(1), id, text FROM docs WHERE CONTAINS(text, 'city', 1) > 0;
No comments:
Post a Comment