0
(Noch nicht bewertet)
27. April 2016
DataOne Blog

Generische Codegenerierung mit Oracle 1: Konkatenation mit WM_CONCAT

Die nachfolgende Blogserie ermöglicht Ihnen einen Überblick über verschiedene Möglichkeiten, der Erzeugung generischer Statements mit Oracle. Hierbei werden verschiedene Werkzeuge vorgestellt.


Hintergrund

In unserem letzten Blogbeitrag nutzte mein Kollege Yannic Klein die LISTAGG-Function um einen bekannten Bug des Oracle Warehouse Builders 11g R2, im Zusammenhang mit der Oracle 12c Datenbank, effizient zu umgehen.
Im Anschluss an diesen Beitrag wollen wir uns einen Überblick über ein paar Möglichkeiten verschaffen, generische SQL-Statements zu erzeugen.

Die in dem Blogbeitrag verwendete Funktion LISTAGG ist eine analytische Funktion, die mit Oracle 11g R2 eingeführt wurde. Mittels LISTAGG lassen sich Werte, die zeilenweise in einem Resultset angeordnet sind, nach bestimmten Regeln konkatenieren und in einer Zeile ausgeben. Vor der Verfügbarkeit dieser Funktion musste häufig noch eine hierarchische Abfrage, ein rechenintensiver Cursor, eine selbst geschriebene Funktion oder die Funktion WM_CONCAT benutzt werden.

WM_CONCAT

Zu WM_CONCAT sollte erwähnt werden, dass sie eine nicht dokumentierte und in Oracle 12c vollständig entfernte User Defined Function ist. Da das Release 11g R2 noch weit verbreitet ist und WM_CONCAT zwar ab 12c nicht mehr enthalten ist, dafür aber recht unkompliziert in seiner Anwendung ist, widmen wir uns ihr der Vollständigkeit halber in unserem ersten Blogbeitrag dieser Reihe.

Bei WM_CONCAT handelt es sich um eine Aggregatfunktion, sie liefert also für mehrere Eingabewerte einen aggregierten Wert zurück. Somit kann sie in einem SQL-Statement, dass auch nicht aggregierte Felder liefert, nur zusammen mit einem Group By genutzt werden. WM_CONCAT erwartet als Übergabeparameter eine Expression die einen VARCHAR2-Wert liefert. WM_CONCAT selbst gibt einenCLOB zurück.
Beispiele mit WM_CONCAT

Folgendes Beispiel nutzt das Beispielschema HR, das von Oracle bei der Installation bereitgestellt wird um WM_CONCAT zu demonstrieren. Hierbei werden alle Nachnamen aus einer bestimmten Jobkategorie zusammengefasst und in einem Feld ausgegeben:

Generische Codegenerierung mit Oracle 1 Konkatenation mit WM_CONCAT 1

Das Ergebnis dieser Abfrage sieht in Auszügen wie folgt aus:

Generische Codegenerierung mit Oracle 1 Konkatenation mit WM_CONCAT 2

Da es sich bei dem Übergabeparameter für WM_CONCAT um eine Expression handelt, kann hier natürlich jeder Ausdruck stehen, der einen VARCHAR2-Wert zurückgibt. Was uns eine gewisse Flexibilität dabei erlaubt, wie die zu konkatenierenden Werte aufzubereiten sind.

Als Einschränkung von WM_CONCAT sollte noch erwähnt werden, dass der Einsatzbereich limitiert ist. So kann man nicht jedes beliebige Statement erzeugen, da z. B. der Wertetrenner nicht frei wählbar und immer ein , (Komma) ist. Auch die Reihenfolge, in der die konkatenierten Werte zusammengefügt werden, kann nicht frei gewählt werden. Sind solche Anforderungen gegeben, muss ein anderer Weg gefunden werden um die Werte aufzubereiten. Ab Oracle 11g R2 bietet LISTAGG all diese Optionen von Haus aus.

CREATE-Statements mit WM_CONCAT generieren

Nützlich sind diese konkatenierungs Funktionen um z. B. generische SQL-Statements zu erzeugen. In unserem Fall wollen wir uns mal ansehen, wie wir auf einfache Art und Weise Create-Statements für Tabellen erzeugen können, äquivalent kann man das Beispiel auch auf Views anwenden.

Das folgende Statement zeigt, wie mit WM_CONCAT aus einer gegebenen Menge von Metadaten aus dem Oracle Metadatenkatalog, schnell und einfach Statements generiert werden können. So kann man effizient z. B. Datentypen für Attribute, die zuvor in der Modellierung falsch designet wurden, ersetzen.

 Generische Codegenerierung mit Oracle 1 Konkatenation mit WM_CONCAT 3

Das Ergebnis zu der Abfrage sieht auszugsweise wie folgt aus:

Generische Codegenerierung mit Oracle 1 Konkatenation mit WM_CONCAT 4

 

Die Spalte STMNT kann markiert und mit Copy and Paste in einen beliebiges SQL-Verarbeitungstool überführt werden und dort einzeln oder als Skript ausgeführt werden.

In unserem Nächsten Blog möchten wir uns LISTAGG widmen.

Schreiben Sie einen Kommentar

Ihre E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.