Oracle bietet mit der Oracle Text Option eine kostenlose und in allen Editionen enthaltene Möglichkeit der Volltextsuche. Eine kleine Einführung dazu gab es schon in unserem Monatstipp vom Februar 2010. Carsten Czarski und Ulrike Schwinn von Oracle haben zu diesem Thema einen sehr informativen Blog eingerichtet.

CTX_DOC.SNIPPET

Da man mit Oracle Text auch größere PDF- oder Word-Dateien indizieren kann, wäre es schön, wenn - wie bei den üblichen Suchmaschinen - alle Fundstellen des gesuchten Begriffs in ihrem Kontext zu sehen wären. Eigentlich gibt es dafür seit Version 10.2 die Funktion CTX_DOC.SNIPPET, die genau dieses Keyword-in-Context (KWIC) Feature bietet. 

Die grundsätzliche Abfragesyntax ist angenehm einfach:

SELECT ctx_doc.snippet('index_name', rowid, 'Suchstring')
FROM tabelle WHERE CONTAINS (indexspalte, 'Suchstring') > 0;
=>
ein paar Zeichen davor <b>Suchstring</b> ein paar Zeichen danach

Was will man mehr? Wie so oft im Leben gibt es aber ein paar Haken an der Sache.
Carstens Beispieltabelle hat keinen Primärschlüssel. Wenn man diesen vor Erstellung des Indexes anlegt und die Snippet-Abfrage laufen läßt, bekommt man folgende Fehlermeldung:

ORA-20000: Oracle Text-Fehler:
DRG-50857: oracle error in drvdoc.get_rowid
ORA-01722: Ungültige Zahl
ORA-06512: in "CTXSYS.DRUE", Zeile 160

Ein Blick in die Doku zeigt, welche Parameter man der Funktion übergeben kann:

CTX_DOC.SNIPPET(
  index_name          IN VARCHAR2,
  textkey             IN VARCHAR2 | CLOB, -- unique identifier (id.R. der primary key)
  text_query          IN VARCHAR2,        -- Suchstring
  starttag            IN VARCHAR2 DEFAULT '<b>',
  endtag              IN VARCHAR2 DEFAULT '</b>',
  entity_translation  IN BOOLEAN  DEFAULT TRUE,
  separator           IN VARCHAR2 DEFAULT '<b>...</b>')
RETURN VARCHAR2;

Schuld an der Fehlermeldung war der Parameter textkey. Diesen kann man übergeben als

• Primärschlüsselwert (Hochkommas nicht vergessen!)
  Wenn der PK aus mehreren Spalten besteht, muss man die einzelnen Werte als kommagetrennte Liste übergeben und die Prozedur CTX_DOC.PKENCODE verwenden, also z. B.  CTX_DOC.PKENCODE('4711', 'Lukas', 'Maier')

• Rowid

Mit der Prozedur CTX_DOC.SET_KEY_TYPE kann man zwischen der Benutzung von ROWID und Primärschlüssel umschalten.

exec CTX_DOC.SET_KEY_TYPE('ROWID') bzw. exec CTX_DOC.SET_KEY_TYPE('PRIMARY_KEY')

Nach dem Umschalten zur ROWID funktioniert die Abfrage in der oben beschriebenen Form auch bei Tabellen mit Primärschlüssel.

TEST VON CTX_DOC.SNIPPET MIT GRÖSSEREN PDFS

Jetzt testen wir mal die Snippet-Funktion an 3 etwas größeren PDFs (zwischen 1 und 8 MB), die sich mit der Chemie und Physik beim Kochen beschäftigen (mal was anderes). Zunächst brauchen wir eine Tabelle und eine Prozedur zum Hochladen der PDFs. Die Originaldaten werden über BFILEs in die DB geladen.
Alternativ könnten wir übrigens für den Textindex auch einen sog. file_datastore einrichten, bei dem nur die Pfade innerhalb der Datenbank gespeichert werden, die Dateien selber aber im Betriebssystem gespeichert werden.

Scott braucht Lese- und Schreibrechte an dem Verzeichnis, in dem die PDFs gespeichert sind und für die Benutzung der Oracle Text-Packages entweder die Rolle CTXAPP oder die EXECUTE-Rechte and CTX_DDL. Die Rechte am Packages CTX_DOC sind an Public gegrantet.

conn sys/sys as sysdba
CREATE OR REPLACE DIRECTORY extern AS 'c:\temp';
GRANT READ ON DIRECTORY extern TO scott;
GRANT EXECUTE ON CTX_DDL TO scott;

conn scott/tiger
DROP TABLE docs PURGE;
CREATE TABLE docs(
id         NUMBER PRIMARY KEY,
dateiname  VARCHAR2(100),
datei      BLOB);

CREATE SEQUENCE doc_seq;
CREATE OR REPLACE PROCEDURE prc_load_blob (
      p_dir      VARCHAR2 ,
      p_filename VARCHAR2)
IS
  l_bfile  BFILE;
  l_blob   BLOB;
  l_file   VARCHAR2(200);
BEGIN
  INSERT INTO docs(id, dateiname, datei)
  VALUES (doc_seq.NEXTVAL, p_filename, empty_blob())
  RETURNING datei INTO l_blob;
  l_bfile := bfileName(UPPER(p_dir), p_filename);
       DBMS_LOB.fileOpen(l_bfile);
       DBMS_LOB.loadFromFile(
           dest_lob => l_blob,  -- Ziel
            src_lob => l_bfile, -- Quelle
             amount => DBMS_LOB.getLength(l_bfile));
     DBMS_OUTPUT.PUT_LINE('Grösse des Clobs: '||TO_CHAR
             (DBMS_LOB.getLength(l_blob), '99g999g999d99'));
     DBMS_LOB.fileClose(l_bfile);
     COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_LOB.fileCloseALL;
    RAISE;
END;
/
exec prc_load_blob('extern', 'Die Geheimnisse des Kochens.pdf')
Grösse des Clobs:   1.057.898,00
exec prc_load_blob('extern', 'Von der Kochkunst zur Lebensmittelchemie.pdf')
Grösse des Clobs:   8.203.918,00
exec prc_load_blob('extern', 'kulinarische Physik.pdf')
Grösse des Clobs:   2.872.450,00

Für den Index basteln wir uns einen Lexer, der die Suche unabhängig von Groß- und Kleinschreibung macht und bestimmte Zeichen als Wortbestandteile wertet. Zusätzlich kann man natürlich die sog. stoplist optimieren, verhindern, dass Zahlen und Wörter mit weniger als 3 Zeichen indiziert werden und vieles mehr.  

exec CTX_DDL.DROP_PREFERENCE('SCOTT_LEXER')
BEGIN
  CTX_DDL.CREATE_PREFERENCE(
     preference_name => 'SCOTT_LEXER',
     object_name     => 'BASIC_LEXER');
  CTX_DDL.SET_ATTRIBUTE('SCOTT_LEXER',
      attribute_name  => 'MIXED_CASE',
      attribute_value => 'NO');
  CTX_DDL.SET_ATTRIBUTE('SCOTT_LEXER',
      attribute_name  => 'COMPOSITE',
      attribute_value => 'GERMAN');
  CTX_DDL.SET_ATTRIBUTE('SCOTT_LEXER',
      attribute_name  => 'PRINTJOINS',
      attribute_value => '-_');
END;

Da die PDFs in der Tabelle nicht verändert werden und auch nicht alle 2 Sekunden neue dazukommen, ist eine Synchronisation des Indexes nach jedem COMMIT angemessen:

DROP INDEX doc_idx;
CREATE INDEX doc_idx ON docs(datei) INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS ('LEXER scott_lexer SYNC(ON COMMIT)');

Und jetzt suchen wir mal nach Maillard (die Maillard-Reaktion ist für das typische Aroma und die Färbung beim Braten, Backen und Rösten von eiweißhaltigen Lebensmitteln verantwortlich).
SCORE gibt übrigens nicht die Anzahl der Fundstellen wieder, sondern deren Relevanz als Zahl zwischen 1 und 100. Wenn ein Suchstring in einem bestimmten Dokument sehr häufig, in allen Dokumenten aber selten vorkommt, bekommt er einen hohen SCORE-Wert. Häufiges Vorkommen in allen Dokumenten wird als Rauschen gewertet.

SELECT id, CTX_DOC.SNIPPET('DOC_IDX', rowid, 'Maillard%') snippet, score(0) score
FROM docs
WHERE CONTAINS (datei, 'Maillard%',0) > 0;
=>
    ID SNIPPET                                            SCOR
------ -------------------------------------------------------
 19167 und im Fleisch über 600 Aromastoffe. Die <b>Mailla    2
       rdReaktion</b> beschleunigt sich mit steigender Te
       mperatur<b>...</b>angebraten (die <b>Maillard-Reak
       tion</b> wirdinGang
 19168 um bereits eine Bräunungsreaktion (eine <b>Maillar     
       d-Reaktion</b>, siehe dazu die Erläuterung zu Vers
       uch 68
 19169 Druckkochtopfes Herdplatte, Backrohr                  3
        140 °C Die <b>Maillard-Reaktion</b> setzt ein Her

Die mehrfachen Zeilenumbrüche des Originalergebnisses wurden entfernt.
Das gesuchte Wort kommt im ersten und im dritten Dokument wesentlich häufiger vor. Warum gibt es hier nicht mehr Ergebnisse ? Dazu wieder ein Zitat aus der Doku:
CTX_DOC.SNIPPET returns one or more most relevant fragments for a document that contains the query term. Because CTX_DOC.SNIPPET returns surrounding text, you can immediately evaluate how useful the returned term is.
Für die Version 12c war geplant, diese Einschränkung von CTX_DOC.SNIPPET zu beheben, aber so schnell wird wohl keiner umsteigen.

EIN EIGENER ANSATZ MIT HTML-AUSGABE

Dann schreiben wir uns halt eine eigene Snippet-Funktion, bei der man dann z. B. auch die Anzahl der Zeichen vor und hinter der Fundstelle bestimmen kann. Viele Wege führen nach Rom, aber der naheliegendste Ansatz führt über die die Prozedur CTX_DOC.MARKUP, die aus jedem Dokument einen CLOB - per Default - im HTML-Format macht, in dem die Suchtreffer per Default mit <<< und >>> hervorgehoben sind. Die Syntax hierzu:

 CTX_DOC.MARKUP(
 index_name  IN VARCHAR2,
 textkey     IN VARCHAR2,
 text_query  IN VARCHAR2,
 restab      IN OUT NOCOPY CLOB,
 plaintext   IN BOOLEAN   DEFAULT FALSE,
 tagset      IN VARCHAR2  DEFAULT 'TEXT_DEFAULT',
 starttag    IN VARCHAR2  DEFAULT NULL,
 endtag      IN VARCHAR2  DEFAULT NULL,
 prevtag     IN VARCHAR2  DEFAULT NULL,
 nexttag     IN VARCHAR2  DEFAULT NULL);

Um die markierten Treffer herum soll mit einem einstellbaren Offset der Text ausgegeben werden. Die Fundstellen hätte ich gerne in rot. Alles andere belasse ich erstmal auf den Defaullt-Werten. Benutzer der Version 10g müssen statt REGEXP_COUNT Konstruktionen à la (LENGTH(l_clob) - LENGTH(REPLACE(l_clob, '<<<', '<')))/2  (oder so ähnlich) einsetzen.

CREATE OR REPLACE FUNCTION fnc_get_snippets
     (p_index_name IN VARCHAR2,
      p_rowid    IN VARCHAR2,
      p_text_query IN VARCHAR2,
      p_offset     IN NUMBER DEFAULT 50,
      p_delim      IN VARCHAR2 DEFAULT '<br/>',
      p_starttag   IN VARCHAR2 DEFAULT '<i><font color=red>',
      p_endtag     IN VARCHAR2 DEFAULT '</font></i>')   RETURN CLOB
 AS
   l_clob      CLOB;
   l_match     VARCHAR2 (32767);
   l_result    CLOB;
   l_start     PLS_INTEGER;
   l_end       PLS_INTEGER;
   l_size      PLS_INTEGER := DBMS_LOB.GETLENGTH(l_clob);
BEGIN
  CTX_DOC.SET_KEY_TYPE('ROWID');
  CTX_DOC.MARKUP (p_index_name, p_rowid, p_text_query, l_clob);
   FOR i IN 1.. REGEXP_COUNT(l_clob, '<<<') LOOP
      -- Wir setzen uns auf den gewählten Offset vor der 1. Markierung
      -- bzw. auf das 1. Zeichen, falls der Offset vor dem Anfang
      -- des Dokuments liegt
      l_start := CASE WHEN INSTR(l_clob, '<<<', 1, i) - p_offset <= 0
                  THEN 1
                  ELSE INSTR(l_clob, '<<<', 1, i) - p_offset
                 END;
      -- Wenn der letzte Offset die Länge des CLOBs überschreitet,
      -- geben wir das Snippet nur bis zum ende des Lobs aus
      l_end  := CASE
                   WHEN INSTR(l_clob, '>>>', 1, i) + p_offset > l_size
                   THEN l_size
                   ELSE INSTR(l_clob, '>>>', 1, i) + p_offset
                 END;
      -- die Fundstelle mit Offsets wird in l_match gespeichert
      l_match  := SUBSTR(l_clob, l_start, l_end - l_start)|| p_delim;
      -- und in den CLOB geschrieben
      l_result := l_result||l_match;
   END LOOP;
   l_result := REPLACE(REPLACE(l_result, '<<<', p_starttag), '>>>', p_endtag);
  RETURN l_result;
END fnc_get_snippets;
/

Test mit:

SELECT id, fnc_get_snippets('doc_idx', rowid, 'Maillard%') FROM docs;

   ID SNIPPET
----- ----------------------------------------------------------------------
19167 twa 1000 und im Fleisch über 600 Aromastoffe. Die <i><font color=red>M
      aillardReaktion</font></i> beschleunigt sich mit  steigender Temperatu
      r u<br/>erleihen, wird es au§en ebenfalls angebraten (die <i><font col
      or=red>Maillard-Reaktion</font></i> wirdinGang gesetzt),dannaber mit w
      ürziger Flüs<br/>/p>
      <p><font size="3" face="WDRMinion"><b><i>Die <i><font color=red>Mailla
      rd-Reaktion</font></i></i></b></font></p>
      <p><font size="1" face="Me<br/>em Entdecker, dem französischen Biochem
      iker Louis <i><font color=red>Maillard</font></i>: Die sogenannte <i><
      font color=red>Maillard-Reaktion</font></i>. Währe<br/> Biochemiker Lo
      uis <i><font color=red>Maillard</font></i>: Die sogenannte <i><font co
...

19168 icht aus, um bereits eine Bräunungsreaktion (eine <i><font color=red>M
      aillard-Reaktion</font></i>, siehe dazu die Erläuterung zu Versuch 68
      in A<br/>

19169  size="2" face="BookmanOldStyle"> 140 °C Die <i><font color=red>M
      aillard-Reaktion</font></i> setzt ein  Herdplatte, Backrohr,  Griller
      </fo<br/> und bilden das feste Gerüst der Krume. Durch die <i><font co
      lor=red>MaillardReaktion</font></i> (Erklärung siehe Seite 44) und div
      erser Karame<br/> man im Hochtemperaturbereich, das bedeutet, die  <i>
      <font color=red>Maillard-Reaktion</font></i> setzt ein. Durch die <i><
...

Die Ergebnisse werden jetzt vollständig aufgeführt. Was stört, sind die HTML-Formatierungen.

VERBESSERTE DO-IT-YOURSELF-FUNKTION MIT TEXT-AUSGABE

Die CTX_DOC.MARKUP-Prozedur bietet aber auch die Möglichkeit, reinen Text auszugeben, also schreiben wir die Funktion um und passen die Ausgabe ein bisschen an, damit die vielen Leerzeilen, die auch bei CTX_DOC.SNIPPET auftauchen, eliminiert werden.

CREATE OR REPLACE FUNCTION fnc_get_snippets
     (p_index_name IN VARCHAR2,
      p_rowid      IN VARCHAR2,
      p_text_query IN VARCHAR2,
      p_offset     IN NUMBER   DEFAULT 50,
      p_delim      IN VARCHAR2 DEFAULT CHR(10)||CHR(13),
      p_starttag   IN VARCHAR2 DEFAULT '<b>',
      p_endtag     IN VARCHAR2 DEFAULT '</b>')   RETURN CLOB
 AS
   l_clob      CLOB;
   l_match     VARCHAR2 (32767);
   l_result    CLOB;
   l_start     PLS_INTEGER;
   l_end       PLS_INTEGER;
   l_size      PLS_INTEGER := DBMS_LOB.GETLENGTH(l_clob);
BEGIN
  CTX_DOC.SET_KEY_TYPE('ROWID');
  CTX_DOC.MARKUP (p_index_name, p_rowid, p_text_query, l_clob, plaintext => true);
   FOR i IN 1.. REGEXP_COUNT(l_clob, '<<<') LOOP
      l_start := CASE WHEN INSTR(l_clob, '<<<', 1, i) - p_offset <= 0
                  THEN 1
                  ELSE INSTR(l_clob, '<<<', 1, i) - p_offset
                 END;
      l_end  := CASE
                   WHEN INSTR(l_clob, '>>>', 1, i) + p_offset > l_size
                   THEN l_size
                   ELSE INSTR(l_clob, '>>>', 1, i) + p_offset
                 END;
      l_match  := SUBSTR(l_clob, l_start, l_end - l_start)|| '.........';
      l_result := l_result||l_match;
   END LOOP;
   l_result := REPLACE(REPLACE(l_result, '<<<', p_starttag), '>>>', p_endtag);
   -- mehrere Leerzeichen ersetzen
   l_result := REPLACE(l_result, CHR(10)||CHR(10)||CHR(10)||CHR(10));
   l_result := REPLACE(l_result, '.........', p_delim);
  RETURN l_result;
END fnc_get_snippets;

Test:

SELECT id, fnc_get_snippets('doc_idx', rowid, 'Maillard%') FROM docs;

    ID SNIPPET
------ ----------------------------------------------------------------------
 19167 twa 1000 und im Fleisch über 600 Aromastoffe. Die <b>MaillardReaktion<
       /b> beschleunigt sich mit steigender Temperatur un
        erleihen, wird es au§en ebenfalls angebraten (die <b>Maillard-Reaktio
       n</b> wirdinGang gesetzt),dannaber mit würziger Flüs
        denn es entstehen viele neue Aromastoffe. Die <b>Maillard-Reaktion</b
       >Weshalb wird die Kruste des Brotes beim Bac
        em Entdecker, dem französischen Biochemiker Louis <b>Maillard</b>: Di
       e sogenannte <b>Maillard-Reaktion</b>. Währe...
 19168 icht aus, um bereits eine Bräunungsreaktion (eine <b>Maillard-Reaktion
       </b>, siehe dazu die Erläuterung zu Versuch 68 in A
 19169 ckkochtopfes Herdplatte, Backrohr  140 °C Die <b>Maillard-Reaktion</b>
        setzt ein Herdplatte, Backrohr, Griller
         und bilden das feste Gerüst der Krume. Durch die <b>MaillardReaktion
       </b> (Erklärung siehe Seite 44) und diverser Karame
        t man im Hochtemperaturbereich, das bedeutet, die <b>Maillard-Reaktio...

Die merkwürdige Formatierung der PDFs könnte man natürlich bei der Gelegenheit auch noch bereinigen, aber hier geht's ja nur um's Prinzip.

FAZIT:

Oracle Text macht fast alles möglich, wenn man selber bereit dazu ist ein bisschen Arbeit reinzustecken. Wenn Sie wissen wollen, wie man das Ganze optimieren und den Index schlanker gestalten kann oder einfach nur mehr über die Möglichkeiten der Volltextsuche wissen wollen, dann schauen Sie mal in unserem Sonderkurs Oracle Text vorbei.