Oracle 7.3 bis Oracle 21c Windows 11 kompatibel Version (Freigegeben: )

Oracle XML DB – Implementierung einer Datenimportschnittstelle (Teil 1)

23 Jan 2023

Einführung

Hiermit begrüßen wir Sie zu einer Folge von zwei Artikeln, in denen wir zeigen, wie KeepTool 15 zusammen mit Oracle XML DB verwendet werden kann.  Dazu wird Oracles XML-Komplettlösung verwendet, die das Speichern und Laden von XML-Dokumenten aus der Datenbank erlaubt. Dazu können XML-Schemas in der Datenbank registriert und Abfragen auf Objekt-relationale Datenstrukturen durchgeführt werden.

Im ersten Blogbeitrag werden zunächst die Grundlagen erklärt. Anschließend wird Schritt für Schritt eine XML-Schnittstelle für den Datenimport in die Tabelle EMPLOYEES aus Oracles HR (Human Resources) Beispielschema entwickelt.

Im zweiten Beitrag ergänzen wir das Beispiel dann durch eine Exportschnittstelle. Dabei zeigen wir anhand der beiden Tabellen HR.DEPARTMENTS und LOCATIONS, wie eine Master/Detail-Beziehung verarbeitet werden kann.
Dabei konzentrieren wir uns auf das Wesentliche. Dabei bleibt Spielraum, die Logik weiter zu vervollständigen und an die eigenen Bedürfnisse anzupassen.

Ein Anwendungsbeispiel

Betrachten wir einen regionalen Standort eines großen Unternehmens. Lokal wird eine eigene Kopie der Personaldatenbank (HR) geführt. Die Mitarbeiterdaten der EMPLOYEES-Tabelle werden zentral verwaltet. Der lokale Standort erhält Aktualisierungen von neuen und geänderten Mitarbeiterdaten über eine XML-Schnittstelle vom Hauptstandort:

  • Die Hauptdatenbank der Zentrale exportiert neue und geänderte Mitarbeiterdaten als XML-Struktur.
    Dies geschieht in regelmäßigen Abständen, zum Beispiel nachts oder bei jeder Aktualisierung eines EMPLOYEES-Datensatzes.
    Es gibt mehrere Möglichkeiten, die XML-Struktur an den lokalen Standort zu übertragen, z. B. über einen Webservice oder über einen Remote-Datenbankzugriff.
  • Der lokale Standort empfängt die XML-Datenstruktur und importiert den Inhalt in seine eigene lokale Datenbank.
    Neue Mitarbeiterdaten werden eingefügt und bestehende Mitarbeiter werden lokal aktualisiert.

Im ersten Teil unserer Serie werden wir zeigen, wie eine XML-Struktur importiert werden kann.  EMPLOYEES-Daten in der lokalen HR-Datenbank werden entweder eingefügt oder aktualisiert. Im zweiten Blog werden wir dann die Technologie des Datenexports beschreiben.

Definition von PL/SQL Types

Die Aufgabe besteht darin, Daten in die Tabelle EMPLOYEES aus Oracle Demoschema HR zu importieren. Als ersten Schritt dazu definieren wir einige PL/SQL Types, um anschließend daraus mit Hilfe von Oracle XML-DB eine XSD-Schema-Definition zu erzeugen.
Genauer gesagt, benötigen wir 3 voneinander abhängige PL/SQL Types:

  • EMPLOYEE_XML_ITEM
    ist ein Object Type, dessen Elemente der Datenstruktur der Tabelle EMPLOYEES entsprechen.
  • EMPLOYEES_XML_LIST
    ist ein Collection Type, der mehrere Elemente des Typs EMPLOYEE_XML_ITEM aufnimmt.
  • EMPLOYEES_XML_ELEM
    ist ein weiterer Object Type, der als einziges Element die EMPLOYEES_XML_LIST enthält.
    Wir benötigen das, um den Collection Type in einen Object Type einzubetten.

Wir erzeugen die 3 Types, indem wir das folgende Skript auf Horas SQL-Seite ausführen:

CREATE OR REPLACE TYPE EMPLOYEE_XML_ITEM AS OBJECT ( EMPLOYEE_ID NUMBER (6,0), FIRST_NAME VARCHAR2 (20), LAST_NAME VARCHAR2 (25), EMAIL VARCHAR2 (25), PHONE_NUMBER VARCHAR2 (20), HIRE_DATE DATE, JOB_ID VARCHAR2 (10), SALARY NUMBER (8,2), COMMISSION_PCT NUMBER (2,2), MANAGER_ID NUMBER (6,0), DEPARTMENT_ID NUMBER (4,0) ) / CREATE OR REPLACE TYPE EMPLOYEES_XML_LIST AS TABLE OF EMPLOYEE_XML_ITEM / CREATE OR REPLACE TYPE EMPLOYEES_XML_ELEM AS OBJECT ( EMPLOYEE EMPLOYEES_XML_LIST ) /

Jetzt können wir die Types in Hora auf der PL/SQL Types-Seite sehen.  Da wir den Reiter ‚All Types‘ aktiviert haben, sind die Objekt Types und Collection Types in einer gemeinsamen Liste zu sehen:

Das Beispiel zeigt auch, wie Hora automatisch Warnungen bzw. Fehlermeldungen anzeigt, die von der Datenbank erzeugt werden.

Erzeugen des XSD-Schemas

Jetzt können wir die Prozedur dbms_xmlschema.generateschema verwenden, um eine XML-Schemabeschreibung zu erzeugen:

select dbms_xmlschema.generateschema( 'HR', 'EMPLOYEES_XML_ELEM', 'EMPLOYEES_LIST') from dual;

Das Ergebnis der Abfrage ist das XSD als CLOB-Datentyp. Wir reichen den CLOB gleich weiter an die Prozedur dbms_xmlschema.registerschema:

begin
  dbms_xmlschema.registerschema( 'EMPLOYEES.XSD',
    dbms_xmlschema.generateschema(
      schemaname => 'HR',
      typename => 'EMPLOYEES_XML_ELEM',
      elementname => 'EMPLOYEES_LIST'),
    gentypes => FALSE,
    genbean => FALSE,
    gentables => FALSE);
end;

Nachdem dieser anonyme Block ausgeführt worden ist, ist das XSD in der Datenbank registriert. Das können wir uns in Hora auf der XML-Seite anschauen:

Auf der zweiten Tab-Lasche zeigt Hora die Zuordnung der XSD-Elemente zu den PL/SQL Types:

Um die beschriebene PL/SQL-Logik bequem nutzen zu können, haben wir den Code in einem PL/SQL-Package XML_LOGIC zusammengefasst:

Mit Hilfe dieses Packages brauchen wir nur noch eine einzige Methode, register_xsd, aufzurufen:

begin
  XML_LOGIC.register_xsd( 'EMPLOYEES');
end;

Die Prozedur registriert das XSD in der Datenbank. Darüber hinaus speichert sie es auch als XSD-Datei in einem vordefinierten Oracle-Directory.
Sie können diese XSD-Datei den Softwareentwicklern bereitstellen, welche die zu importierenden XML-Daten zukünftig bereitstellen sollen.

Schnittstellentabelle für den Datenimport

Die Tabelle XML_EMPLOYEES_TRANSFER wird von uns für die Durchführung des Datenimports verwendet.  Die externe Stelle, die das System mit Daten versorgen soll, kann diese Tabelle zum Bereitstellen neuer Daten benutzen.
Die Struktur der Tabelle sieht wie folgt aus:

Für jeden Importvorgang fügt das externe System einen neuen Datensatz in die Tabelle ein. Ein Trigger versorgt die Primärschlüsselspalte ID mit einem neuen Wert aus einer Sequence.
Die Spalte XML_DATA enthält die zu importierenden Daten im XML-Format. Die restlichen Spalten beinhalten Metadaten wie Zeitstempel, Statistiken und Fehlermeldungen. Letztere beinhaltet Informationen, wenn beim Import Fehler aufgetreten sind.

Die View XML_EMPLOYEES_TRANS_DATA_V selektiert Daten aus der Importtabelle, wobei alle XML-Daten in eine relationale Struktur gebracht werden:

Hier https://www.oratable.com/xmltable-convert-xml-to-relational-form/ finden Sie weitergehende Informationen zur Umwandlung von XML-Daten eine relationale Ansicht.
Sie können sich den Inhalt der View später anschauen, nachdem wir einen Datensatz in die Tabelle XML_EMPLOYEES_TRANSFER eingefügt haben.

Datenimport-Logik

Nachdem wir die Grundlagen bereitgestellt haben, können wir mit der Implementierung der eigentlichen Importlogik fortfahren. Dazu erstellen wir das PL/SQL-Package XML_INTERFACE.

….

Das Package beinhaltet genau eine Prozedur für den XML-Import. Die Prozedur implementiert die folgenden Schritte:

  • Iterieren der Daten zur View XML_EMPLOYEES_TRANS_DATA_V für die übergebene AXmlTranferID
  • Falls der Primärschlüsselwert EMPLOYEE.ID bereits existiert,
    • Falls der EMPLOYEE-Datensatz gerade gesperrt ist, wird der Eintrag übersprungen
    • Anderenfalls wird der EMPLOYEE aktualisiert
  • Ansonsten wird ein neuer EMPLOYEE-Datensatz eingefügt.
  • Abschließend werden die Statistiken und ggf. Fehlermeldungen gespeichert.

Der XML-Import kann nun mittels folgender zwei Schritte genutzt werden:

  • Einfügen eines Datensatzes in die Tabelle XML_EMPLOYEES_TRANSFER table. Die XML-Struktur wird dazu in der Spalte XML_DATA bereitgestellt.
  • Die ID des Transfer-Datensatzes wird anschließend an die Prozedur ProcessEmployeesImport weitergeleitet.

Anzeige der importierten Daten

Nachdem der Datensatz eingefügt wurde, können Sie das XML noch einmal in Hora überprüfen:

Nun führen wir die Prozedur aus. Die DataContent-Ansicht in Hora enthält jetzt den neu eingefügten Datensatz:

Die Statistiken zum Datensatz der Transfertabelle wurden nun aktualisiert:

Mit Hilfe der bereits beschriebenen View XML_EMPLOYEES_TRANS_DATA_V können die Daten aus dem XML zusammen mit den Spalten der Tabelle XML_EMPLOYEES_TRANSFER in relationaler Form angezeigt werden:

Zusammenfassung

Die XML-basierte Datenimport-Schnittstelle ist nun einsatzbereit. Sie können das Beispiel hier herunterladen und als Vorlage benutzen, um Ihre eigene Datenimport-Schnittstelle zu erstellen.

Weitaus komplexere Schnittstellenimplementierungen aufbauend auf dem vorgestellten Konzept haben sich bereits in der praktischen Projektarbeit bewährt. Die beteiligten Entwickler berichten von einer deutlichen Verbesserung der Produktivität durch den Einsatz unserer Tools.

Im nächsten Blockbeitrag greifen wir das Beispiel wieder auf und ergänzen es um eine Datenexport-Schnittstelle.