Gunther Oracle APEX und Datenbank Wiki

Diese Seite ist nicht editierbar. Sie können den Quelltext sehen, jedoch nicht verändern. Kontaktieren Sie den Administrator, wenn Sie glauben, dass hier ein Fehler vorliegt.
=====Mit Oracle APEX 5 und Oracle Text ein Dokumentenarchive für technische Dokumentation aufbauen=====
**Oracle 12c, APEX 5.0**

**Erstellt März 2016**

{{ :prog:apex:apex_katze_v01.png?direct&200 |Apex macht Katzen glücklich}}




Aufgabe: 

In Oracle APEX 5 soll ein einfaches Dokumentenarchive für die Volltext Suche über technischer Dokumentation erstellt werden.

Details:

  * Die Dokumente verbleiben auf dem Fileserver im Dateisystem
  * Der Text der binären Dokumente wie PDF, PowerPoint, Word etc. kann frei durchsucht werden
  * Für das Wandeln in Text werden die Oracle Filter und eigene PDF Filter eingesetzt
  * Die Dokumente werden über den Inhalt in in Kategorien, wie Java, Datenbank Entwicklung, Python etc. eingeordnet
  * Die Suche findet über eine APEX Suchmaske statt


Als Datenbank kommt eine Oracle 12c R1 als Standard Edition zum Einsatz, prinzipiell reicht aber auch eine Oracle XE.

Für den Volltext Index wird Oracle Text eingesetzt, mehr dazu im Detail siehe hier => [[dba:oracle_text|Oracle Text - Volltext Suche über Text Dokumente]]



=== Vortrag ===


Die Ergebnisse aus diesen kleinen Projekt werden auf der APEX Connect 2016 vorgestellt.

**<fc #ff0000>DOAG</fc> - [[https://apex.doag.org/de/| Apex Connect 2016 Konferenz- April 2016]] **\\
26. bis 28. April.2016 in Berlin - Die größte Entwicklerkonferenz rund um Oracle PL/SQL und Oracle APEX in Deutschland 

Mein Thema: <fc #008080>Oracle Text</fc>  - Übersicht - Neue Möglichkeiten unter 12c -   Integration in Apex  - Indizieren von binären Daten


=== Ablauf ===

  * Datenmodell für die Dokumenttabelle und die Pflege eines eigenen Thesaurus
  * Ladeskript für die Dokumente in Python erstellen und Daten laden
  * Filter Script für eigenen PDF Filter erstellen
  * APEX Pflege Oberfläche für den Thesaurus
  * Thesaurus pflegen und für Oracle Text erstellen
  * Theme Index Information in der Datenbank hinterlegen
  * Oracle Text Index konfigurieren
  * Oracle Text Index auf den Dokumenten anlegen
  * APEX Suchmaske für die Dokumente erstellen




----


==== Datenmodell ====

Die Dokumente werden als BFILE Referenz gespeichert.


Beim Laden werde soviel Metadaten wie möglich gleich mitgespeichert.


Dokumente - Tabelle DOCUMENTS

{{ :prog:apex:document_table_apex_archive_v01.png?direct | Dokumententabelle für ein APEX Dokumenten Archiv}}

<code sql Documents.sql>
---------------------------------------------------------
-- Document table
---------------------------------------------------------

CREATE TABLE Documents
  (
    ID                  NUMBER (30) NOT NULL ,
    Filename            VARCHAR2 (512 CHAR) NOT NULL ,
    FileTyp             VARCHAR2 (32 CHAR) ,
    FileDirectory       VARCHAR2 (2000 CHAR) NOT NULL ,
    FilePointer         BFILE   NOT NULL ,
    MD5Hash             VARCHAR2 (32 CHAR) NOT NULL ,
    FileCreateDate      DATE ,
    FileLastModify      DATE ,
    Language            VARCHAR2 (32 CHAR) ,
    CreateDate          DATE default sysdate,
    CreateUser          VARCHAR2 (32 CHAR) default user,
    ChangeDate          DATE default sysdate,
    ChangeUser          VARCHAR2 (32 CHAR) default user,
    Theme_data_avaiable VARCHAR2 (1 CHAR)  DEFAULT 'N'
  ) ;

CREATE UNIQUE INDEX IDX_Documents_ID_PK ON Documents (  ID ASC );

ALTER TABLE Documents ADD CONSTRAINT Document_PK PRIMARY KEY ( ID ) ;

COMMENT ON TABLE Documents IS   'Store the file information' ;
COMMENT ON COLUMN Documents.ID IS  'Primary Key' ;
COMMENT ON COLUMN Documents.Filename IS  'Name of the file on disk' ;
COMMENT ON COLUMN Documents.FileTyp IS  'Typ of the file' ;
COMMENT ON COLUMN Documents.FileDirectory IS  'Directory of the file' ;
COMMENT ON COLUMN Documents.FilePointer IS  'Bfile Pointer to the File' ;
COMMENT ON COLUMN Documents.MD5Hash IS  'Hash of the files to indentify dublicate files' ;
COMMENT ON COLUMN Documents.FileCreateDate IS  'File Create Time from the file' ;
COMMENT ON COLUMN Documents.FileLastModify IS  'Last modificatoin date from the file' ;
COMMENT ON COLUMN Documents.Language IS  'Language of the file (Oracle NLS Format String!)' ;
COMMENT ON COLUMN Documents.CreateDate IS  'Date when the record was created' ;
COMMENT ON COLUMN Documents.CreateUser IS  'User create the record' ;
COMMENT ON COLUMN Documents.ChangeDate IS  'Last Change on the record' ;
COMMENT ON COLUMN Documents.ChangeUser IS  'User change the record' ;
COMMENT ON COLUMN Documents.Theme_data_avaiable IS  'If Themdata is there => Y, if not N' ;

---------------------------------------------------------

create sequence documents_seq;

</code>

Thesaurus - Tabelle THESAURUS

<code sql thesaurus.sql>

---------------------------------------------------------
-- thesaurus tables
---------------------------------------------------------
drop table thesaurus cascade constraint
/
create table thesaurus(
 id		number(11) not null
,name		varchar2(255) not null
,description 	varchar2(2000)
)
/
--pk
create unique index idx_thesaurus_id_pk on thesaurus ( id asc );
alter table thesaurus add constraint thesaurus_pk primary key ( id ) ;
--uk
create unique index idx_thesaurus_name_uk on thesaurus (name);
--comment
comment on table thesaurus is 'the name and the meaning of the thesaurus' ;
comment on column thesaurus.id is 'primary key' ;
comment on column thesaurus.name is 'name of the thesaurus' ;
comment on column thesaurus.description is 'description of the thesaurus' ;

---------------------
drop table thesaurus_phrases  cascade constraint
/

create table thesaurus_phrases(
 id		number(11) not null
,ths_id 	number(11) not null
,phrase 	varchar2(255) not null
)
/
--pk
create unique index idx_thesaurus_phrases_id_pk on thesaurus_phrases ( id asc );
alter table thesaurus_phrases add constraint thesaurus_phrases_pk primary key ( id ) ;
--uk
create unique index idx_thesaurus_phrases_uk on thesaurus_phrases (phrase);
--comment
comment on table thesaurus_phrases is 'all phrases of this thesaurus';
comment on column thesaurus_phrases.id is 'primary key' ;
comment on column thesaurus_phrases.ths_id is 'fk to the thesaurus name' ;
comment on column thesaurus_phrases.phrase is 'the phrase' ;
---------------------
drop table thesaurus_relations cascade constraint
/

create table thesaurus_relations(
id 		number(11) not null
,ths_id 	number(11)
,thp_phrase 	number(11) not null
,relation 	varchar2(3) not null
,thp_rel_phrase number(11) not null
)
/
create unique index idx_thesaurus_relations_pk on thesaurus_relations ( id asc );
alter table thesaurus_relations add constraint thesaurus_relations_pk primary key ( id ) ;

--uk
create unique index idx_thesaurus_relations_uk on thesaurus_relations (ths_id,thp_phrase,relation,thp_rel_phrase);

--comment
comment on table thesaurus_relations is 'all relations of this thesaurus';
comment on column thesaurus_relations.id is 'primary key' ;
comment on column thesaurus_relations.ths_id is 'fk to the thesaurus name' ;
comment on column thesaurus_relations.relation is 'the relation between the phrases' ;
comment on column thesaurus_relations.thp_phrase is 'from this phrase' ;
comment on column thesaurus_relations.thp_rel_phrase is 'to this phrase' ;
---------------------
-- fk
---------------------
alter table thesaurus_relations add constraint thesaurus_relations_t_fk foreign key (ths_id) references thesaurus (id);
alter table thesaurus_relations add constraint thesaurus_relations_p1_fk foreign key (thp_phrase) references thesaurus_phrases (id);
alter table thesaurus_relations add constraint thesaurus_relations_p2_fk foreign key (thp_rel_phrase) references thesaurus_phrases (id);
-------
alter table thesaurus_phrases add constraint thesaurus_phrases_t_fk foreign key (ths_id) references thesaurus (id);

---------------------
-- Sequence
---------------------
drop sequence thesaurus_seq;
drop sequence thesaurus_phrases_seq;
drop sequence thesaurus_relations_seq;

create sequence thesaurus_seq;
create sequence thesaurus_phrases_seq;
create sequence thesaurus_relations_seq;

</code>

Über diese Tabellenstruktur lassen sich mehrere Thesaurus aufbauen und abbilden.

Aus den Daten in den Tabellen wird dann mit dem PL/SQL Package CTX_THES der eigentliche Thesaurus aufgebaut.

----


==== Ladeskript für die Dokumente in Python erstellen und Daten laden ====

Das Lade Script wird in Python erstellt.



=== Vorbereitung -Directory Objekt anlegen und Rechte vergeben ===
Über ein Directory Object wird der Einstieg in die Verzeichnisstruktur auf der Festplatte definiert.

In unseren Test Fall liegen die Daten unter "d:\data\info-archiv" in einer Verzeichnisstruktur.

Anlegen als SYS und Rechte vergeben:
<code sql>
sqlplus / as sysdba
#Directory anlegen
create directory INFO_ARCHIVE as 'D:\data\info-archiv';
#Rechte vergeben
grant read,write on directory INFO_ARCHIVE to GPI;
</code>

=== Testdaten manuell einfügen ===
Um den prinzipiellen Aufbau zu testen, können natürlich am Anfang ein paar Dokument zum Testen dort abgelegt werden.

Dazu einige auf Platte existierende Dokument unter dem Directory "INFO_ARCHIVE" angeben.
Hash und weitere Werte füllen wir später richtig mit der Laderoutine, hier geht es erstmal nur darum etwas testen zu können!

Um den Bfile Pointer zu setzen die Methode BFILENAME verwenden:
<code sql>
INSERT INTO documents (ID,filename,filetyp,FileDirectory,FilePointer,MD5Hash ) 
       VALUES  (documents_seq.nextval,'apex41-new-features-487382.ppt','PPT','apex',BFILENAME('INFO_ARCHIVE', 'apex\apex41-new-features-487382.ppt'),'HASH1');
INSERT INTO documents (ID,filename,filetyp,FileDirectory,FilePointer,MD5Hash ) 
        VALUES  (documents_seq.nextval,'Oracle-Dojo10-web.pdf','PDF','.', BFILENAME('INFO_ARCHIVE', 'Oracle-Dojo10-web.pdf'),'HASH4');
commit;

</code>

=== Lade Skript erstellen ===

Die Detail zum Lade Script siehe dazu hier: [[prog:python_data_loader_oracle_bfile_text_index|Mit Python 3.4 Referenzen alle Dateien in einer Verzeichnisstruktur in der Oracle Datenbank speichern]]


----

====Filter Script für eigenen PDF Filter von "Foxit PDF IFilter" erstellen====

Über ein Script wird der Inhalt einer Datei in reinen Text bzw. HTML Text gewandelt. Auf diesen Text basiert dann auch die Volltext Suche über die binären Dokumente wie PDF,PowerPoint oder Word.

Siehe dazu auch => [[dba:oracle_text_index_binary_data|Oracle Text für die Indizierung binärer Daten verwenden]]



Für die PDF Dateien wird auf den Filter von https://www.foxitsoftware.com  gesetzt.

Download Foxit PDF IFilter (30 Tage Trial für den ersten Test von https://www.foxitsoftware.com/downloads/#Foxit-PDF-IFilter-Server - Angabe der Kontaktdaten notwendig, Datei ca 9MB groß.


=== Installation Foxit PDF IFilter  ===

Testsystem ist eine Windows 10 Workstation

  * Für die Installation Datei FoxitPDFIFilter311_Server_x64_enu.msi starten
  * Dialoge nacheinander bestätigen, alles Default belassen, keine Besonderen Einstellung
  * Am Ende wird dann noch nach der Lizenz gefragt, nach 30 Tage läuft dann das ab


Test ob der Filter auch registriert ist:

{{ :prog:apex:check_indexer_settings_for_pdf.png?400 | Foxit PDF IFilter Test}}
=== Microsoft Windows Search per Script ansprechen ===

Der PDF Filter ist für das Microsoft [[https://en.wikipedia.org/wiki/IFilter|"IFilter"]] Interface ausgelegt und muss normalerweise darüber auch angesprochen werden.


Um das in einem Script zu verwenden kann dazu das Werkzeug **[[https://msdn.microsoft.com/en-us/library/windows/desktop/dd940434%28v=vs.85%29.aspx|filtdump.exe]]** von Microsoft verwandt werden. 
Das Programm dient eigentlich dazu die Filter zu testen.


Das Programm ist unter anderen in der "Microsoft Windows Search 3.x SDK" enthalten und kann über https://www.microsoft.com/en-us/download/details.aspx?id=7388 geladen werden.


Datei herunterladen und entpacken, z.b. nach D:\tools\WindowsSearchSDK3x.

Erster Test führt aber zum Fehler **<fc #800000>Error 0x80004005 loading IFilter</fc>**:
<code powershell>
cd D:\tools\WindowsSearchSDK3x\Indexing\Filtdump

.\FiltDump.exe -b D:\data\info-archiv\linux_805.pdf

FILE: D:\data\info-archiv\linux_805.pdf

Error 0x80004005 loading IFilter

</code>

Das ist dann aber nicht so recht das gewünschte Ergebnis ... ist ein 64bit Problem, Filtdump.exe ist in diesem Download nur 32Bit tauglich! 

Nach etwas Internet Recherche könnte das Programm auch in MS SDK enthalten sein. Leider ist das auch nicht so klar was und wo da nun genau das richtige ist.

Habe daher als nächstes das Windows 10 SDK über https://developer.microsoft.com/de-de/windows/downloads/windows-10-sdk heruntergeladen und installiert.

Leider ist es nicht so klar in welchen Packet dort das Programm FiltDump.exe wirklich steckt.

Mit den folgenden ausgewählten Featuren war es dann aber dabei:

{{ :prog:apex:windows_software_development_kit.png?direct&500 |Installation Windows SDK für Windows 10}}


Nächster Test:
<code powershell>
cd C:\Program Files (x86)\Windows Kits\10\bin\x64


.\FiltDump.exe -b  D:\data\info-archiv\linux_805.pdf

# viel Text wird angezeigt, aber keine echten Strukturen ....
# hmm hatte mehr erwartet

</code>

Das kann nun in eine Script eingebaut werden und damit lassen sich nun alle Filter die Microsoft auf dem System kennt verwenden!

Nun kann auch einfach überprüft werden welcher Filter mit welcher DDL für was eingesetzt wird:
<code powershell>
 .\filtreg.exe

...
.pdf --> Foxit PDF Filter (C:\Program Files\Foxit Software\Foxit PDF IFilter\PDFFilt.dll)
...
</code>


=== USER_FILTER Script für den IFilter mit FiltDump===

Der ersten Entwurf, der aber so nicht funktioniert, das hier die Endung fehlt:
<code dos>
@echo off
echo PARAM 1: %1 >> d:\temp\ctx_test.log
echo PARAM 2: %2 >> d:\temp\ctx_test.log

set FILTDUMP_HOME=C:\Program Files (x86)\Windows Kits\10\bin\x64

"%FILTDUMP_HOME%\FiltDump.exe"  -b -o %2 %1 >> d:\temp\ctx_test.log
</code>

Testweise aufrufen und pürfen ob Daten erzeugt wurden
<code powershell>
D:\oracle\products\12.1.0.2\dbhome_1\ctx\bin\readDocument.cmd D:\data\info-archiv\linux_805.pdf d:\temp\pdf_export.txt
</code>


Und schon das nächste Problem, wird der Index angelegt und damit das Script im Scope des Oracle Users aufgerufen, erhalten ich eine <fc #800000>"FILTDUMP failed, hr == 0x80070006"</fc> Fehlermeldung.


Das Problem liegt wohl daran das Windows eine Datei Endung erwartet und von Oracle aber nur eine Datei OHNE Endung erhält.


Jetzt wird es kompliziert, jetzt rächt sich die Architektur vom USER_FILTER, das hier keine weiteren Parameter übergeben werden können.

Wir müssen nun mit einem Tool wie "file" (aus der Unix Welt) erkennen um was es sich handelt. die passende Endung aus dem Mime Type ermitteln und dann diese anfügen um den passenden Filter auszuwählen.

Version 2, nun mit Erkennung des Datei Types und beiden Filteren:
<code dos>
@echo off

echo -- Start Filter at : %date% %time% >> d:\temp\ctx_test.log
echo -- Info: PARAM 0   : %0 >> d:\temp\ctx_test.log
echo -- Info: PARAM 1   : %1 >> d:\temp\ctx_test.log
echo -- Info: PARAM 2   : %2 >> d:\temp\ctx_test.log

set ORACLE_HOME=D:\oracle\products\12.1.0.2\dbhome_1
set FILTDUMP_HOME=C:\Program Files (x86)\Windows Kits\10\bin\x64
set MAGIC_HOME=D:\tools\file\bin

rem use fixed path, error get result as oracle job???
for /f %%i in ('D:\tools\file\bin\file.exe -b %1') do set FILE_TYPE=%%i

echo -- Info: File type : %FILE_TYPE% >> d:\temp\ctx_test.log

IF %FILE_TYPE% NEQ PDF goto ORACLE_FILTER

:FILTER_PDF
echo -- Info: Use Filter: iFilter  >> d:\temp\ctx_test.log
copy %1 %1.pdf 2>> d:\temp\ctx_test.log 1>nul
"%FILTDUMP_HOME%\FiltDump.exe" -b -o %2 %1.pdf >> d:\temp\ctx_test.log
del %1.pdf 2>> d:\temp\ctx_test.log 1>nul
goto END

:ORACLE_FILTER
echo -- Info: Use Filter: Oracle   >> d:\temp\ctx_test.log
%ORACLE_HOME%/bin/ctxhx.exe %1 %2 >> d:\temp\ctx_test.log

:END
echo -- End Filter at   : %date% %time% >> d:\temp\ctx_test.log
echo -- =============================  >> d:\temp\ctx_test.log
</code>

Allerdings ist das ganze nicht wirklich stabil, nach ein paar Versuchen wieder ORA-07445 Fehler erhalten!

Nach einiger Suche sind das PDF Dokument mit Password!!, die bleiben anscheinend dann wohl im Hintergrund hängen und warten auf ein Passwort!



Besser wäre natürlich eine direkte Integration in der PowerShell oder ähnlich, hier dazu ein paar Anregungen dazu:

  * http://stackoverflow.com/questions/7313828/using-ifilter-in-c-sharp-and-retrieving-file-from-database-rather-than-file-syst/19504946#19504946
  * http://www.codeproject.com/Articles/13391/Using-IFilter-in-C
  * https://msdn.microsoft.com/en-us/library/office/hh694268%28v=office.14%29.aspx
  * https://sourceforge.net/projects/pdfsharp/files/pdfsharp/




----

====APEX Pflege Oberfläche für den Thesaurus====

Für die Pflege des Thesaurus wird eine einfache Formular Maske in APEX generiert.

siehe http://docs.oracle.com/html/E39147_04/tree_query.htm

{{ :prog:apex:thesaurus_tree.png | Ein Tree in Apex um einen Thesaurus darzustellen}}

Beispiel für das SQL um den Baum aufzubauen:
<code sql>
select  case when connect_by_isleaf = 1 then 0 when level = 1 then 1 else -1 end as status
        , level
        , name as title
        , 'icon-tree-folder' as icon                    
        , id as value
        , tooltip as tooltip
        , case when item_type = 'S' then 
               apex_util.prepare_url('f?p='||:app_id||':65:'||:app_session||':T:::P_ELEMENT_ID:'||id||','||link)
		  else
		    null
          end as link 
   from (select 'P' as item_type
                , thes.name as label
                , to_char (thes.id) as id
                , null as parent
                , thes.name name
                , thes.name as tooltip
                , null link
          from thesaurus thes
          union all
          select 'T' as item_type
               , tp.phrase as label
               , to_char (tp.id) || '-' || to_char (tp.ths_id) as id
               , to_char (tp.ths_id) parent
               , tp.phrase as name
               , tp.phrase as tooltip
               , to_char (tp.id) link
           from thesaurus_phrases tp
           union all
           select 'S' item_type
                , rs.relation label
                , to_char (rs.id)||'-'||to_char (rs.thp_phrase)||'-'||to_char(rs.thp_rel_phrase)  id
                , to_char (rs.thp_phrase) || '-' || to_char (rs.ths_id) as parent
                , '-> Relation :'||rs.relation || ' to ' || tp_rel.phrase name
                , rs.relation || ' ' || tp_rel.phrase tooltip
                , to_char (rs.id) idlink
           from thesaurus_relations rs inner join thesaurus_phrases tp_rel on (rs.thp_rel_phrase = tp_rel.id)
        )
  start with parent is null
connect by prior id = parent
  order siblings by name
</code>

Der eigentliche Thesaurus wird dann über PL/SQL aus den Texteinträgen in dieser Thesaurus Tabelle erzeugt.


----

====Thesaurus pflegen und für Oracle Text erstellen, Theme Information in der Datenbank hinterlegen====

Aus den Tabelleneinträgen für unseren Thesaurus wird mit dem PL/SQL Package "CTX_THES" der eigentliche Thesaurus erstellt.

Das hätte auch über eine Text Datei stattfinden können, so ist das aber deutlich einfacher in der Pflege.



Siehe dazu auch =>  [[dba:oracle_text_thesaurus|Mit einem Thesaurus und Oracle Text arbeiten]]


Die ersten Daten des Thesaurus werden per SQL in die DB eingespielt, das Feintuning kann dann über die APEX Oberfläche erfolgen:

<code sql>

variable THS_ID number
variable THP_PHRASE number
variable THP_REL_PHRASE number


insert into THESAURUS ( id ,name  ,description,lang) 
    values (  thesaurus_seq.nextval,'T_PROD_DOCUMENT','Programming Dokumentation','EN') return id into :THS_ID;

DOC
-----------------------------------
# 
Pascal
 SYN Delphi
 RT  Borland
SQL
 RT PL/SQL
Java
 NT SCALA 
 NT Groovy
JavaScript
 NT CoffeeScript
Python
 NT Juliactx
-----------------------------------
#

------------------------- Pascal

insert into  thesaurus_phrases  (id,ths_id,phrase)	values(thesaurus_phrases_seq.nextval,:THS_ID,'Pascal') return id into :THP_PHRASE;	
insert into  thesaurus_phrases  (id,ths_id,phrase)	values(thesaurus_phrases_seq.nextval,:THS_ID,'Delphi') return id into :THP_REL_PHRASE;

insert into thesaurus_relations (id,ths_id,thp_phrase,relation,thp_rel_phrase) values(thesaurus_relations_seq.nextval,:THS_ID,:THP_PHRASE,'SYN',:THP_REL_PHRASE);

insert into  thesaurus_phrases  (id,ths_id,phrase)	values(thesaurus_phrases_seq.nextval,:THS_ID,'Borland')return id into :THP_REL_PHRASE;
insert into thesaurus_relations (id,ths_id,thp_phrase,relation,thp_rel_phrase) values(thesaurus_relations_seq.nextval,:THS_ID,:THP_PHRASE,'SYN',:THP_REL_PHRASE);

----------------------- SQL

insert into  thesaurus_phrases  (id,ths_id,phrase)	values(thesaurus_phrases_seq.nextval,:THS_ID,'SQL') return id into :THP_PHRASE;	
insert into  thesaurus_phrases  (id,ths_id,phrase)	values(thesaurus_phrases_seq.nextval,:THS_ID,'PL/SQL') return id into :THP_REL_PHRASE;

insert into thesaurus_relations (id,ths_id,thp_phrase,relation,thp_rel_phrase) values(thesaurus_relations_seq.nextval,:THS_ID,:THP_PHRASE,'RT',:THP_REL_PHRASE);

----------------------- Java

insert into  thesaurus_phrases  (id,ths_id,phrase)	values(thesaurus_phrases_seq.nextval,:THS_ID,'Java') return id into :THP_PHRASE;	
insert into  thesaurus_phrases  (id,ths_id,phrase)	values(thesaurus_phrases_seq.nextval,:THS_ID,'SCALA') return id into :THP_REL_PHRASE;

insert into thesaurus_relations (id,ths_id,thp_phrase,relation,thp_rel_phrase) values(thesaurus_relations_seq.nextval,:THS_ID,:THP_PHRASE,'NT',:THP_REL_PHRASE);

insert into  thesaurus_phrases  (id,ths_id,phrase)	values(thesaurus_phrases_seq.nextval,:THS_ID,'Groovy')return id into :THP_REL_PHRASE;
insert into thesaurus_relations (id,ths_id,thp_phrase,relation,thp_rel_phrase) values(thesaurus_relations_seq.nextval,:THS_ID,:THP_PHRASE,'NT',:THP_REL_PHRASE);

----------------------- JavaScript
insert into  thesaurus_phrases  (id,ths_id,phrase)	values(thesaurus_phrases_seq.nextval,:THS_ID,'JavaScript') return id into :THP_PHRASE;	
insert into  thesaurus_phrases  (id,ths_id,phrase)	values(thesaurus_phrases_seq.nextval,:THS_ID,'CoffeeScript') return id into :THP_REL_PHRASE;

insert into thesaurus_relations (id,ths_id,thp_phrase,relation,thp_rel_phrase) values(thesaurus_relations_seq.nextval,:THS_ID,:THP_PHRASE,'NT',:THP_REL_PHRASE);

----------------------- Python
insert into  thesaurus_phrases  (id,ths_id,phrase)	values(thesaurus_phrases_seq.nextval,:THS_ID,'Python') return id into :THP_PHRASE;	
insert into  thesaurus_phrases  (id,ths_id,phrase)	values(thesaurus_phrases_seq.nextval,:THS_ID,'Juliactx') return id into :THP_REL_PHRASE;

insert into thesaurus_relations (id,ths_id,thp_phrase,relation,thp_rel_phrase) values(thesaurus_relations_seq.nextval,:THS_ID,:THP_PHRASE,'NT',:THP_REL_PHRASE);

commit;
</code>

Um dann den eigentlichen Thesaurus aufbauen zu können, wird eine PL/SQL Procedure verwandt, hier die einfachste Variante:
<code sql>

set serverout put on

CREATE OR REPLACE procedure createthesaurus
is
	cursor c_thes
	is
		select	*
			 from thesaurus
		order by id;

	cursor c_pharse (p_ths_id number)
	is
		select	*
			 from thesaurus_phrases
			where ths_id = p_ths_id
		order by phrase;

	cursor c_relation (p_ths_id number)
	is
		select p1.phrase
			  , p2.phrase as rel_phrase
			  , r.relation
		  from thesaurus_relations r
				 inner join thesaurus_phrases p1
					 on (r.thp_phrase = p1.id)
				 inner join thesaurus_phrases p2
					 on (r.thp_rel_phrase = p2.id)
		 where r.ths_id = p_ths_id;
begin
	for trec in c_thes
	loop
		-- löschen und neu anlegen
		dbms_output.put_line ('-- Info :: drop thesaurus ::' || trec.name);

		begin
			ctx_thes.drop_thesaurus (name => trec.name);
		exception
			when others
			then
				dbms_output.put_line ('-- Info :: drop thesaurus Error ::' || sqlerrm);
		end;

		-- create the empty thesaurus case insensitiv
		dbms_output.put_line ('-- Info :: create thesaurus ::' || trec.name);
		ctx_thes.create_thesaurus (name		  => trec.name
										 , casesens   => false
										  );

		--create a Phrase to the thesaurus
		for prec in c_pharse (p_ths_id => trec.id)
		loop
			dbms_output.put_line ('-- Info :: create pharse ::' || prec.phrase);
			ctx_thes.create_phrase (tname 	=> trec.name
										 , phrase	=> prec.phrase
										  );
		end loop;

		-- define a relation to this phrases
		for rrec in c_relation (p_ths_id => trec.id)
		loop
			dbms_output.put_line (
				'-- Info :: create relation ::' || rrec.phrase || ' - ' || rrec.relation || ' - ' || rrec.rel_phrase);
			ctx_thes.create_relation (tname		  => trec.name
											, phrase 	  => rrec.phrase
											, rel 		  => rrec.relation
											, relphrase   => rrec.rel_phrase
											 );
		end loop;
	end loop;
end;
/
</code>

Nach dem Aufruf mit "exec createthesaurus" kann der Thesaurus verwandt werden.

<fc #800000>Spannende Frage: Muss der Index neu aufgebaut werden?</fc>

Wenn nur die Thesaurus  Funktion verwandt werden soll, wohl nicht, wird auf Theme gesetzt dann schon

Gerade am Testen, nur der Thesaurus kann auch ohne Neuaufbau verwendet werden, d.h. es können auch mehrere davon gleichzeitig eingesetzt werden.


----

==== Thesaurus für den Theme Index übersetzen ====

Vorhandenen Thesarus für den Theme Index übersetzen:

<code bash>
 ctxkbtc -user ctxsys/ctxsys -name T_PROD_DOCUMENT
</code>


Bzgl. Theme siehe auch [[dba:oracle_text_index_themes|Einen Oracle Theme Index aufbauen]]


----
====Oracle Text Index konfigurieren====


Unser User GPI, unter dem der Oracle Text Index aufgebaut wird, benötigt die Rolle CTXAPP.

Vor dem Aufbau einer Oracle Text Indexes können die verschiedenen Parameter für den Index Aufbau konfiguriert werden.

<code sql>

-- zuvor als sys
sqlplus / as sysdba

grant CTXAPP to GPI;
grant all on ctxsys.ctx_thes to GPI;

-- als context User die Eigenschaften einstellen
connect admin

--- LEXER
EXEC ctx_ddl.create_preference( 'gpi_lexer', 'BASIC_LEXER' );
EXEC ctx_ddl.set_attribute ('gpi_lexer', 'INDEX_THEMES', 'YES');

-- STORAGE 
-- Forward Indexing
-- Plain Text speichern
begin
ctx_ddl.create_preference
   (
    preference_name => 'GPI_BASIC_STORAGE',
    object_name     => 'BASIC_STORAGE'
    );
end;
/   
exec ctx_ddl.set_attribute('GPI_BASIC_STORAGE','forward_index','TRUE');
exec ctx_ddl.set_attribute('GPI_BASIC_STORAGE','save_copy','PLAINTEXT');
</code>



----

==== Oracle Text Index auf den Dokumenten anlegen====

Index mit entsprechenden Eigenschaften dann anlegen

<code sql>
-- create the Oracle Text Index 
-- Frist try with AUTO_FILTER

drop index idx_doc_files;

CREATE INDEX idx_doc_files ON documents(FilePointer)  
             INDEXTYPE IS CTXSYS.CONTEXT 
             PARAMETERS ('FILTER   USER_FILTER_GPI_PREF
                          LEXER    GPI_LEXER
                          STORAGE  GPI_BASIC_STORAGE
			  STOPLIST GPI_STOPLIST')
/


--Auf Fehler prüfen
SELECT * FROM ctx_user_index_errors;
 
 
--
--Index tabellen anzeigen lassen
 
SELECT TABLE_NAME FROM user_tables WHERE TABLE_NAME LIKE '%IDX_DO%';

TABLE_NAME
-------------------
DR$IDX_DOC_FILES$I
DR$IDX_DOC_FILES$R
DR$IDX_DOC_FILES$K
DR$IDX_DOC_FILES$N


--testen ob Text Tockens auch eingetragen wurden

select TOKEN_TEXT from DR$IDX_DOC_FILES$I where rownum < 10;


--prüfen ob der Theme Index auch geklappt hat
SELECT token_type FROM dr$idx_documents_docs$i GROUP BY token_type;

</code>

Nun kann getestet werden ob eine erste Suche mit Hilfe eines Thesaurus auch erfolgreich ist:

<code sql>
SELECT * FROM DOCUMENTS WHERE contains(filepointer, 'NT(Java,1,T_PROD_DOCUMENT)') >0;
</code>


----

====APEX Suchmaske für die Dokumente erstellen====

Über eine APEX Maske wird die Volltextsuche über die Dokumente gesteuert

Für die Suche über den Index wird dazu der Oracle Contains Operator (siehe [[prog:oracle_text_contains|Oracle Text - In Texten suchen]]) eingesetzt, in der Suchmaske kann der Operator aus einer Werte Liste ausgewählt werden.

In der Treffermenge soll ein Stück des gefundenen Text als Preview in jeder Trefferzeile angezeigt werden. 

Der gefundene Text wird dabei farblich hervorgehoben.

Für die Aufbereitung der Trefferliste wird das [[https://docs.oracle.com/database/121/CCREF/cdocpkg.htm#CCREF0700|CTX_DOC Package]] eingesetzt, siehe dazu [[prog:oracle_text_highlight|Oracle Text - Die Treffer in der Ergebnismenge hervorheben]]

==== Tree Ansicht über die Einträge im Thesaurus =====

Einfache Varianten nur auf Vorkommen der Suchwörter:
<code sql>

select  case when connect_by_isleaf = 1 then 0 when level = 1 then 1 else -1 end as status
        , level
        , name as title
        , 'icon-tree-folder' as icon                    
        , id as value
        , tooltip as tooltip
        , case when item_type = 'S' then 
               apex_util.prepare_url('f?p='||:app_id||':13:'||:app_session||':T:::P13_TEXT_KEY:'||id||','||link)
		  else
		    null
          end as link 
   from (select 'P' as item_type
                , thes.name as label
                , to_char (thes.id) as id
                , null as parent
                , thes.name||' Search for :: '||nvl(:P15_SEARCH_TEXT,'oracle') name
                , thes.name as tooltip
                , null link
          from thesaurus thes
          union all
          select 'T' as item_type
               , tp.phrase as label
               , to_char (tp.id) || '-' || to_char (tp.ths_id) as id
               , to_char (tp.ths_id) parent
               , tp.phrase as name
               , tp.phrase as tooltip
               , to_char (tp.id) link
           from thesaurus_phrases tp
           union all
           select 'S' item_type
                , d.filename
                , to_char (d.id)||'-'||to_char (tp.phrase)    id
                , to_char (tp.id) || '-' || to_char (tp.ths_id) as parent
                , '-> Found :'||d.filename || ' in ' || tp.phrase name
                , score(1) || '  to ' ||score(2) || tp.phrase tooltip
                , to_char (d.id) idlink
           from documents d,thesaurus_phrases tp 
          where contains(d.filepointer,tp.phrase,1)>0 
            and contains(d.filepointer,nvl(:P15_SEARCH_TEXT,'oracle'),2)>0
        )
  start with parent is null
connect by prior id = parent
  order siblings by name
</code>

Sieht dann so aus:

{{ :prog:apex:oracle_text_documenttree_v01.png?direct | Oracle Apex  Tree zur Dokumentensuche}}

----
==== Quellen ====

Siehe jeweils die Detail Artikel