Post on 15-Aug-2019
transcript
Seite 1DOAG2006 XML-Schnittstellen zum Import und Export von Daten für eine Oracle DB – Ein Erfahrungsbericht
XML-Schnittstellen zum Import und Export
von Daten für eine Oracle DB
Ein Erfahrungsbericht
DOAG 2006
Dr. Wolfgang Braunisch
Opitz Consulting GmbH
Seite 2DOAG2006 XML-Schnittstellen zum Import und Export von Daten für eine Oracle DB – Ein Erfahrungsbericht
Gliederung
● Anforderungsszenario
● Lösungsalternativen
● Realisierte Lösung● Technische Hürden, Lösungen und Workarounds
● Alternative Lösungen
● Fazit
● Fragen und Antworten
Seite 3DOAG2006 XML-Schnittstellen zum Import und Export von Daten für eine Oracle DB – Ein Erfahrungsbericht
Anforderungsszenario: Übersicht
DB-Server
Filesystem
xml
csv
xml
csv
xsdSFTP
SCPImport-Jobs
Export-Jobs
ORACLE-DB
Seite 4DOAG2006 XML-Schnittstellen zum Import und Export von Daten für eine Oracle DB – Ein Erfahrungsbericht
Anforderungsszenario: Schnittstellenformate
● Importdateien können sowohl im xml-Format als auch im csv-Format vorliegen
● Exportdateien müssen im xml-Format und im csv-Formaterzeugt werden
● Verschiedene Typen von Import- und Exportdateien● Unterscheidung nach Speicherort und Namenskonventionen
● Für xml-Dateitypen sind externe Schemata definiert
● Bestimmte Dateien können recht groß werden (mehrere 100 MB)
Seite 5DOAG2006 XML-Schnittstellen zum Import und Export von Daten für eine Oracle DB – Ein Erfahrungsbericht
Anforderungsszenario : Schnittstellen
xml-Dokumente haben i. A. eine hierarchische Struktur:
<dokument_knoten><masterelement_knoten>
<masterattribut>Wert</masterattribut>...<detailelement_knoten>
<detailattribut>Wert</detailattribut>...
</detailelement_knoten><detailelement_knoten>
...</detailelement_knoten>...
</masterelement_knoten><masterelement_knoten>
...</masterelement_knoten>...
<\dokument_knoten>
Master-tabelle
Detail-tabelle
Seite 6DOAG2006 XML-Schnittstellen zum Import und Export von Daten für eine Oracle DB – Ein Erfahrungsbericht
Anforderungsszenario : Schnittstellen
xml-Dokumente haben i. A. eine hierarchische Struktur:
<dokument_knoten><masterelement_knoten>
<masterattribut>Wert</masterattribut>...<detailelement_knoten>
<detailattribut>Wert</detailattribut>...
</detailelement_knoten><detailelement_knoten>
...</detailelement_knoten>...
</masterelement_knoten><masterelement_knoten>
...</masterelement_knoten>...
<\dokument_knoten>
Master-tabelle
Detail-tabelle
Seite 7DOAG2006 XML-Schnittstellen zum Import und Export von Daten für eine Oracle DB – Ein Erfahrungsbericht
Anforderungsszenario : Schnittstellen
xml-Dokumente haben i. A. eine hierarchische Struktur:
<dokument_knoten><masterelement_knoten>
<masterattribut>Wert</masterattribut>...<detailelement_knoten>
<detailattribut>Wert</detailattribut>...
</detailelement_knoten><detailelement_knoten>
...</detailelement_knoten>...
</masterelement_knoten><masterelement_knoten>
...</masterelement_knoten>...
<\dokument_knoten>
Master-tabelle
Detail-tabelle
Seite 8DOAG2006 XML-Schnittstellen zum Import und Export von Daten für eine Oracle DB – Ein Erfahrungsbericht
Anforderungsszenario : Schnittstellen
...veranschaulicht am bekannten EMP-DEPT Beispiel:
<Departments-Employees><Dept>
<DeptNo>10</DeptNo>...<Emp>
<EmpNo>7782</EmpNo>...
</Emp><Emp>
...</Emp>...
</Dept><Dept>
...</Dept>...
</Departments-Employees>
DEPT
EMP
Seite 9DOAG2006 XML-Schnittstellen zum Import und Export von Daten für eine Oracle DB – Ein Erfahrungsbericht
Anforderungsszenario : Schnittstellen
csv-Dokumente haben eine flache Struktur:
Deptno , ... , Empno, ...10 , ... , 7782 , ...10 , ... , 7839 , ...10 , ... , 7934 , ...20 , ... , 7369 , ...20 , ... , 7566 , ...20 , ... , 7788 , ...20 , ... , 7876 , ...20 , ... , 7902 , ...30 , ... , 7499 , ...30 , ... , 7521 , .........
<Departments-Employees><Dept>
<DeptNo>10</DeptNo>...<Emp>
<EmpNo>7782</EmpNo>...
</Emp><Emp>
...</Emp>...
</Dept><Dept>
...</Dept>...
</Departments-Employees>
Seite 10DOAG2006 XML-Schnittstellen zum Import und Export von Daten für eine Oracle DB – Ein Erfahrungsbericht
Anforderungsszenario : Schnittstellen
csv-Dokumente haben eine flache Struktur:
Deptno , ... , Empno, ...10 , ... , 7782 , ...10 , ... , 7839 , ...10 , ... , 7934 , ...20 , ... , 7369 , ...20 , ... , 7566 , ...20 , ... , 7788 , ...20 , ... , 7876 , ...20 , ... , 7902 , ...30 , ... , 7499 , ...30 , ... , 7521 , .........
<Departments-Employees><Dept>
<DeptNo>10</DeptNo>...<Emp>
<EmpNo>7782</EmpNo>...
</Emp><Emp>
...</Emp>...
</Dept><Dept>
...</Dept>...
</Departments-Employees>
Seite 11DOAG2006 XML-Schnittstellen zum Import und Export von Daten für eine Oracle DB – Ein Erfahrungsbericht
Anforderungsszenario : Schnittstellen
csv-Dokumente haben eine flache Struktur:
Deptno , ... , Empno, ...10 , ... , 7782 , ...10 , ... , 7839 , ...10 , ... , 7934 , ...20 , ... , 7369 , ...20 , ... , 7566 , ...20 , ... , 7788 , ...20 , ... , 7876 , ...20 , ... , 7902 , ...30 , ... , 7499 , ...30 , ... , 7521 , .........
<Departments-Employees><Dept>
<DeptNo>10</DeptNo>...<Emp>
<EmpNo>7782</EmpNo>...
</Emp><Emp>
...</Emp>...
</Dept><Dept>
...</Dept>...
</Departments-Employees>
Seite 12DOAG2006 XML-Schnittstellen zum Import und Export von Daten für eine Oracle DB – Ein Erfahrungsbericht
Anforderungsszenario : Schnittstellen
csv-Dokumente haben eine flache Struktur:
Deptno , ... , Empno, ...10 , ... , 7782 , ...10 , ... , 7839 , ...10 , ... , 7934 , ...20 , ... , 7369 , ...20 , ... , 7566 , ...20 , ... , 7788 , ...20 , ... , 7876 , ...20 , ... , 7902 , ...30 , ... , 7499 , ...30 , ... , 7521 , .........
<Departments-Employees><Dept>
<DeptNo>10</DeptNo>...<Emp>
<EmpNo>7782</EmpNo>...
</Emp><Emp>
...</Emp>...
</Dept><Dept>
...</Dept>...
</Departments-Employees>
Seite 13DOAG2006 XML-Schnittstellen zum Import und Export von Daten für eine Oracle DB – Ein Erfahrungsbericht
Anforderungsszenario: Technische Daten zu Server - OS, DB, Java
● Datenbankserver OS: LINUX sles9
● ORACLE-Datenbank: 10g R1 (10.1.0.4.0)
● Java: Java 1.4 von SUN
Seite 14DOAG2006 XML-Schnittstellen zum Import und Export von Daten für eine Oracle DB – Ein Erfahrungsbericht
Gliederung
● Anforderungsszenario
● Lösungsalternativen
● Realisierte Lösung● Technische Hürden, Lösungen und Workarounds
● Alternative Lösungen
● Fazit
● Fragen und Antworten
Seite 15DOAG2006 XML-Schnittstellen zum Import und Export von Daten für eine Oracle DB – Ein Erfahrungsbericht
SQLPL/SQL
Lösungsalternativen
XML-DB
XDK
Java
Eigenentw.
PL/SQL(utl_file)
Seite 16DOAG2006 XML-Schnittstellen zum Import und Export von Daten für eine Oracle DB – Ein Erfahrungsbericht
Lösungsalternativen: XML-DB
XML-DB
+ Sehr mächtig+ Speicherung der xml-Dokumente in der DB (als xml-Dokumente)
+ Registrierung externer Schemata in der DB
+ Umfangreiche Funktionalität
- Speicherung von xml-Dokumenten in der DB war nicht erforderlich- reine Schnittstellenfunktion neben csv
→ Für unser Projekt eher etwas überdimensioniert
Seite 17DOAG2006 XML-Schnittstellen zum Import und Export von Daten für eine Oracle DB – Ein Erfahrungsbericht
Lösungsalternativen: XDK
XDK
+ Eher an einfachen Entwicklungsansprüchen orientiert
+ Utilities für Import und Export von xml-Dokumenten● dbms_xmlstore für Import
● dbms_xmlquery für Export
→ Für unser Projekt geeignet
Seite 18DOAG2006 XML-Schnittstellen zum Import und Export von Daten für eine Oracle DB – Ein Erfahrungsbericht
Lösungsalternativen: Eigenentwicklung mit UTL_FILE
Eigenentwicklung mit UTL_FILE u. ä.
+ Verarbeitung zeilenweise möglich● Zumindest für Export
● Grosse Dokumente sind nicht komplett im Hauptspeicher
- Komplette xml-Logik muss manuell entwickelt werden● Keine Nutzung der xml-Logik in entspr. Bibliotheken
● Gefahr unvorhergesehener Probleme
● Gefahr eines hohen Wartungsaufwandes
→ Keine echte Alternative
Seite 19DOAG2006 XML-Schnittstellen zum Import und Export von Daten für eine Oracle DB – Ein Erfahrungsbericht
Lösungsalternativen: Java
Java
- Prozesssteuerung ist überwiegend PL/SQL
+ Verwendung von Java-Bibliotheken mit einschlägiger xml-Logik möglich
● Kann in PL/SQL eingebunden werden
● Kann direkt auf dem Betriebssystem aufgerufen werden
● DB-Zugriff aus Java heraus möglich
→ Als Ergänzung zu PL/SQL-Funktionalität hilfreich
Seite 20DOAG2006 XML-Schnittstellen zum Import und Export von Daten für eine Oracle DB – Ein Erfahrungsbericht
Gliederung
● Anforderungsszenario
● Lösungsalternativen
● Realisierte Lösung● Technische Hürden, Lösungen und Workarounds
● Alternative Lösungen
● Fazit
● Fragen und Antworten
Seite 21DOAG2006 XML-Schnittstellen zum Import und Export von Daten für eine Oracle DB – Ein Erfahrungsbericht
Realisierte Lösung: Import
● CSV-Dateien werden über External Tables importiert
● Type sql_loader
● Keine Zwischentabelle => Select direkt auf die Datei
● Zeilenweise Verarbeitung über Cursor-Loops in PL/SQL
● Auch MERGE und Minus-Selects möglich
● XML-Dateien:● Umwandlung in die entsprechenden csv-Formate ist relativ
einfach...
Seite 22DOAG2006 XML-Schnittstellen zum Import und Export von Daten für eine Oracle DB – Ein Erfahrungsbericht
Realisierte Lösung : Übersicht
xmlcsv
xml csv
ORACLE-DB
csvImportdateien ImportdateienStylesheetTransformation
dbms_xmlquery
StylesheetTransformation
Validierung
Relationales Datenmodell
Exportdateien Exportdateien
External TablesExternal TablesExternal Tables
ORACLEScheduler
Seite 23DOAG2006 XML-Schnittstellen zum Import und Export von Daten für eine Oracle DB – Ein Erfahrungsbericht
Realisierte Lösung : Jobsteuerung
● Prozesssteuerung● Regelmäßiges Einpflegen der Importdateien in die Tabellen der
ORACLE-DB
● Regelmäßige Erzeugung von Exportdateien aus Inhalten der Tabellen der ORACLE-DB
● Verwaltung der Im- und Export-Dateien von außen(scp, sftp, ssh-Webserver)
● Steuerung aus der DB heraus● Verwendung von dbms_scheduler
● Aufruf von PL/SQL-Programmen
● Java-API für Filesystemzugriffe und OS-Aufrufe(eingebettet in PL/SQL-Wrapper)
Seite 24DOAG2006 XML-Schnittstellen zum Import und Export von Daten für eine Oracle DB – Ein Erfahrungsbericht
Realisierte Lösung : Import
● Validierung gegen externe Schemata
● Validierung der Datei außerhalb der DB => Java
● XML-Schemata (*.xsd) liegen auf dem Filesystem
● Aufruf aus PL/SQL heraus (Wrapper)
● Stylesheet-Transformationen
● Umwandlung xml - csv vor Import in die DB => Java
● Stylesheets (*.xsl) liegen auf dem Filesystem
● Aufruf aus PL/SQL heraus (Wrapper)
Seite 25DOAG2006 XML-Schnittstellen zum Import und Export von Daten für eine Oracle DB – Ein Erfahrungsbericht
Realisierte Lösung : Import
Schematischer Aufbau der xml-Stylesheets:<xsl:stylesheet version="1.0„
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><xsl:output method="text"/><xsl:template match=„Departments-Employees">
<xsl:text> DeptNo, ... ,EmpNo, ... ,</xsl:text><xsl:text> </xsl:text><xsl:for-each select=„Dept/Emp">
<xsl:text>""" </xsl:text><xsl:value-of select="../DeptNo"/><xsl:text>""","""</xsl:text>...
<xsl:value-of select=„EmpNo"/>...
<xsl:text>""" </xsl:text></xsl:for-each>
</xsl:template></xsl:stylesheet>
DeptNo, ... , EmpNo, ...10 , ... , 7782 , ...10 , ... , 7839 , ......
<Departments-Employees><Dept>
<DeptNo>10</DeptNo>...<Emp>
<EmpNo>7782</EmpNo>...
</Emp><Emp>
...</Emp>...
</Dept><Dept>
...</Dept>...
</Departments-Employees>
Seite 26DOAG2006 XML-Schnittstellen zum Import und Export von Daten für eine Oracle DB – Ein Erfahrungsbericht
Realisierte Lösung : Import
Schematischer Aufbau der xml-Stylesheets:<xsl:stylesheet version="1.0„
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><xsl:output method="text"/><xsl:template match=„Departments-Employees">
<xsl:text> DeptNo, ... ,EmpNo, ... ,</xsl:text><xsl:text> </xsl:text><xsl:for-each select=„Dept/Emp">
<xsl:text>""" </xsl:text><xsl:value-of select="../DeptNo"/><xsl:text>""","""</xsl:text>...
<xsl:value-of select=„EmpNo"/>...
<xsl:text>""" </xsl:text></xsl:for-each>
</xsl:template></xsl:stylesheet>
DeptNo, ... , EmpNo, ...10 , ... , 7782 , ...10 , ... , 7839 , ......
<Departments-Employees><Dept>
<DeptNo>10</DeptNo>...<Emp>
<EmpNo>7782</EmpNo>...
</Emp><Emp>
...</Emp>...
</Dept><Dept>
...</Dept>...
</Departments-Employees>
Seite 27DOAG2006 XML-Schnittstellen zum Import und Export von Daten für eine Oracle DB – Ein Erfahrungsbericht
Realisierte Lösung : Import
Schematischer Aufbau der xml-Stylesheets:<xsl:stylesheet version="1.0„
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><xsl:output method="text"/><xsl:template match=„Departments-Employees">
<xsl:text> DeptNo, ... ,EmpNo, ... ,</xsl:text><xsl:text> </xsl:text><xsl:for-each select=„Dept/Emp">
<xsl:text>""" </xsl:text><xsl:value-of select="../DeptNo"/><xsl:text>""","""</xsl:text>...
<xsl:value-of select=„EmpNo"/>...
<xsl:text>""" </xsl:text></xsl:for-each>
</xsl:template></xsl:stylesheet>
DeptNo, ... , EmpNo, ...10 , ... , 7782 , ...10 , ... , 7839 , ......
<Departments-Employees><Dept>
<DeptNo>10</DeptNo>...<Emp>
<EmpNo>7782</EmpNo>...
</Emp><Emp>
...</Emp>...
</Dept><Dept>
...</Dept>...
</Departments-Employees>
Seite 28DOAG2006 XML-Schnittstellen zum Import und Export von Daten für eine Oracle DB – Ein Erfahrungsbericht
Realisierte Lösung : Import
Schematischer Aufbau der xml-Stylesheets:<xsl:stylesheet version="1.0„
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><xsl:output method="text"/><xsl:template match=„Departments-Employees">
<xsl:text> DeptNo, ... ,EmpNo, ... ,</xsl:text><xsl:text> </xsl:text><xsl:for-each select=„Dept/Emp">
<xsl:text>""" </xsl:text><xsl:value-of select="../DeptNo"/><xsl:text>""","""</xsl:text>...
<xsl:value-of select=„EmpNo"/>...
<xsl:text>""" </xsl:text></xsl:for-each>
</xsl:template></xsl:stylesheet>
DeptNo, ... , EmpNo, ...10 , ... , 7782 , ...10 , ... , 7839 , ......
<Departments-Employees><Dept>
<DeptNo>10</DeptNo>...<Emp>
<EmpNo>7782</EmpNo>...
</Emp><Emp>
...</Emp>...
</Dept><Dept>
...</Dept>...
</Departments-Employees>
Seite 29DOAG2006 XML-Schnittstellen zum Import und Export von Daten für eine Oracle DB – Ein Erfahrungsbericht
Realisierte Lösung : Import
Schematischer Aufbau der xml-Stylesheets:<xsl:stylesheet version="1.0„
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><xsl:output method="text"/><xsl:template match=„Departments-Employees">
<xsl:text> DeptNo, ... ,EmpNo, ... ,</xsl:text><xsl:text> </xsl:text><xsl:for-each select=„Dept/Emp">
<xsl:text>""" </xsl:text><xsl:value-of select="../DeptNo"/><xsl:text>""","""</xsl:text>...
<xsl:value-of select=„EmpNo"/>...
<xsl:text>""" </xsl:text></xsl:for-each>
</xsl:template></xsl:stylesheet>
DeptNo, ... , EmpNo, ...10 , ... , 7782 , ...10 , ... , 7839 , ......
<Departments-Employees><Dept>
<DeptNo>10</DeptNo>...<Emp>
<EmpNo>7782</EmpNo>...
</Emp><Emp>
...</Emp>...
</Dept><Dept>
...</Dept>...
</Departments-Employees>
Seite 30DOAG2006 XML-Schnittstellen zum Import und Export von Daten für eine Oracle DB – Ein Erfahrungsbericht
Realisierte Lösung : Import
Schematischer Aufbau der xml-Stylesheets:<xsl:stylesheet version="1.0„
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><xsl:output method="text"/><xsl:template match=„Departments-Employees">
<xsl:text> DeptNo, ... ,EmpNo, ... ,</xsl:text><xsl:text> </xsl:text><xsl:for-each select=„Dept/Emp">
<xsl:text>""" </xsl:text><xsl:value-of select="../DeptNo"/><xsl:text>""","""</xsl:text>...
<xsl:value-of select=„EmpNo"/>...
<xsl:text>""" </xsl:text></xsl:for-each>
</xsl:template></xsl:stylesheet>
DeptNo, ... , EmpNo, ...10 , ... , 7782 , ...10 , ... , 7839 , ......
<Departments-Employees><Dept>
<DeptNo>10</DeptNo>...<Emp>
<EmpNo>7782</EmpNo>...
</Emp><Emp>
...</Emp>...
</Dept><Dept>
...</Dept>...
</Departments-Employees>
Seite 31DOAG2006 XML-Schnittstellen zum Import und Export von Daten für eine Oracle DB – Ein Erfahrungsbericht
Realisierte Lösung : Import
● Performance-Probleme bei● Stylesheet-Transformationen● Validierung gegen externe Schemata
● Validerung und Transformation erfolgen● aus PL/SQL heraus● Mit Hilfe von Java-Utilities
● Erster Ansatz● Erstellung von Java-Programmen für Validierung und
Transformation● Laden dieser Programme in die DB und erstellen von PL/SQL-
Wrappern● In den DB-Jobs Aufruf der PL/SQL-Wrapper
Seite 32DOAG2006 XML-Schnittstellen zum Import und Export von Daten für eine Oracle DB – Ein Erfahrungsbericht
Realisierte Lösung : Import
● Performance-Probleme:● Validierungen und Transformationen dauern mindestens einen
Faktor 10 länger als der direkte Aufruf auf dem OS
● Änderungen an DB-Parametern wie java_pool_size halfen nicht
● Workaround: Indirekter Aufruf der Java-Programme
beginpr_os_aufruf ('java_programm name_qellxml name_xsl name_zielxml');
end;
PL/SQL-Programm
In PL/SQLeingebundenesJava-Programm
OS-Befehl(LINUX)
Java-Programm
DB OS
Seite 33DOAG2006 XML-Schnittstellen zum Import und Export von Daten für eine Oracle DB – Ein Erfahrungsbericht
Realisierte Lösung : Export
XML-Dokumente erzeugen mit dbms_xmlquery
● CLOB mit UTL_FILE in eine Datei schreiben
declarev_query_ctx dbms_xmlquery.ctxtype;v_ergebnis_xml clob;
begin-- XML-Dokument als CLOB erstellenv_query_ctx := dbms_xmlquery.newcontext ('select deptno, dname, ... from dept'); dbms_xmlquery.setrowsettag (v_query_ctx, 'Departments-Employees');dbms_xmlquery.setencodingtag(v_query_ctx, 'iso-8859-15');--v_ergebnis_xml := dbms_xmlquery.getxml(v_query_ctx);dbms_xmlquery.closecontext (v_query_ctx);--
end;
Seite 34DOAG2006 XML-Schnittstellen zum Import und Export von Daten für eine Oracle DB – Ein Erfahrungsbericht
Realisierte Lösung : Export
XML-Dokumente erzeugen mit dbms_xmlquery
● CLOB mit UTL_FILE in eine Datei schreiben
declarev_query_ctx dbms_xmlquery.ctxtype;v_ergebnis_xml clob;
begin-- XML-Dokument als CLOB erstellenv_query_ctx := dbms_xmlquery.newcontext ('select deptno, dname, ... from dept'); dbms_xmlquery.setrowsettag (v_query_ctx, 'Departments-Employees');dbms_xmlquery.setencodingtag(v_query_ctx, 'iso-8859-15');--v_ergebnis_xml := dbms_xmlquery.getxml(v_query_ctx);dbms_xmlquery.closecontext (v_query_ctx);--
end;
Seite 35DOAG2006 XML-Schnittstellen zum Import und Export von Daten für eine Oracle DB – Ein Erfahrungsbericht
Realisierte Lösung : Export
XML-Dokumente erzeugen mit dbms_xmlquery
● CLOB mit UTL_FILE in eine Datei schreiben
declarev_query_ctx dbms_xmlquery.ctxtype;v_ergebnis_xml clob;
begin-- XML-Dokument als CLOB erstellenv_query_ctx := dbms_xmlquery.newcontext ('select deptno, dname, ... from dept'); dbms_xmlquery.setrowsettag (v_query_ctx, 'Departments-Employees');dbms_xmlquery.setencodingtag(v_query_ctx, 'iso-8859-15');--v_ergebnis_xml := dbms_xmlquery.getxml(v_query_ctx);dbms_xmlquery.closecontext (v_query_ctx);--
end;
Seite 36DOAG2006 XML-Schnittstellen zum Import und Export von Daten für eine Oracle DB – Ein Erfahrungsbericht
Realisierte Lösung : Export
XML-Dokumente erzeugen mit dbms_xmlquery
● CLOB mit UTL_FILE in eine Datei schreiben
declarev_query_ctx dbms_xmlquery.ctxtype;v_ergebnis_xml clob;
begin-- XML-Dokument als CLOB erstellenv_query_ctx := dbms_xmlquery.newcontext ('select deptno, dname, ... from dept'); dbms_xmlquery.setrowsettag (v_query_ctx, 'Departments-Employees');dbms_xmlquery.setencodingtag(v_query_ctx, 'iso-8859-15');--v_ergebnis_xml := dbms_xmlquery.getxml(v_query_ctx);dbms_xmlquery.closecontext (v_query_ctx);--
end;
Seite 37DOAG2006 XML-Schnittstellen zum Import und Export von Daten für eine Oracle DB – Ein Erfahrungsbericht
Realisierte Lösung : Export
XML-Dokumente erzeugen mit dbms_xmlquery
● CLOB mit UTL_FILE in eine Datei schreiben
declarev_query_ctx dbms_xmlquery.ctxtype;v_ergebnis_xml clob;
begin-- XML-Dokument als CLOB erstellenv_query_ctx := dbms_xmlquery.newcontext ('select deptno, dname, ... from dept'); dbms_xmlquery.setrowsettag (v_query_ctx, 'Departments-Employees');dbms_xmlquery.setencodingtag(v_query_ctx, 'iso-8859-15');--v_ergebnis_xml := dbms_xmlquery.getxml(v_query_ctx);dbms_xmlquery.closecontext (v_query_ctx);--
end;
Seite 38DOAG2006 XML-Schnittstellen zum Import und Export von Daten für eine Oracle DB – Ein Erfahrungsbericht
Realisierte Lösung : Hierarchische XML-Strukturen
● dbms_xmlquery greift nur auf eine Tabelle/View zu
● Einfache Join-View führt nicht zum gewünschten Ergebnis
create view dept_emp_v asselect dept.deptno,
…emp.EMPNO,…
from dept,emp
where emp.deptno = dept.deptno;/
<Departments-Employees>
<ROW num="1"><DEPTNO>10</DEPTNO>...<EMPNO>7782</EMPNO>...
</ROW><ROW num="2">
<DEPTNO>10</DEPTNO>...<EMPNO>7839</EMPNO>...
</ROW>...
</Departments-Employees>
Seite 39DOAG2006 XML-Schnittstellen zum Import und Export von Daten für eine Oracle DB – Ein Erfahrungsbericht
Realisierte Lösung : Hierarchische XML-Strukturen
● dbms_xmlquery greift nur auf eine Tabelle/View zu
● Einfache Join-View führt nicht zum gewünschten Ergebnis
create view dept_emp_v asselect dept.deptno,
…emp.EMPNO,…
from dept,emp
where emp.deptno = dept.deptno;/
<Departments-Employees>
<ROW num="1"><DEPTNO>10</DEPTNO>...<EMPNO>7782</EMPNO>...
</ROW><ROW num="2">
<DEPTNO>10</DEPTNO>...<EMPNO>7839</EMPNO>...
</ROW>...
</Departments-Employees>
Seite 40DOAG2006 XML-Schnittstellen zum Import und Export von Daten für eine Oracle DB – Ein Erfahrungsbericht
Realisierte Lösung : Hierarchische XML-Strukturen
● Master-Detail-Struktur wird aber abgebildet, wenn die Tabelle eine Spalte mit Objektrelationalem Typ enthält, welcher eine Liste mit (Detail)-Objekten enthält (Collection).
create or replace type t_emp_objas object (
empno number (4),ename varchar2 (10),…
);/
create or replace typet_emp_liste is table of t_emp_obj;/
Seite 41DOAG2006 XML-Schnittstellen zum Import und Export von Daten für eine Oracle DB – Ein Erfahrungsbericht
Realisierte Lösung : Hierarchische XML-Strukturen
● Alternative: View, die den Objekttyp zur Laufzeit bildet:
create view dept_emp_v asselect deptno,
…cast (multiset (select empno,
…from emp
where emp.deptno = dept.deptnoorder by empno
) as t_emp_liste) as emp
from dept;/
Seite 42DOAG2006 XML-Schnittstellen zum Import und Export von Daten für eine Oracle DB – Ein Erfahrungsbericht
Realisierte Lösung : Hierarchische XML-Strukturen
● Alternative: View, die den Objekttyp zur Laufzeit bildet:
create view dept_emp_v asselect deptno,
…cast (multiset (select empno,
…from emp
where emp.deptno = dept.deptnoorder by empno
) as t_emp_liste) as emp
from dept;/
Seite 43DOAG2006 XML-Schnittstellen zum Import und Export von Daten für eine Oracle DB – Ein Erfahrungsbericht
Realisierte Lösung : Hierarchische XML-Strukturen
● Alternative: View, die den Objekttyp zur Laufzeit bildet:
create view dept_emp_v asselect deptno,
…cast (multiset (select empno,
…from emp
where emp.deptno = dept.deptnoorder by empno
) as t_emp_liste) as emp
from dept;/
Seite 44DOAG2006 XML-Schnittstellen zum Import und Export von Daten für eine Oracle DB – Ein Erfahrungsbericht
Realisierte Lösung : Hierarchische XML-Strukturen
● Knotenstruktur kann nur bedingt konfiguriert werden <Departments-Employees>
<ROW num="1"><DEPTNO>10</DEPTNO>...<EMP>
<EMP_ITEM><EMPNO>7782</EMPNO>...
</EMP_ITEM><EMP_ITEM>
...</EMP_ITEM>...
</EMP></ROW><ROW num="2">
...</ROW>...
</Departments-Employees>
<Departments-Employees><Dept>
<DeptNo>10</Deptno>...<Emp>
<EmpNo>7782t</EmpNo>...
</Emp><Emp>
...</Emp>...
</Dept><Dept>
...</Dept>...
</Departments-Employees>
Seite 45DOAG2006 XML-Schnittstellen zum Import und Export von Daten für eine Oracle DB – Ein Erfahrungsbericht
Realisierte Lösung : Hierarchische XML-Strukturen
● Knotenstruktur kann nur bedingt konfiguriert werden <Departments-Employees>
<ROW num="1"><DEPTNO>10</DEPTNO>...<EMP>
<EMP_ITEM><EMPNO>7782</EMPNO>...
</EMP_ITEM><EMP_ITEM>
...</EMP_ITEM>...
</EMP></ROW><ROW num="2">
...</ROW>...
</Departments-Employees>
<Departments-Employees><Dept>
<DeptNo>10</Deptno>...<Emp>
<EmpNo>7782t</EmpNo>...
</Emp><Emp>
...</Emp>...
</Dept><Dept>
...</Dept>...
</Departments-Employees>
Seite 46DOAG2006 XML-Schnittstellen zum Import und Export von Daten für eine Oracle DB – Ein Erfahrungsbericht
Realisierte Lösung : Hierarchische XML-Strukturen
● ...Knotenstruktur kann nur bedingt konfiguriert werden
● Lösung: Stylesheet-Transformation
● In unserem Fall auf dem Filesystem
● Anlog zur Umwandlung XML-CSV
● Alternative: direkt im CLOB
● Verwendung von XDK-Werkzeugen
– dbms_xmlgen
– dbms_xmlparser
– dbms_xslprocessor
– dbms_xmldom
Seite 47DOAG2006 XML-Schnittstellen zum Import und Export von Daten für eine Oracle DB – Ein Erfahrungsbericht
Realisierte Lösung : Große Dateien
● Erzeugte Dateien können einige 100 MB groß werden
● Speicherung im Hauptspeicher als ein CLOB ist für die vorhandene Systemkonfiguratíon problematisch
● Vergrößerung des Hauptspeichers, um solche CLOB-Größen zu verkraften, ist angesichts der Projektanforderungen nicht angemessen.
● Lösung: Erzeugung des XML-Dokuments in mehreren Schritten:● Begrenzung der Datensätze pro Vorgang:
...dbms_xmlquery.setmaxrows(v_query_ctx, 100);...
Seite 48DOAG2006 XML-Schnittstellen zum Import und Export von Daten für eine Oracle DB – Ein Erfahrungsbericht
Realisierte Lösung : Große Dateien
● ...Lösung: Erzeugung des xml-Dokuments in mehreren Schritten:● Schleife zur Erzeugung kleiner XMLDokumente:
● Achtung: Jedes Ergebnis-Clob ist ein wohlgeformtes XML-Dokument
● Der Dokumentheader wird jedes Mal eingefügt
● Auch der Dokumentknoten wird jedes Mal geöffnet und geschlossen
dbms_xmlquery.setraisenorowsexception(v_query_ctx, true);begin
loopv_ergebnis_xml := dbms_xmlquery.getxml (v_query_ctx);...
end loop;exception
when others thennull;
end;
Seite 49DOAG2006 XML-Schnittstellen zum Import und Export von Daten für eine Oracle DB – Ein Erfahrungsbericht
Realisierte Lösung : Große Dateien
● ...Lösung: Erzeugung des xml-Dokuments in mehreren Schritten:● Schleife zur Erzeugung kleiner XMLDokumente:
● Achtung: Jedes Ergebnis-Clob ist ein wohlgeformtes XML-Dokument
● Der Dokumentheader wird jedes Mal eingefügt
● Auch der Dokumentknoten wird jedes Mal geöffnet und geschlossen
dbms_xmlquery.setraisenorowsexception(v_query_ctx, true);begin
loopv_ergebnis_xml := dbms_xmlquery.getxml (v_query_ctx);...
end loop;exception
when others thennull;
end;
Seite 50DOAG2006 XML-Schnittstellen zum Import und Export von Daten für eine Oracle DB – Ein Erfahrungsbericht
Realisierte Lösung : Große Dateien
● ...Lösung: Erzeugung des xml-Dokuments in mehreren Schritten:
● Zusammenführung der Einzeldokumente in einer Datei● Entfernen der ersten beiden Zeilen ab dem 2. CLOB
● Entfernen der letzten Zeile in allen CLOBs bis auf das letzte
● Zeilen können in CLOB durch chr(10) identifiziert werden
● Alle Buffer an die XML-Datei anhängen
– Verwendung von utl_file.put
● Aufteilen CLOB in n Buffer mit maximal 32767 Zeichen
– Verwendung von dbms_lob.substr => varchar2-Variable
– Buffergröße so wählen, dass die beiden ersten Zeilen komplett im ersten Buffer sind und die letzte Zeile komplett im letzten Buffer ist
Seite 51DOAG2006 XML-Schnittstellen zum Import und Export von Daten für eine Oracle DB – Ein Erfahrungsbericht
Gliederung
● Anforderungsszenario
● Lösungsalternativen
● Realisierte Lösung● Technische Hürden, Lösungen und Workarounds
● Alternative Lösungen
● Fazit
● Fragen und Antworten
Seite 52DOAG2006 XML-Schnittstellen zum Import und Export von Daten für eine Oracle DB – Ein Erfahrungsbericht
Alternative Lösung: Import mit dbms_xmlstore
Externe XML-Datei in clob laden:
declaredeclarev_xml clob;v_xml_lobloc clob;v_datei bfile := bfilename ('XMLDIRECTORY', 'dept.xml');...
begindbms_lob.open (v_datei, dbms_lob.lob_readonly);dbms_lob.createtemporary (v_xml_lobloc, true);dbms_lob.LOADFROMFILE(v_xml_lobloc, v_datei, dbms_lob.lobmaxsize);v_xml := v_xml_lobloc;dbms_lob.freetemporary (v_xml_lobloc);dbms_lob.CLOSE(v_datei);...
end;/
Seite 53DOAG2006 XML-Schnittstellen zum Import und Export von Daten für eine Oracle DB – Ein Erfahrungsbericht
Alternative Lösung: Import mit dbms_xmlstore
XML-Dokument in Zieltabelle laden:
declare...v_xml clob;v_insctx dbms_xmlstore.ctxtype;v_rows number;
begin...v_insctx := dbms_xmlstore.newcontext('DEPT');dbms_xmlstore.clearupdatecolumnlist (v_insctx);dbms_xmlstore.setupdatecolumn (v_insctx, 'DEPTNO');dbms_xmlstore.setupdatecolumn (v_insctx, 'DNAME');...v_rows := dbms_xmlstore.insertxml (v_insctx, v_xml);dbms_xmlstore.closecontext (v_insctx);--commit;
end;/
Seite 54DOAG2006 XML-Schnittstellen zum Import und Export von Daten für eine Oracle DB – Ein Erfahrungsbericht
Alternative Lösung: Import mit dbms_xmlstore
XML-Dokument in Zieltabelle laden:
declare...v_xml clob;v_insctx dbms_xmlstore.ctxtype;v_rows number;
begin...v_insctx := dbms_xmlstore.newcontext('DEPT');dbms_xmlstore.clearupdatecolumnlist (v_insctx);dbms_xmlstore.setupdatecolumn (v_insctx, 'DEPTNO');dbms_xmlstore.setupdatecolumn (v_insctx, 'DNAME');...v_rows := dbms_xmlstore.insertxml (v_insctx, v_xml);dbms_xmlstore.closecontext (v_insctx);--commit;
end;/
Seite 55DOAG2006 XML-Schnittstellen zum Import und Export von Daten für eine Oracle DB – Ein Erfahrungsbericht
Alternative Lösung: Import mit dbms_xmlstore
XML-Dokument in Zieltabelle laden:
declare...v_xml clob;v_insctx dbms_xmlstore.ctxtype;v_rows number;
begin...v_insctx := dbms_xmlstore.newcontext('DEPT');dbms_xmlstore.clearupdatecolumnlist (v_insctx);dbms_xmlstore.setupdatecolumn (v_insctx, 'DEPTNO');dbms_xmlstore.setupdatecolumn (v_insctx, 'DNAME');...v_rows := dbms_xmlstore.insertxml (v_insctx, v_xml);dbms_xmlstore.closecontext (v_insctx);--commit;
end;/
Seite 56DOAG2006 XML-Schnittstellen zum Import und Export von Daten für eine Oracle DB – Ein Erfahrungsbericht
Alternative Lösung: Import mit dbms_xmlstore
XML-Dokument in Zieltabelle laden:
declare...v_xml clob;v_insctx dbms_xmlstore.ctxtype;v_rows number;
begin...v_insctx := dbms_xmlstore.newcontext('DEPT');dbms_xmlstore.clearupdatecolumnlist (v_insctx);dbms_xmlstore.setupdatecolumn (v_insctx, 'DEPTNO');dbms_xmlstore.setupdatecolumn (v_insctx, 'DNAME');...v_rows := dbms_xmlstore.insertxml (v_insctx, v_xml);dbms_xmlstore.closecontext (v_insctx);--commit;
end;/
Seite 57DOAG2006 XML-Schnittstellen zum Import und Export von Daten für eine Oracle DB – Ein Erfahrungsbericht
Alternative Lösung: Import mit dbms_xmlstore
XML-Dokument in Zieltabelle laden:
declare...v_xml clob;v_insctx dbms_xmlstore.ctxtype;v_rows number;
begin...v_insctx := dbms_xmlstore.newcontext('DEPT');dbms_xmlstore.clearupdatecolumnlist (v_insctx);dbms_xmlstore.setupdatecolumn (v_insctx, 'DEPTNO');dbms_xmlstore.setupdatecolumn (v_insctx, 'DNAME');...v_rows := dbms_xmlstore.insertxml (v_insctx, v_xml);dbms_xmlstore.closecontext (v_insctx);--commit;
end;/
Seite 58DOAG2006 XML-Schnittstellen zum Import und Export von Daten für eine Oracle DB – Ein Erfahrungsbericht
Alternative Lösung: Import mit dbms_xmlstore
XML-Dokument in Zieltabelle laden:
declare...v_xml clob;v_insctx dbms_xmlstore.ctxtype;v_rows number;
begin...v_insctx := dbms_xmlstore.newcontext('DEPT');dbms_xmlstore.clearupdatecolumnlist (v_insctx);dbms_xmlstore.setupdatecolumn (v_insctx, 'DEPTNO');dbms_xmlstore.setupdatecolumn (v_insctx, 'DNAME');...v_rows := dbms_xmlstore.insertxml (v_insctx, v_xml);dbms_xmlstore.closecontext (v_insctx);--commit;
end;/
Seite 59DOAG2006 XML-Schnittstellen zum Import und Export von Daten für eine Oracle DB – Ein Erfahrungsbericht
Alternative Lösung: Import mit dbms_xmlstore
XML-Dokument einfacher Struktur (1 Tabelle):
● Kaum Konfigurations-möglichkeiten imVergleich mit dbms_xmlquery
● Konventionen● ROW
● ROW NUM=
● Ggf. Stylesheettransformation
<ROWSET><ROW num="1">
<DEPTNO>10</DEPTNO><DNAME>ACCOUNTING</DNAME>...
</ROW><ROW num="2">
<DEPTNO>20</DEPTNO><DNAME>RESEARCH</DNAME>...
</ROW>...
</ROWSET>
Seite 60DOAG2006 XML-Schnittstellen zum Import und Export von Daten für eine Oracle DB – Ein Erfahrungsbericht
Alternative Lösung: Import mit dbms_xmlstore
XML-Dokument Hierarchischer Struktur (Master-Detail):
● Zieltabelle mit objektrelationaler Spalte (collection):
create table dept_emp(deptno number (2),dname varchar2 (14),…emp t_emp_liste)nested table emp store as nt_emp;
create or replace type t_emp_objas object (
empno number (4) ,ename varchar2 (10),...
);/
create or replace typet_emp_liste is table of t_emp_obj;/
Seite 61DOAG2006 XML-Schnittstellen zum Import und Export von Daten für eine Oracle DB – Ein Erfahrungsbericht
Alternative Lösung: Import mit dbms_xmlstore
XML-Dokument Hierarchischer Struktur (Master-Detail):
<ROWSET><ROW num="1">
<DEPTNO>10</DEPTNO>...<EMP>
<T_EMP_OBJ><EMPNO>7782</EMPNO>...
</T_EMP_OBJ>...
</EMP></ROW>...
</ROWSET>
Name der Collection-Spalte
Name der Object Types
Seite 62DOAG2006 XML-Schnittstellen zum Import und Export von Daten für eine Oracle DB – Ein Erfahrungsbericht
Alternative Lösung: Import mit dbms_xmlstore
XML-Dokument in Zieltabelle laden:
declare...v_xml clob;v_insctx dbms_xmlstore.ctxtype;v_rows number;
begin...v_insctx := dbms_xmlstore.newcontext('DEPT_EMP');dbms_xmlstore.clearupdatecolumnlist (v_insctx);dbms_xmlstore.setupdatecolumn (v_insctx, 'DEPTNO');dbms_xmlstore.setupdatecolumn (v_insctx, 'DNAME');...dbms_xmlstore.setupdatecolumn (v_insctx, 'EMP');v_rows := dbms_xmlstore.insertxml (v_insctx, v_xml);dbms_xmlstore.closecontext (v_insctx);--commit;
end;/
Name der Collection-Spalte
Seite 63DOAG2006 XML-Schnittstellen zum Import und Export von Daten für eine Oracle DB – Ein Erfahrungsbericht
Fazit
● Für einfache Import-Export-Aufgaben (1 xml-File <=> 1 Tabelle) sind die XDK-Toolsdbms_xmlquery und dbms_xmlstore hinreichend geeignet
● Bei hierarchischen Strukturen (Master-Detail) in einem XML-Dokument sind einige „Tricks“ erforderlich● Verwendung von Objekttypen bzw. Collections
● Ggf. Stylesheet-Transformationen
● Die Validierung und Transformation von XML-Dateienerfolgte über Java-Programme● Aufruf aus PL/SQL: Performanceprobleme
● Indirekter Aufruf über OS-Befehl aus PL/SQL
Seite 64DOAG2006 XML-Schnittstellen zum Import und Export von Daten für eine Oracle DB – Ein Erfahrungsbericht
Fragen und Antworten
F R A G E NF R A G E NA N T W O R T E NA N T W O R T E N