Tipps & Tricks: Dezember 2010 Umwandlung von relationalen

Tipps & Tricks: Dezember 2010
Bereich:
PL/SQL, XML
Erstellung:
12/2010 EF
Versionsinfo:
10g, 11g, XE
Letzte Überarbeitung:
12/2010 EF
Umwandlung von relationalen Daten in XML-Dateien
Um den Inhalt von Tabellen in XML-Files zu überführen, gibt es eine ganze Reihe von Möglichkeiten, u.a.
die Verwendung der SQL-XML(SQLX)-Funktionen wie XMLElement, XMLAgg, XMLForest etc.
das Package DBMS_XMLGen
die Erzeugung von XML-Daten über das sog. DBUriServlet
In diesem Monatstipp sollen die beiden letztgenannten Methoden kurz vorgestellt werden, und zwar anhand einer
Beispieltabelle, die im Gegensatz zu den üblichen Beispieltabellen aus den Oracle-Demo-Schemata einige
Stolpersteine für die XML-Verarbeitung in Oracle enthält.
Die SQLX-Funktionen und die Möglichkeiten der Kombination von XML-Erzeugung und gleichzeitiger
Transformation folgen in einem späteren Beitrag.
conn scott/tiger
set define off
DROP TABLE verliehen;
CREATE TABLE verliehen(
id
NUMBER(4),
kundenname VARCHAR2(40),
geräte_nr
NUMBER(6),
geräte_art VARCHAR2(40),
zahl
NUMBER(4),
verliehen_am DATE);
INSERT INTO verliehen VALUES(1, 'Müller & Söhne', 201, 'Häcksler', 1, sysdate - 20);
INSERT INTO verliehen VALUES(2, 'Susi Süß', 2290, 'Küchenmaschine', 1, sysdate - 12);
INSERT INTO verliehen VALUES(3, 'Hannes&Moritz', 15, 'Schürzen', 20, sysdate - 10);
INSERT INTO verliehen VALUES(4, 'Werner Bauer', 201, 'Mörser', 1, sysdate - 20);
INSERT INTO verliehen VALUES(5, 'Max "Morle" Mustermann', 121, 'Sofa', 1, sysdate - 8);
INSERT INTO verliehen VALUES(6, 'Lieschen Müller', 122, 'Vom Winde verweht', 1,
sysdate - 5);
Erzeugung von XML-Daten über das DBUriServlet
Die auf den ersten Blick unkomplizierteste Methode benutzt das sog. DBUriServlet, zu dem Carsten Czarski eine
schöne Einführung geschrieben hat.
Statt ein XML-File aus den relationalen Daten zu generieren, kann man den Tabelleninhalt einfach über den
Browser als XML abrufen. Voraussetzung ist, dass der sog. der HTTP-Listener aktiviert ist, den auch APEX nutzt
(Defaultport 8080).
Wenn das der Fall ist, kann man die Tabellen über folgende Eingabe in der Adressleiste des Browsers aufrufen
(User- und Tabellenname müssen groß geschrieben werden), nachdem man sich über Username und Passwort
authentifiziert hat.
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 1 von 5
http://<hostname>:<http-portnummer>/oradb/<USERNAME>/<TABELLENNAME>
z.B.
http://localhost:8080/oradb/SCOTT/EMP
Ausgabe im Browser:
<EMP>
<ROW>
<EMPNO>7369</EMPNO>
<ENAME>SMITH</ENAME>
<JOB>CLERK</JOB>
<MGR>7902</MGR>
<HIREDATE>17-DEC-80</HIREDATE>
<SAL>800</SAL>
<DEPTNO>10</DEPTNO>
</ROW>
....
</EMP>
Leider hat diese nette Funktionalität einen Haken. Sie ist per default auf utf-8 encoding eingestellt, was zu
Problemen bei der Zeichensatz-Konvertierung führt, wenn der Characterset der Datenbank z.B. Windows-1252
ist.
Weil in der Tabelle "verliehen" Spaltennamen und Spalteninhalte mit deutschen Umlauten vorkommen, erhält
man bei der Eingabe von http://localhost:8080/oradb/SCOTT/VERLIEHEN die Rückmeldung
XML-Verarbeitungsfehler: nicht wohlgeformt
Adresse: http://localhost:8080/oradb/SCOTT/VERLIEHEN
Zeile Nr. 5, Spalte 16:
<KUNDENNAME>M?ller & S?hne</KUNDENNAME>
---------------^
Ein einfacher Workaround ist die Darstellung des XML-Inhalts als Text. Das erreicht man mit dem Zusatz
?contenttype=text/plain.
http://localhost:8080/oradb/SCOTT/VERLIEHEN?contenttype=text/plain
=>
<?xml version="1.0"?>
<VERLIEHEN>
<ROW>
<ID>1</ID>
<KUNDENNAME>Müller & Söhne</KUNDENNAME>
<GERÄTE_NR>201</GERÄTE_NR>
<GERÄTE_ART>Häcksler</GERÄTE_ART>
<ZAHL>1</ZAHL>
<VERLIEHEN_AM>17-NOV-10</VERLIEHEN_AM>
</ROW>
...
</VERLIEHEN>
Die XML-Daten kann man direkt aus dem Browser als XML-Datei unter dem gewünschten Namen in einem
beliebigen Ordner speichern.
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 2 von 5
Das so erzeugte Dokument ist zunächst nicht wohlgeformt, weil man die Kodierung auf diesem einfachen Weg
nicht direkt mitgeben kann. Eine einfache Nachbearbeitung der XML-Datei durch den Zusatz
encoding="WINDOWS-1252" zum XML-Prolog ändert dies jedoch schnell.
Eine elegantere Methode ist die ebenfalls über das DB URI Servlet mögliche Transformation des XML-Files mit
Hilfe eines Stylesheets. Dazu müsste man aber zunächst ein passendes Stylesheet erstellen und innerhalb der in
Oracle integrierten XML-DB zur Verfügung stellen.
Verwendung des DBMS_XMLGen-Packages
DBMS_XMLGen ist der Nachfolger des veralteten Packages DBMS_XMLQuery. In der Oracle Version 10g ist es
noch performanter als die SQLX-Funktionen, in der Version 11gR2 haben letztere die Nase vorn.
Als Beispiel dient hier eine kleine Prozedur, an die man den Namen der als XML-File zu exportierenden Tabelle,
das Directory für den Export, die gewünschten Tag-Namen für das Wurzelelement und die Unterelemente und
optional den encoding-Zusatz als Parameter übergibt.
Erklärungen zu den verwendeten Funktionen und Prozeduren des Packages
Die Funktion newContext übernimmt einen Select und gibt den sogenannten ctxHandle (eine Nummer
zur "Weiterverfolgung") zurück
Die Funktion getXML generiert ein XML-Dokument aus der Abfrage, die durch den übergebenen
ctxHandle spezifiziert wurde. Der Rückgabewert ist ein CLOB.
Über die Prozedur setRowTag kann man den Tag-Namen für eine einzelne Zeile der Abfrage setzen
(Default wäre row) und
über die Prozedur setRowSetTag den Namen des Root-Elements des XML-Dokuments (Default wäre
rowset)
Die Prozedur closeContext schließt den "Kontext" und gibt alle Ressourcen frei.
CREATE OR REPLACE PROCEDURE export_xml(
p_tabname VARCHAR2
, p_directory VARCHAR2
, p_rowtag
VARCHAR2
, p_rowsettag VARCHAR2
, p_encoding VARCHAR2 DEFAULT 'UTF-8')
AS
l_string VARCHAR2(100);
l_ctx
DBMS_XMLGen.ctxHandle;
l_clob
CLOB;
BEGIN
l_string := 'SELECT * FROM '||p_tabname;
-- Erzeugung des neuen Kontexts
l_ctx := DBMS_XMLGen.newContext(l_string);
-- Namen für Unterelemente und Wurzelelement werden übergeben
DBMS_XMLGen.setRowTag(l_ctx, p_rowtag);
DBMS_XMLGen.setRowSetTag(l_ctx, p_rowsettag);
-- Das über getXML erzeugt XML-Dokument wird in einen CLOB zurückgeschrieben
l_clob := DBMS_XMLGen.getXML(l_ctx);
-- und die Ressourcen wieder freigegeben
DBMS_XMLGen.closeContext(l_ctx);
-- Die Grösse des CLOBs wird zur Kontrolle ausgegeben
DBMS_OUTPUT.PUT_LINE('Größe des CLOBs: '||DBMS_LOB.getlength (l_clob));
-- Anpassung der Kodierung
l_clob := REPLACE(l_clob, '<?xml version="1.0"?>', q'[<?xml version='1.0'
encoding=']'||p_encoding||q'['?>]');
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 3 von 5
-- Export des CLOBs
DBMS_XSLPROCESSOR.CLOB2FILE(l_clob, UPPER(p_directory), p_tabname||'.xml');
END;
Die Verwendung der ab 10g zur Verfügung stehenden Prozedur DBMS_XSLPROCESSOR.CLOB2FILE hat
gegenüber dem üblichen Export des CLOBs über DBMS_LOB.READ und UTL_FILE in einer Schleife den Vorteil,
dass keine Zeilenumbrüche innerhalb von Tag-Namen vorkommen können. In diesem Fall ist nämlich einiges an
Nacharbeit nötig, um die XML-Datei in einen wohlgeformten Zustand zu versetzen.
Das optionale "Einschmuggeln" der Kodierung über die REPLACE-Funktion ist nicht die eleganteste Lösung, aber
für kleinere XML-Dateien durchaus praktikabel. Sie hält auch beim Export größerer Dateien - wie im Beispiel hier
all_objects (28 MB) - den Vorgang nur unwesentlich auf.
Beispiele:
conn sys/<passwort> as sysdba
CREATE OR REPLACE DIRECTORY xmldir AS 'c:\temp';
GRANT READ, WRITE ON DIRECTORY xmldir TO scott;
conn scott/tiger
set timing on
BEGIN
export_xml(
p_tabname => 'all_objects',
p_directory => 'xmldir',
p_rowtag => 'details',
p_rowsettag =>'alle_objekte');
END;
/
Größe des CLOBs: 28961278
PL/SQL-Prozedur erfolgreich abgeschlossen.
Abgelaufen: 00:00:32.31 (auf 11gR2, ohne die Übergabe des Kodierungsparameters und
Ersetzung des Prologs)
Abgelaufen: 00:00:32.92 (auf 11gR2, mit Übergabe des Kodierungsparameters und
Ersetzung des Prologs)
BEGIN
export_xml(
p_tabname => 'verliehen',
p_directory => 'xmldir',
p_rowtag => 'Details',
p_rowsettag =>'Verleih-Daten',
p_encoding => 'Windows-1252');
END;
/
Größe des CLOBs: 1283
PL/SQL-Prozedur erfolgreich abgeschlossen.
Abgelaufen: 00:00:00.06
Wenn Sie noch mehr über die Möglichkeiten der XML-Bearbeitung erfahren wollen, besuchen Sie doch einfach
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 4 von 5
Wenn Sie noch mehr über die Möglichkeiten der XML-Bearbeitung erfahren wollen, besuchen Sie doch einfach
unseren XML- oder PL/SQL II-Kurs.
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 5 von 5