21. Dezember 2015

2015 geht zu Ende ...

Nun geht es zu Ende, das Jahr 2015. Es schon einiges los für den Entwickler, der mit SQL, PL/SQL und der Oracle-Datenbank arbeitet. Highlights waren mit Sicherheit das Release von APEX 5.0 im April - das dis dato umfangreichste und beste APEX-Release. Für Node.js gibt es seit Januar 2015 einen "offiziellen" Treiber - über node-oracledb habe ich in einem Blog-Posting berichtet. Seit August ist der Treiber production.

In einem Youtube-Video habe ich das Jahr 2015 für euch zusammengefasse - wenn Ihr Lust habt, schaut es euch das Dezember-Video der Reihe Oracle Developer Monthly einfach an. Waren euch noch alle Neuerungen präsent ...?

Persönlich habe ich mich sehr über die Auszeichnungen DOAG Botschafter 2015 auf der DOAG2015 und den Community Award auf der APEX Connect 2015 gefreut. Es macht einfach Spaß, mit einer so aktiven Community zusammen zu arbeiten. Und ich habe keine Zweifel daran, dass es auch 2016 und danach Spaß machen wird.

Und was erwartet und 2016 ... man sieht jetzt schon eine Menge ...

Die DOAG wird Ende April 2016 nochmals eine APEX Connect veranstalten - das Programm könnt Ihr euch schon ansehen. Es werden wieder hochkarätige Sprecher aus der deutschsprachigen und internationalen Community dabeisein. Dass das APEX Entwicklerteam wieder vertreten sein wird, versteht sich ja schon fast von selbst.

Doch nicht nur die APEX Connect ist für Entwickler interessant. Wer Interesse an einem etwas interaktiveren Format als eine Konferenz hat, sollte sich mal das DOAG DevCamp, welches im Februar stattfindet, näher ansehen. Wie bei einem Barcamp üblich, gibt es keine im Vorfeld festgelegte Vortragsagenda. Die Agenda wird am ersten Tag von den Teilnehmern selbst zusammengestellt. Ein Barcamp ist ein sehr offenes Format; in den Diskussionen kann man unglaublich viele Ideen aufnehmen und wird auch mit Themen und Ansätzen vertraut, die man vorher nicht auf dem Radar hatte ... ich kann das nur empfehlen.

Beim DevCamp sind Eigenschaften gefragt, die auf Konferenzen seltener in Erscheinung treten: Spontaneität und Ehrlichkeit. Denn alle Sessions werden zu Tagesbeginn von den Teilnehmern selbst vorgeschlagen und gestaltet. Ihr werdet überrascht zu sehen, was dabei rauskommt.

Nicht vergessen möchte ich die Reihe Moderne Anwendungsentwicklung im Unternehmen, die ich, gemeinsam mit einigen Kollegen von Oracle und aus der Community, ab Januar 2016 durchführen werde. Ab dem 19. Januar wird jede Woche ein Video zu einem "modernen" Entwicklerthema wie JSON, Node.js, HTML5, NoSQL oder REST erscheinen. Das ist die Gelegenheit, mit diesen Themen und wie man sie im Unternehmen einsetzen kann, vertraut zu werden. Schaut einfach mal rein.

This blog is about events in german language and therefore in German only.

3. Dezember 2015

Datenmuster finden: SQL Pattern Matching in Oracle12c

Das heutige Blog-Posting ist nicht aus meiner Feder, vielmehr war mein Kollege Rainer Willems so nett, ein Posting zum neuen Oracle12c-Feature SQL Pattern Matching zu schreiben. Meine Aufgabe ist nun lediglich, das Posting auf dem Blog zu veröffentlichen, was ich hiermit gerne tue.
Heute wollen wir uns mit SQL Pattern Matching beschäftigen. Mit Oracle 10g wurden reguläre Ausdrücke in der Datenbank eingeführt, um nach Mustern in Zeichenketten zu suchen. SQL Pattern Matching bieten nun die Möglichkeit, auch in über Datenströme deklarativ nach Mustern zu suchen. Im folgenden sehen wir ein kleines Beispiel und entwickeln anhand eines Aktienkursverlaufes Suchen nach Mustern darin. Beginnen wir mit einer Tabelle mit Beispieldaten.
CREATE TABLE TICKER  ( 
                   TSTAMP DATE, 
                   SYMBOL VARCHAR2(5), 
            PRICE  NUMBER  );

Insert into TICKER (TSTAMP,SYMBOL,PRICE) values (to_date('01-JAN-14','DD-MON-RR'),'abc',13);
Insert into TICKER (TSTAMP,SYMBOL,PRICE) values (to_date('02-JAN-14','DD-MON-RR'),'abc',13.4);
Insert into TICKER (TSTAMP,SYMBOL,PRICE) values (to_date('03-JAN-14','DD-MON-RR'),'abc',13.8);
Insert into TICKER (TSTAMP,SYMBOL,PRICE) values (to_date('04-JAN-14','DD-MON-RR'),'abc',13.1);
Insert into TICKER (TSTAMP,SYMBOL,PRICE) values (to_date('05-JAN-14','DD-MON-RR'),'abc',13.3);
Insert into TICKER (TSTAMP,SYMBOL,PRICE) values (to_date('06-JAN-14','DD-MON-RR'),'abc',13.9);
Insert into TICKER (TSTAMP,SYMBOL,PRICE) values (to_date('07-JAN-14','DD-MON-RR'),'abc',14.1);
Insert into TICKER (TSTAMP,SYMBOL,PRICE) values (to_date('08-JAN-14','DD-MON-RR'),'abc',14.7);
Insert into TICKER (TSTAMP,SYMBOL,PRICE) values (to_date('09-JAN-14','DD-MON-RR'),'abc',14.7);
Insert into TICKER (TSTAMP,SYMBOL,PRICE) values (to_date('10-JAN-14','DD-MON-RR'),'abc',14.6);
Insert into TICKER (TSTAMP,SYMBOL,PRICE) values (to_date('11-JAN-14','DD-MON-RR'),'abc',14.8);
Insert into TICKER (TSTAMP,SYMBOL,PRICE) values (to_date('12-JAN-14','DD-MON-RR'),'abc',14.9);
Insert into TICKER (TSTAMP,SYMBOL,PRICE) values (to_date('13-JAN-14','DD-MON-RR'),'abc',14.6);
Insert into TICKER (TSTAMP,SYMBOL,PRICE) values (to_date('14-JAN-14','DD-MON-RR'),'abc',14.5);
Insert into TICKER (TSTAMP,SYMBOL,PRICE) values (to_date('15-JAN-14','DD-MON-RR'),'abc',14.7);
Insert into TICKER (TSTAMP,SYMBOL,PRICE) values (to_date('16-JAN-14','DD-MON-RR'),'abc',14.9);
Insert into TICKER (TSTAMP,SYMBOL,PRICE) values (to_date('17-JAN-14','DD-MON-RR'),'abc',14.3);
Insert into TICKER (TSTAMP,SYMBOL,PRICE) values (to_date('18-JAN-14','DD-MON-RR'),'abc',14.4);
Insert into TICKER (TSTAMP,SYMBOL,PRICE) values (to_date('19-JAN-14','DD-MON-RR'),'abc',14.6);
Insert into TICKER (TSTAMP,SYMBOL,PRICE) values (to_date('01-JAN-14','DD-MON-RR'),'xyz',17);
Insert into TICKER (TSTAMP,SYMBOL,PRICE) values (to_date('02-JAN-14','DD-MON-RR'),'xyz',16.5);
Insert into TICKER (TSTAMP,SYMBOL,PRICE) values (to_date('03-JAN-14','DD-MON-RR'),'xyz',16.9);
Insert into TICKER (TSTAMP,SYMBOL,PRICE) values (to_date('04-JAN-14','DD-MON-RR'),'xyz',16.5);
Insert into TICKER (TSTAMP,SYMBOL,PRICE) values (to_date('05-JAN-14','DD-MON-RR'),'xyz',16.3);
/
Die Tabelle enthält nun Aktienkurse von 2 Symbolen. Als erstes wollen wir ein simples SQL Pattern Matching Statement verwenden, welches auf den ersten Blick keinen Mehrwert zu einer normalen Abfrage liefert:
SELECT * FROM ticker 
       MATCH_RECOGNIZE ( 
           PARTITION BY symbol 
           ORDER BY tstamp 
           MEASURES e.tstamp as st, e.symbol as s, e.price as p 
           ONE ROW PER MATCH 
           PATTERN (e) 
             DEFINE e AS price=price);
  • MATCH_RECOGNIZE ist das Schlüsselwort für ein SQL Pattern Matching Statement.
  • PARTITION BY gibt an, nach welchem Attribut die Daten zur Analyse (logisch) partitioniert werden sollen. Pattern Matching findet stets innerhalb einer Partition statt und gefundene Muster gehen niemals über mehrere Partitionen. Hier ist es das Stock-Symbol, da wir Ausagen über die Entwicklung eines solchen treffen wollen.
  • ORDER BY legt die Reihenfolge der Datensätze fest, in der wir nach einem Muster suchen möchten. Dies ist hier sonnvollerweise die Zeitachse.
  • MEASURES gibt an, welche Informationen zurückgegeben werden, also von der SQL Pattern Matching-Klausel an die umgebende SQL-Query zurückgegeben werden. Der Alias e referenziert hierbei einen Teil des Musters, der ...
  • ... im Bereich PATTERN definiert wird. Diese Aliasnamen sind frei wählbar und legen zunächst fest, aus welchen Abschnitten ein Muster besteht.
  • DEFINE legt fest, was konkret unter den benannten Abschnitten zu verstehen ist. Ist ein in PATTERN verwendeter Abschnitt nicht unter DEFINE definiert, so matcht dieser alle Zeilen.
  • ONE ROW PER MATCH (Default) bestimmt, dass pro vollständigen Treffer nur eine Zeile zurückgegeben werden soll.
SYMBOL ST  S P
abc 01-JAN-14 abc 13
abc 02-JAN-14 abc 13.4
abc 03-JAN-14 abc 13.8
abc 04-JAN-14 abc 13.1
abc 05-JAN-14 abc 13.3
abc 06-JAN-14 abc 13.9
abc 07-JAN-14 abc 14.1
abc 08-JAN-14 abc 14.7
abc 09-JAN-14 abc 14.7
abc 10-JAN-14 abc 14.6
abc 11-JAN-14 abc 14.8
abc 12-JAN-14 abc 14.9
abc 13-JAN-14 abc 14.6
abc 14-JAN-14 abc 14.5
abc 15-JAN-14 abc 14.7
abc 16-JAN-14 abc 14.9
abc 17-JAN-14 abc 14.3
abc 18-JAN-14 abc 14.4
abc 19-JAN-14 abc 14.6
xyz 01-JAN-14 xyz 17
xyz 02-JAN-14 xyz 16.5
xyz 03-JAN-14 xyz 16.9
xyz 04-JAN-14 xyz 16.5
xyz 05-JAN-14 xyz 16.3

Im folgenden hängen wir jeweils noch einen Filter auf das Symbol an, um nur das Symbol abc zu betrachten. Die Grafik zum Aktienkurs sieht dann so aus:
In diesem Chart möchten wir nun ein V-Muster suchen, also eine fallende Phase gefolgt von einer steigenden Phase. Die Grafik zeigt deutlich vier solcher Muster im Chart. Als ersten Query-Versuch starten wir mit ...
SELECT * FROM ticker
   MATCH_RECOGNIZE (
      PARTITION BY symbol 
      ORDER BY tstamp 
      MEASURES strt.tstamp startdate, LAST(up.tstamp) as enddate, strt.price startprice, LAST(up.price) as endprice
      ONE ROW PER MATCH 
      PATTERN (strt down+ up+) 
         DEFINE down AS price < prev(price), 
                up AS price > prev(price))
where symbol='abc';  
Das verwendete Pattern beginnt mit dem Alias strt; dieser ist innerhalb von DEFINE nicht näher definiert, matcht per Definition also alle Tabellenzeilen. Das bedeutet, dass ein Match prinzipiell überall beginnen kann. Gefolgt wird strt vom Abschnitt down und danach von up. Für "up" ist in DEFINE festgelegt, dass der vorherige Kurs kleiner sein muss als der der aktuellen Zeile, für "down" muss es entsprechend umgekehrt sein. Das Pluszeichen dahinter (+) ist ein Quantifizierer; damit lassen sich Angaben machen, wie oft der Abschnitt vorkommen muss. Folgende Möglichkeiten gibt es:
*      kein, ein oder mehrmaliges Vorkommen
+      1 oder mehrmaliges Vorkommen
?      kein oder genau ein Vorkommen
{n}    genau n Vorkommen
{n,}   n oder mehr Vorkommen
{n,m}  n bis m Vorkommen
{,m}   maximal m Vorkommen
Zusätzlich lässt sich noch ein Fragezeichen (?) anhängen, damit werden die Quantifizierer vom Greedy- in den Reluctant Modus geschaltet. Greedy bedeutet, dass der Muster-Abschnitt so viele Input-Daten heranzieht wie möglich - wenn alle Input-Daten auf den ersten Abschnitt passen; so werden auch alle herangezogen. Reluctant bedeutet dagegen, dass gerade soviele Daten herangezogen werden, wie zur Erfüllung der Bedingung minestens nötig sind. Sobald der Abschnitt gerade erfüllt ist, werden die danach folgenden Abschnitte geprüft. Mehr zu diesen Unterschieden findet sich in der Oracle-Dokumentation. Das Ergebniss der Abfrage ist wie folgt:
SYMBOL STARTDATE ENDDATE  STARTPRICE ENDPRICE
abc 03-JAN-14 08-JAN-14 13.8  14.7
abc 09-JAN-14 12-JAN-14 14.7  14.9
abc 13-JAN-14 16-JAN-14 14.6  14.9
Wenn wir das mit dem Chart vergleichen, sind wir offensichtlich noch nicht so ganz in Einklang mit unserem Ziel.
Das dritte V-Muster startet offensichtlich schon am 12. und nicht erst am 13. Ein vierter Treffer (16. - 19.) erscheint gar nicht in der Treffermenge. Dies liegt daran, dass wir nicht angegeben haben, wo, nach einem gefunden Treffer, weiter gesucht werden soll.
Die Default-Einstellung hierfür ist AFTER MATCH SKIP TO PAST LAST ROW. Dies erklärt das Ergebnis: Nach dem zweiten Treffer (endet am 12.) wird erst ab dem 13. weiter gesucht und der 12. bleibt unberücksichtigt. Ebenso wird nach dem dritten Treffer erst ab dem 17. weiter gesucht und somit gar kein Muster mehr gefunden. Diese Einstellung kann verändert werden:
  • AFTER MATCH SKIP TO NEXT ROW - Resume pattern matching at the row after the first row of the current match.
  • AFTER MATCH SKIP PAST LAST ROW - Resume pattern matching at the next row after the last row of the current match.
  • AFTER MATCH SKIP TO FIRST pattern_variable - Resume pattern matching at the first row that is mapped to the pattern variable.
  • AFTER MATCH SKIP TO LAST pattern_variable - Resume pattern matching at the last row that is mapped to the pattern variable.
  • AFTER MATCH SKIP TO pattern_variable - The same as AFTER MATCH SKIP TO LAST pattern_variable.
Um also den letzten Wert eines Treffers auch als möglichen ersten Wert eines weiteren Treffers zu berücksichtigen, verwenden wir AFTER MATCH SKIP TO LAST pattern_variable und somit die folgende Abfrage:
SELECT * FROM ticker
   MATCH_RECOGNIZE (
      PARTITION BY symbol 
      ORDER BY tstamp 
      MEASURES strt.tstamp startdate, LAST(up.tstamp) as enddate, strt.price startprice, LAST(up.price) as endprice
      ONE ROW PER MATCH 
      AFTER MATCH SKIP TO LAST up
      PATTERN (strt down+ up+) 
         DEFINE down AS price < prev(price), 
                up AS price > prev(price))
where symbol='abc';
Das Ergebnis dieser Abfrage sieht wie folgt aus:
SYMBOL STARTDATE ENDDATE  STARTPRICE ENDPRICE
abc 03-JAN-14 08-JAN-14 13.8  14.7
abc 09-JAN-14 12-JAN-14 14.7  14.9
abc 12-JAN-14 16-JAN-14 14.9  14.9
abc 16-JAN-14 19-JAN-14 14.9  14.6
und gibt uns nun also die richtigen Werte zurück
Als nächstes lassen wir uns nun in der Anzeige auch Teilergebnisse anzeigen, so dass schön dargestellt wird, wie ein Ergebnis entsteht. Hierzu ersetzen wir das ONE ROW PER MATCH in der Abfrage mit einem ALL ROWS PER MATCH.
SELECT * FROM ticker 
   MATCH_RECOGNIZE (
      PARTITION BY symbol 
      ORDER BY tstamp 
      MEASURES strt.tstamp startdate, LAST(up.tstamp) AS enddate, strt.price startprice, LAST(up.price) AS endprice
      ALL ROWS PER MATCH 
      AFTER MATCH SKIP TO LAST up
      PATTERN (strt down+ up+) 
         DEFINE down AS price < prev(price), 
                up AS price > prev(price))
where symbol='abc'; 
Das Ergebnis ist wie folgt:
SYMBOL TSTAMP  STARTDATE ENDDATE  STARTPRICE ENDPRICE PRICE
abc 03-JAN-14 03-JAN-14 -  13.8  -  13.8
abc 04-JAN-14 03-JAN-14 -  13.8  -  13.1
abc 05-JAN-14 03-JAN-14 05-JAN-14 13.8  13.3  13.3
abc 06-JAN-14 03-JAN-14 06-JAN-14 13.8  13.9  13.9
abc 07-JAN-14 03-JAN-14 07-JAN-14 13.8  14.1  14.1
abc 08-JAN-14 03-JAN-14 08-JAN-14 13.8  14.7  14.7
abc 09-JAN-14 09-JAN-14 -  14.7  -  14.7
abc 10-JAN-14 09-JAN-14 -  14.7  -  14.6
abc 11-JAN-14 09-JAN-14 11-JAN-14 14.7  14.8  14.8
abc 12-JAN-14 09-JAN-14 12-JAN-14 14.7  14.9  14.9
abc 12-JAN-14 12-JAN-14 -  14.9  -  14.9
abc 13-JAN-14 12-JAN-14 -  14.9  -  14.6
abc 14-JAN-14 12-JAN-14 -  14.9  -  14.5
abc 15-JAN-14 12-JAN-14 15-JAN-14 14.9  14.7  14.7
abc 16-JAN-14 12-JAN-14 16-JAN-14 14.9  14.9  14.9
abc 16-JAN-14 16-JAN-14 -  14.9  -  14.9
abc 17-JAN-14 16-JAN-14 -  14.9  -  14.3
abc 18-JAN-14 16-JAN-14 18-JAN-14 14.9  14.4  14.4
abc 19-JAN-14 16-JAN-14 19-JAN-14 14.9  14.6  14.6
Ist das ENDDATE in der Ausgabe noch leer, haben wir bisher noch keinen gültigen Treffer entdeckt. In der dritten Zeile sehen wir den ersten Treffer, welcher sukzessive verlängert wird, bis das Muster komplett ist. Sehr schön lässt sich dies nachvollziehen, wenn man sich anzeigen läßt, in welchem Teil des Musters man gerade ist (CLASSIFIER), und der wievielte Treffer (MATCH_NUMBER) gerade betrachtet wird. Zusätzlich lassen wir uns UNMATCHED ROWS anzeigen.
SELECT * FROM ticker
   MATCH_RECOGNIZE (
      PARTITION BY symbol 
      ORDER BY tstamp 
      MEASURES strt.tstamp startdate, LAST(up.tstamp) AS enddate, CLASSIFIER() AS c, MATCH_NUMBER() AS m 
      ALL ROWS PER MATCH WITH UNMATCHED ROWS
      AFTER MATCH SKIP TO LAST up
      PATTERN (strt down+ up+) 
         DEFINE down AS price < prev(price), 
                up AS price > prev(price))
where symbol='abc'; 
Die Ausgabe ist nun leicht verständlich und überschauber:
SYMBOL TSTAMP  STARTDATE ENDDATE  C M PRICE
abc 01-JAN-14 - - -  - 13
abc 02-JAN-14 - - -  - 13.4
abc 03-JAN-14 03-JAN-14 -  STRT 1 13.8
abc 04-JAN-14 03-JAN-14 -  DOWN 1 13.1
abc 05-JAN-14 03-JAN-14 05-JAN-14 UP 1 13.3
abc 06-JAN-14 03-JAN-14 06-JAN-14 UP 1 13.9
abc 07-JAN-14 03-JAN-14 07-JAN-14 UP 1 14.1
abc 08-JAN-14 03-JAN-14 08-JAN-14 UP 1 14.7
abc 08-JAN-14 - - -  - 14.7
abc 09-JAN-14 09-JAN-14 -  STRT 2 14.7
abc 10-JAN-14 09-JAN-14 -  DOWN 2 14.6
abc 11-JAN-14 09-JAN-14 11-JAN-14 UP 2 14.8
abc 12-JAN-14 09-JAN-14 12-JAN-14 UP 2 14.9
abc 12-JAN-14 12-JAN-14 -  STRT 3 14.9
abc 13-JAN-14 12-JAN-14 -  DOWN 3 14.6
abc 14-JAN-14 12-JAN-14 -  DOWN 3 14.5
abc 15-JAN-14 12-JAN-14 15-JAN-14 UP 3 14.7
abc 16-JAN-14 12-JAN-14 16-JAN-14 UP 3 14.9
abc 16-JAN-14 16-JAN-14 -  STRT 4 14.9
abc 17-JAN-14 16-JAN-14 -  DOWN 4 14.3
abc 18-JAN-14 16-JAN-14 18-JAN-14 UP 4 14.4
abc 19-JAN-14 16-JAN-14 19-JAN-14 UP 4 14.6
abc 19-JAN-14 - - - - 14.6
Die Spalte CLASSIFIER (C) zeigt das Teilmuster unseres Patterns an. Ist die Spalte leer, befinden wir uns nicht in einem möglichen Treffer. Ab dem Wert STRT könnte sich ein Treffer entwickeln, gefunden ist er sobald ENDDATE einen Wert hat und zumindest ein "up" Vorkommen gefunden wurde. Zum Abschluß suchen wir noch nach einem "W-Muster". Hierfür verwenden wir DOWN und UP einfach doppelt:
SELECT * FROM ticker 
   MATCH_RECOGNIZE (
      PARTITION BY symbol 
      ORDER BY tstamp 
      MEASURES strt.tstamp startdate, LAST(up.tstamp) AS enddate
      ONE ROW PER MATCH
      AFTER MATCH SKIP TO LAST up
      PATTERN (strt down+ up+ down+ up+) 
         DEFINE down AS price < prev(price), 
                up AS price > prev(price))
where symbol='abc';   
Heraus kommen folgende Daten
SYMBOL STARTDATE ENDDATE
abc 09-JAN-14 16-JAN-14
Allerdings passt das Ergebnis nicht ganz zum Chart ...
Das erste W-Muster wurde zwar gefunden, nicht aber das zweite. Die Einstellung AFTER MATCH SKIP TO LAST up bedeutet, dass nach Finden des ersten Musters ab dem 16. weitergesucht wird - und ab da gibt es kein Muster mehr. Die Weitersuche muss ab dem 12. erfolgen; "W"-Muster sollen sich überlappen können. Das ist sehr einfach zu erreichen; anstelle der doppelt verwendeten Abschnitte UP und DOWN benennen wir die einzelnen Abschnitte nun explizit. In der Klausel AFTER MATCH SKIP kann mit UP1 dann explizit das Ende der ersten "up"-Phase angegeben werden.
SELECT * FROM ticker 
   MATCH_RECOGNIZE (
      PARTITION BY symbol 
      ORDER BY tstamp 
      MEASURES strt.tstamp startdate, LAST(up2.tstamp) AS enddate
      ONE ROW PER MATCH
      AFTER MATCH SKIP TO LAST up1
      PATTERN (strt down+ up1+ down+ up2+) 
         DEFINE down AS price < prev(price), 
                up1 AS price > prev(price),
                up2 AS price > prev(price))
where symbol='abc'; 
Und schon haben wir das gewünschte Ergebnis
SYMBOL STARTDATE ENDDATE
abc 09-JAN-14 16-JAN-14
abc 12-JAN-14 19-JAN-14
Wer dieses Beispiel nachspielen und ein paar Varianten ausprobieren möchte, kann dies auf Oracle Live SQL tun: https://livesql.oracle.com/apex/livesql/s/cbdvwws7f8mddrph8wrx9sh87. Man kann das komplette Skript dort laufen lassen oder herunterladen und in der eigenen Datenbankumgebung starten.
Today's blog posting has not been authored by me, it is from my colleague Rainer Willems. He will introduce SQL Pattern Matching, the new Oracle12c feature. My task was simply to publich Rainer's posting on my blog - and I'm happy to do this right now.
Some time ago, with Oracle10g. regular expressions (regex) have been introduced to the Oracle Database. These allow to look up patterns within Strings (VARCHAR2). The new feature, SQL Pattern Matching is also about searching for patterns, but within data streams or simpler: within a set of table rows. Let's start with a simple example: The following table contains stock quotes.
CREATE TABLE TICKER  ( 
                   TSTAMP DATE, 
                   SYMBOL VARCHAR2(5), 
            PRICE  NUMBER  );

Insert into TICKER (TSTAMP,SYMBOL,PRICE) values (to_date('01-JAN-14','DD-MON-RR'),'abc',13);
Insert into TICKER (TSTAMP,SYMBOL,PRICE) values (to_date('02-JAN-14','DD-MON-RR'),'abc',13.4);
Insert into TICKER (TSTAMP,SYMBOL,PRICE) values (to_date('03-JAN-14','DD-MON-RR'),'abc',13.8);
Insert into TICKER (TSTAMP,SYMBOL,PRICE) values (to_date('04-JAN-14','DD-MON-RR'),'abc',13.1);
Insert into TICKER (TSTAMP,SYMBOL,PRICE) values (to_date('05-JAN-14','DD-MON-RR'),'abc',13.3);
Insert into TICKER (TSTAMP,SYMBOL,PRICE) values (to_date('06-JAN-14','DD-MON-RR'),'abc',13.9);
Insert into TICKER (TSTAMP,SYMBOL,PRICE) values (to_date('07-JAN-14','DD-MON-RR'),'abc',14.1);
Insert into TICKER (TSTAMP,SYMBOL,PRICE) values (to_date('08-JAN-14','DD-MON-RR'),'abc',14.7);
Insert into TICKER (TSTAMP,SYMBOL,PRICE) values (to_date('09-JAN-14','DD-MON-RR'),'abc',14.7);
Insert into TICKER (TSTAMP,SYMBOL,PRICE) values (to_date('10-JAN-14','DD-MON-RR'),'abc',14.6);
Insert into TICKER (TSTAMP,SYMBOL,PRICE) values (to_date('11-JAN-14','DD-MON-RR'),'abc',14.8);
Insert into TICKER (TSTAMP,SYMBOL,PRICE) values (to_date('12-JAN-14','DD-MON-RR'),'abc',14.9);
Insert into TICKER (TSTAMP,SYMBOL,PRICE) values (to_date('13-JAN-14','DD-MON-RR'),'abc',14.6);
Insert into TICKER (TSTAMP,SYMBOL,PRICE) values (to_date('14-JAN-14','DD-MON-RR'),'abc',14.5);
Insert into TICKER (TSTAMP,SYMBOL,PRICE) values (to_date('15-JAN-14','DD-MON-RR'),'abc',14.7);
Insert into TICKER (TSTAMP,SYMBOL,PRICE) values (to_date('16-JAN-14','DD-MON-RR'),'abc',14.9);
Insert into TICKER (TSTAMP,SYMBOL,PRICE) values (to_date('17-JAN-14','DD-MON-RR'),'abc',14.3);
Insert into TICKER (TSTAMP,SYMBOL,PRICE) values (to_date('18-JAN-14','DD-MON-RR'),'abc',14.4);
Insert into TICKER (TSTAMP,SYMBOL,PRICE) values (to_date('19-JAN-14','DD-MON-RR'),'abc',14.6);
Insert into TICKER (TSTAMP,SYMBOL,PRICE) values (to_date('01-JAN-14','DD-MON-RR'),'xyz',17);
Insert into TICKER (TSTAMP,SYMBOL,PRICE) values (to_date('02-JAN-14','DD-MON-RR'),'xyz',16.5);
Insert into TICKER (TSTAMP,SYMBOL,PRICE) values (to_date('03-JAN-14','DD-MON-RR'),'xyz',16.9);
Insert into TICKER (TSTAMP,SYMBOL,PRICE) values (to_date('04-JAN-14','DD-MON-RR'),'xyz',16.5);
Insert into TICKER (TSTAMP,SYMBOL,PRICE) values (to_date('05-JAN-14','DD-MON-RR'),'xyz',16.3);
/
As you can see, the table contains data for two stock symbols. The first SQL pattern matching query sill not provide any additional value, but it's nice to introduce the syntax.
SELECT * FROM ticker 
       MATCH_RECOGNIZE ( 
           PARTITION BY symbol 
           ORDER BY tstamp 
           MEASURES e.tstamp as st, e.symbol as s, e.price as p 
           ONE ROW PER MATCH 
           PATTERN (e) 
             DEFINE e AS price=price);

  • MATCH_RECOGNIZE is the keyword to start the SQL pattern matching syntax.
  • PARTITION BY determines the column which is being used for logical partitioning of the data. Pattern matching is always being performed within a partition and a found pattern will never span multiple partitions. In this example, we'll use the stock symbol.
  • ORDER BY determines data ordering. It's obvious that ordering is an essential requirement to do pattern matching. In this example, the timestamp will be used.
  • MEASURES specify the columns which the Pattern Matching clause will return to the outer SQL query. The alias e references the pattern declaration later on.
  • PATTERN contains the pattern declaration. The simple usage of an alias like in this example means always true, so the pattern matches any row. This will change later.
  • ONE ROW PER MATCH (which is default) determines that the SQL pattern matching clause will return one row per matched pattern.
That first "pattern matching" query will simply return all rows.
SYMBOL ST  S P
abc 01-JAN-14 abc 13
abc 02-JAN-14 abc 13.4
abc 03-JAN-14 abc 13.8
abc 04-JAN-14 abc 13.1
abc 05-JAN-14 abc 13.3
abc 06-JAN-14 abc 13.9
abc 07-JAN-14 abc 14.1
abc 08-JAN-14 abc 14.7
:   :         :   :
xyz 05-JAN-14 xyz 16.3

Now we'll add a filter - we only want to work with the qoutes for abc. A chart would look as follows.
We want to search for a "V-formation", which is a phase of falling prices, followed by a period of rising prices. In the chart, we can see clearly, that there are four such patterns. So let's try a SQL pattern matching query.
SELECT * FROM ticker
   MATCH_RECOGNIZE (
      PARTITION BY symbol 
      ORDER BY tstamp 
      MEASURES strt.tstamp startdate, LAST(up.tstamp) as enddate, strt.price startprice, LAST(up.price) as endprice
      ONE ROW PER MATCH 
      PATTERN (strt down+ up+) 
         DEFINE down AS price < prev(price), 
                up AS price > prev(price))
where symbol='abc';  
The pattern declaration (keywords PATTERN and DEFINE) contains the most interesting changes. Our pattern consists of three sections; it begins with strt, followed by down and up. The definition of these aliases is containes in the DEFINE clause. An alias which is not defined, matched all rows by definition. So strt matches all rows; thus a pattern can begin anywhere. down is being defined as that the current price is below the previous price; stock is going down. Up is the other way around: The current price is above the previous price. In the PATTERN clause, a plus (+) sign is appended to down and up; which means that both patterns have to appear at least once. You can use the following suffixes to indicate how often a pattern part has to occur.
*      none, one or multiple 
+      one or multiple 
?      zero or one
{n}    exact "n" times
{n,}   "n" or more times
{n,m}  "n" to "m" times
{,m}   not more than "m" times
By default, these quantifiers are greedy; appending an additional question mark makes them reluctant. A greedy quantifier tries to match as much of the input data as possible. Subsequent pattern sections have to deal with the rest of the data. A reluctant quantifier matches just as much data as needed by its definition. More information on this can be found in the Oracle documentation. Query results are as follows ...
SYMBOL STARTDATE ENDDATE  STARTPRICE ENDPRICE
abc 03-JAN-14 08-JAN-14 13.8  14.7
abc 09-JAN-14 12-JAN-14 14.7  14.9
abc 13-JAN-14 16-JAN-14 14.6  14.9
Comparing this to the line chart shows, that we have not reached our goal so far.
The chart shows clearly that the third match begins at the 12th; the query results indicate that its the 13th. The forth match is not being found at all. The reason for this discrepancy is that the SQL pattern matching, after it has found a match, by default continues with the first row after that match. So by default, one row cannot be part of two matches. In reality, the stock price at one day could finish one match and open another. So we have to change that default - we can choose from the following:
  • AFTER MATCH SKIP TO NEXT ROW - Resume pattern matching at the row after the first row of the current match
  • .
  • AFTER MATCH SKIP PAST LAST ROW - Resume pattern matching at the next row after the last row of the current match
  • .
  • AFTER MATCH SKIP TO FIRST pattern_variable - Resume pattern matching at the first row that is mapped to the pattern variable.
  • AFTER MATCH SKIP TO LAST pattern_variable - Resume pattern matching at the last row that is mapped to the pattern variable.
  • AFTER MATCH SKIP TO pattern_variable - The same as AFTER MATCH SKIP TO LAST pattern_variable.
So we change the query as follows ...
SELECT * FROM ticker
   MATCH_RECOGNIZE (
      PARTITION BY symbol 
      ORDER BY tstamp 
      MEASURES strt.tstamp startdate, LAST(up.tstamp) as enddate, strt.price startprice, LAST(up.price) as endprice
      ONE ROW PER MATCH 
      AFTER MATCH SKIP TO LAST up
      PATTERN (strt down+ up+) 
         DEFINE down AS price < prev(price), 
                up AS price > prev(price))
where symbol='abc';
... and we get these results ...
SYMBOL STARTDATE ENDDATE  STARTPRICE ENDPRICE
abc 03-JAN-14 08-JAN-14 13.8  14.7
abc 09-JAN-14 12-JAN-14 14.7  14.9
abc 12-JAN-14 16-JAN-14 14.9  14.9
abc 16-JAN-14 19-JAN-14 14.9  14.6
... which nicely match the chart.
Now we want the query to return not only the matches as such, but also detail information about a match: we want to have the individual rows of a match in order to see how pattern matching works and how it finds the result. So we replace the ONE ROW PER MATCH clause by ALL ROWS PER MATCH
SELECT * FROM ticker 
   MATCH_RECOGNIZE (
      PARTITION BY symbol 
      ORDER BY tstamp 
      MEASURES strt.tstamp startdate, LAST(up.tstamp) AS enddate, strt.price startprice, LAST(up.price) AS endprice
      ALL ROWS PER MATCH 
      AFTER MATCH SKIP TO LAST up
      PATTERN (strt down+ up+) 
         DEFINE down AS price < prev(price), 
                up AS price > prev(price))
where symbol='abc'; 
The result is ...
SYMBOL TSTAMP  STARTDATE ENDDATE  STARTPRICE ENDPRICE PRICE
abc 03-JAN-14 03-JAN-14 -  13.8  -  13.8
abc 04-JAN-14 03-JAN-14 -  13.8  -  13.1
abc 05-JAN-14 03-JAN-14 05-JAN-14 13.8  13.3  13.3
abc 06-JAN-14 03-JAN-14 06-JAN-14 13.8  13.9  13.9
abc 07-JAN-14 03-JAN-14 07-JAN-14 13.8  14.1  14.1
abc 08-JAN-14 03-JAN-14 08-JAN-14 13.8  14.7  14.7
abc 09-JAN-14 09-JAN-14 -  14.7  -  14.7
abc 10-JAN-14 09-JAN-14 -  14.7  -  14.6
abc 11-JAN-14 09-JAN-14 11-JAN-14 14.7  14.8  14.8
abc 12-JAN-14 09-JAN-14 12-JAN-14 14.7  14.9  14.9
abc 12-JAN-14 12-JAN-14 -  14.9  -  14.9
abc 13-JAN-14 12-JAN-14 -  14.9  -  14.6
abc 14-JAN-14 12-JAN-14 -  14.9  -  14.5
abc 15-JAN-14 12-JAN-14 15-JAN-14 14.9  14.7  14.7
abc 16-JAN-14 12-JAN-14 16-JAN-14 14.9  14.9  14.9
abc 16-JAN-14 16-JAN-14 -  14.9  -  14.9
abc 17-JAN-14 16-JAN-14 -  14.9  -  14.3
abc 18-JAN-14 16-JAN-14 18-JAN-14 14.9  14.4  14.4
abc 19-JAN-14 16-JAN-14 19-JAN-14 14.9  14.6  14.6
As long as the ENDDATE result column is SQL NULL, SQL pattern matching did not identify these rows as part of a pattern. Looking into the data, we can see that the price is going down - the rows match the "down" section of our pattern definition. We don't have "up" data so far, so SQL pattern matching does not identify a match. This changes in the third row: the price is now going up and the complete pattern definition is satisfied. So Oracle identified a match and the match the ENDDATE is (for now) being set to JAN-05. This is a partial match, not the final result.
While walking through the remaining rows, match details can still change. And exactly this happens in the three subsequent rows. The stock price is climbing further, so the ENDDATE result is shifted with each row until JAN-09. At this point, Oracle has a found final match for the pattern definition. To get even more insight into the process, we can have the query return the name of the current pattern section (CLASSIFIER), a match number and additionally all rows which don't belong to a match.
SELECT * FROM ticker
   MATCH_RECOGNIZE (
      PARTITION BY symbol 
      ORDER BY tstamp 
      MEASURES strt.tstamp startdate, LAST(up.tstamp) AS enddate, CLASSIFIER() AS c, MATCH_NUMBER() AS m 
      ALL ROWS PER MATCH WITH UNMATCHED ROWS
      AFTER MATCH SKIP TO LAST up
      PATTERN (strt down+ up+) 
         DEFINE down AS price < prev(price), 
                up AS price > prev(price))
where symbol='abc'; 
Die Ausgabe ist nun leicht verständlich und überschauber:
SYMBOL TSTAMP  STARTDATE ENDDATE  C M PRICE
abc 01-JAN-14 - - -  - 13
abc 02-JAN-14 - - -  - 13.4
abc 03-JAN-14 03-JAN-14 -  STRT 1 13.8
abc 04-JAN-14 03-JAN-14 -  DOWN 1 13.1
abc 05-JAN-14 03-JAN-14 05-JAN-14 UP 1 13.3
abc 06-JAN-14 03-JAN-14 06-JAN-14 UP 1 13.9
abc 07-JAN-14 03-JAN-14 07-JAN-14 UP 1 14.1
abc 08-JAN-14 03-JAN-14 08-JAN-14 UP 1 14.7
abc 08-JAN-14 - - -  - 14.7
abc 09-JAN-14 09-JAN-14 -  STRT 2 14.7
abc 10-JAN-14 09-JAN-14 -  DOWN 2 14.6
abc 11-JAN-14 09-JAN-14 11-JAN-14 UP 2 14.8
abc 12-JAN-14 09-JAN-14 12-JAN-14 UP 2 14.9
abc 12-JAN-14 12-JAN-14 -  STRT 3 14.9
abc 13-JAN-14 12-JAN-14 -  DOWN 3 14.6
abc 14-JAN-14 12-JAN-14 -  DOWN 3 14.5
abc 15-JAN-14 12-JAN-14 15-JAN-14 UP 3 14.7
abc 16-JAN-14 12-JAN-14 16-JAN-14 UP 3 14.9
abc 16-JAN-14 16-JAN-14 -  STRT 4 14.9
abc 17-JAN-14 16-JAN-14 -  DOWN 4 14.3
abc 18-JAN-14 16-JAN-14 18-JAN-14 UP 4 14.4
abc 19-JAN-14 16-JAN-14 19-JAN-14 UP 4 14.6
abc 19-JAN-14 - - - - 14.6
The classifier column (C) of the result set now shows the part of our pattern definition which matches the current row. If this is empty, the row definitively does not match the pattern. As soon as it contains a value, we have a partial match - it depends on the subsequent data whether it will become a final match. As soon as we have the sequence of STRT, DOWN and UP, we have a final pattern match - the match number column (M) is being increased by one.
Finally, we extend our pattern definition. We don't want to search for "V" pattern any more, we are interested in the "W" pattern; which means that we need a "down" phase, followed by "up", followed by "down" and by "up" again. For this we simply need to extend the PATTERN clause within our query.
SELECT * FROM ticker 
   MATCH_RECOGNIZE (
      PARTITION BY symbol 
      ORDER BY tstamp 
      MEASURES strt.tstamp startdate, LAST(up.tstamp) AS enddate
      ONE ROW PER MATCH
      AFTER MATCH SKIP TO LAST up
      PATTERN (strt down+ up+ down+ up+) 
         DEFINE down AS price < prev(price), 
                up AS price > prev(price))
where symbol='abc';   
We get the following result ...
SYMBOL STARTDATE ENDDATE
abc 09-JAN-14 16-JAN-14
... which (again) don't match the chart. What happened?
The first "W" pattern has been successfully found by the query. But we told the database to continue at the last row of the "up" section. Thus two "W" patterns cannot overlap and we'll find only one match. To let the query find overlapping patterns as well, we need te be able to distinct between the two legs of the "W" pattern. So we introduce explicit aliases for each of them. After that we can change the AFTER MATCH SKIP clause to continue after the first "up" section and not after the second.
SELECT * FROM ticker 
   MATCH_RECOGNIZE (
      PARTITION BY symbol 
      ORDER BY tstamp 
      MEASURES strt.tstamp startdate, LAST(up2.tstamp) AS enddate
      ONE ROW PER MATCH
      AFTER MATCH SKIP TO LAST up1
      PATTERN (strt down+ up1+ down+ up2+) 
         DEFINE down AS price < prev(price), 
                up1 AS price > prev(price),
                up2 AS price > prev(price))
where symbol='abc'; 
... and this query works as expected. It is able to find even overlapping matches.
SYMBOL STARTDATE ENDDATE
abc 09-JAN-14 16-JAN-14
abc 12-JAN-14 19-JAN-14
This example is also available on Oracle LIVE SQL, Oracle's new platform to contribute and share SQL scripts and tutorial: https://livesql.oracle.com/apex/livesql/s/cbdvwws7f8mddrph8wrx9sh87. You can run the script there or download it and play with it in your own environment.

9. November 2015

Blog Posting 0x100 - aus gegebenem Anlass geht es um Hexcodes

Dies ist das Posting Nr. 256 - im Hex wäre das die Nummer 0x100; bei so einer schönen runden Zahl bietet es sich an, ein wenig über das Thema Hexcodes in Oracle zu schreiben, wann und wo man die verwenden kann ... ich denke nicht, dass dies eine vollständige Aufzählung werden wird, aber einige Dinge sind vielleicht für den einen oder anderen interessant ...
Fangen wir mal ganz leicht an. Selektiert man in SQL*Plus einen BLOB oder einen RAW-Datentypen, so werden Hexcodes zurückgegeben.
SQL> desc jsontable
 Name                                      Null?    Typ
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL VARCHAR2(255)
 CREATED_ON                                NOT NULL TIMESTAMP(6)
 LAST_MODIFIED                             NOT NULL TIMESTAMP(6)
 VERSION                                   NOT NULL VARCHAR2(255)
 JSON_DOCUMENT                                      BLOB

SQL> select json_document from jsontable;

JSON_DOCUMENT
--------------------------------------------------------------------------------
7B227265747765657465645F737461747573223A7B22636F6E7472696275746F7273223A6E756C6C
2C2274657874223A2223636F756E74646F776E206E616172202341706578776F726C6420404F4768
Möchte man im SQL*Plus bereits sehen, was sich hinter diesen Hexcodes verbirgt, so kann man diese on-the-fly umwandeln. Hierzu schneidet man mit der Funktion DBMS_LOB.SUBSTR einen Teil des BLOBs aus - man erhält eine Instanz vom Typ RAW. So wie VARCHAR2 zum CLOB steht, so steht RAW zum BLOB: Es ist ein Datentyp für Folgen bis zu 2000 Bytes in SQL - in PL/SQL kann ein RAW bis zu 32767 Bytes aufnehmen. Mit der Funktion UTL_RAW.CAST_TO_VARCHAR2 kann man die Datenbank nun dazu bringen, die RAW-Bytes wie ein VARCHAR2 aufzufassen - SQL*Plus wird versuchen, den Text darzustellen, was in diesem Beispiel auch gut gelingt ...
SQL> select utl_raw.cast_to_varchar2(dbms_lob.substr(json_document,100,1)) from jsontable;

UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(JSON_DOCUMENT,100,1))
--------------------------------------------------------------------------------
{"retweeted_status":{"contributors":null,"text":"#countdown naar #Apexworld @OGh
_nl #iadvise_live @Y
Umgekehrt kann man Oracle mit UTL_RAW.CAST_TO_RAW dazu bringen, einen VARCHAR2-Wert als RAW aufzufassen - SQL*Plus wird uns dann die Hexcodes für die Bytes anzeigen, aus denen der String besteht. Bei ASCII-Zeichen ist das recht eindeutig; bei Umlauten hängt es vom Datenbank-Zeichensatz ab, was wir sehen werden.
SQL> select utl_raw.cast_to_raw('ABCDEF') from dual;

UTL_RAW.CAST_TO_RAW('ABCDEF')
--------------------------------------------------------------------------------
414243444546

SQL> select utl_raw.cast_to_raw('Müller') from dual;

UTL_RAW.CAST_TO_RAW('MÜLLER')
--------------------------------------------------------------------------------
4DC3BC6C6C6572
Wichtig ist, dass diese CAST-Funktionen den Inhalt des Wertes nicht verändern; die Bytes werden lediglich anders aufgefasst. RAW-Values werden im SQL*Plus, wie man sieht, direkt als Hexcode angezeigt. Analog dazu lassen sich mit Hexcodes auch RAW-Values erzeugen - hier braucht es aber die SQL-Funktion HEXTORAW - sie nimmt einen VARCHAR-String mit Hexcodes entgegen und liefert den korrespondierenden RAW-Wert zurück.
SQL> select hextoraw('000000') as RAWVAL, dump(hextoraw('000000')) as DUMP from dual;

RAWVAL DUMP
------ -------------------
000000 Typ=23 Len=3: 0,0,0

1 Zeile wurde ausgewählt.
Hier muss man jetzt aufpassen - das übergebene VARCHAR2 besteht aus 6 Zeichen (sechs Bytes), aus denen mit der HEXTORAW-Funktion aber ein RAW generiert wird - zwei Zeichen stehen dann für ein Byte. Der tatsächliche Wert sind dann drei Nullbytes, die auf der Kommandozeile eigentlich gar nicht dargestellt werden könnten - daher nimmt SQL*Plus zur Darstellung wieder die Hexcodes her (tatsächlich besteht der Wert aber aus drei Nullbytes).
Mit der umgekehrten Funktion RAWTOHEX generiert die Datenbank aus einem RAW-Wert wieder einen VARCHAR2, wobei hierfür wieder Hexcodes für die einzelnen Bytes gebildet werden - aus den drei Nullbytes wird dann wieder dreimal die Zeichenfolge 00. Das ist ein wesentlicher Unterschied zur Funktion UTL_RAW.CAST_TO_VARCHAR2, welche die Bytes so lässt, wie sie sind, und einfach als VARCHAR2 auffasst. Die folgende Abfrage macht den Unterschied deutlich. Beide Ergebnisspalten sind vom Typ VARCHAR2.
select 
  rawtohex(raw_column) hex, 
  '"' || utl_raw.cast_to_varchar2(raw_column) || '"' as rawval 
from table_with_raw_contents;

HEX                  RAWVAL
-------------------- --------------------
000000               "   "
404142               "@AB"
Bevor man RAW-Values als Zeichenkette (VARCHAR2) weiterverarbeitet (bspw. um sie anzuzeigen), muss man also stets überlegen, wie das konkret verfolgen soll. Die Umwandlung in Hexcodes sorgt dafür, dass jede Konsole etwas anzeigen kann; allerdings sind selbst reine Texte dann nicht mehr leicht lesbar. Ein einfacher Cast nach VARCHAR2 fast alle Bytes als Zeichen auf, was bei manchen Zeichen zu unleserlicher Ausgabe führen kann. RAW sind halt eben Bytes; und jede Darstellung als TEXT ist irgendwo nur ein Hilfsmittel ...
Öfter möchte man einfach eine Dezimalzahl in ein Hexzahl umwandeln und umgekehrt. Dazu braucht es keinerlei RAW-Funktionen; hier gibt es eine Formatmaske in den Funktionen TO_CHAR und TO_NUMBER - diese liefert aber keinen RAW zurück; man erhält einen VARCHAR2 - eben mit Hexcodes als Inhalt.
SQL> select to_char(256, 'FM0XXX') HEX from dual;

HEX
----
0100

SQL> select to_number('100', 'XXXX') DEC from dual;

DEC
-------------
          256
Man kann sich aus diesen Informationen nun eine "Anregung" holen - und zwar für die Fälle, in denen man RAW oder BLOB-Objekte per SQL-Skript erzeugen möchte. Das kann bei Installation einer Anwendung interessant sein, wenn in eine der Tabellen BLOBs abgelegt werden sollen - Export/Import aber nicht gewünscht ist. Oracle Application Express löst dieses Problem schon sehr lange - denn jedes APEX Export-File ist ein SQL-Skript, welches mit SQL*Plus eingespielt werden kann. Enthält eine APEX-Anwendung Bilder oder andere statische Dateien, so werden diese wie folgt im SQL-Skript kodiert.
:

begin
  wwv_flow_api.g_varchar2_table := wwv_flow_api.empty_varchar2_table;
  wwv_flow_api.g_varchar2_table(1) := '73657420646566696E65206F66660A73657420766572696679206F66660A736574...';
  wwv_flow_api.g_varchar2_table(2) := '5F666C6F772E675F696D706F72745F696E5F70726F6772657373203A3D20747275...';
  wwv_flow_api.g_varchar2_table(3) := '414120204141202020202020505020205050202045452020202020202058582020...';
  :
  :
  wwv_flow_api.g_varchar2_table(25) := '20202050505050502020204545454520202020202020585858580A2D2D20202041...';
  wwv_flow_api.g_varchar2_table(26) := '796C6573686565743E';
  wwv_flow_api.create_app_static_file(
   p_id              => 1768395483840570815+wwv_flow_api.g_id_offset
  ,p_file_name       => 'some_static_APEX_image.png'
  ,p_mime_type       => 'image/png'
  ,p_file_content    => wwv_flow_api.varchar2_to_blob(wwv_flow_api.g_varchar2_table)
  );
end;

:
Auf diese Weise können binäre Dateien problemlos in ein SQL-Skript gepackt und - ganz ohne Export/Import - problemlos auf andere Datenbanken übertragen werden. Wenn Ihr das mal ohne APEX und für eigene Tabellen versuchen wollt, schaut euch dieses Blog-Posting aus dem Jahr 2008 an; dort findet Ihr eine PL/SQL-Prozedur, die euch einen BLOB in ein SQL-Skript umwandelt. Das ist natürlich nur für eine überschaubare Anzahl BLOBs interessant - eben dann, wenn ein SQL-Skript Tabellen, Views, PL/SQL-Prozeduren anlegen und die Tabellen gleichzeitig mit Seed-Daten inkl. BLOBs füllen soll.
Zum Abschluß des Blog-Postings habe ich noch ein kleines Geschenk was für euch: zum Blog-Posting 0x100 gibt es einen Hex Viewer für BLOBs in SQL*Plus - spielt einfach diese PL/SQL-Funktion hier ein ...
create or replace function display_blob(
  p_blob    in blob
 ,p_start   in number default 1
 ,p_lines   in number default 16
) return varchar2 authid current_user is
  l_raw   raw(2000);
  l_vc    varchar2(4000);

  l_linesize pls_integer := 16;
  l_finish   boolean     := false;
begin
  if p_lines > 50 then
    raise_application_error(-20000, 'MAXIMUM OF 50 LINES');
  end if;

  l_raw := dbms_lob.substr(p_blob, p_lines * l_linesize, p_start);

  for i in 0 .. p_lines - 1 loop
    l_vc := l_vc || to_char((p_start-1) + i * l_linesize, 'FM0XXXXXXX')||': ';
    for j in 1 .. l_linesize loop
      if i * l_linesize + j <= utl_raw.length(l_raw) then
        l_vc := l_vc || rawtohex(utl_raw.substr(l_raw, (i * l_linesize) + j, 1)) ||' ';
      else 
        l_vc := l_vc || '   ';
        l_finish := true;
      end if;
    end loop;
    l_vc := l_vc || '| ';
    for j in 1 .. l_linesize loop
      if i * l_linesize + j <= utl_raw.length(l_raw) then
        if ascii(utl_raw.cast_to_varchar2(utl_raw.substr(l_raw, (i * l_linesize) + j, 1))) between 32 and 126 then 
          l_vc := l_vc || utl_raw.cast_to_varchar2(utl_raw.substr(l_raw, (i * l_linesize) + j, 1)); 
        else 
          l_vc := l_vc || '.';
        end if;
      end if;
    end loop;
    l_vc := l_vc || chr(10);
    if l_finish then exit;  end if;
  end loop;
  return l_vc;
end display_blob;
/
sho err
Die könnt Ihr nun mit einem BLOB aufrufen - der erste Parameter ist der BLOB, den Ihr euch ansehen wollt, der zweite die Startposition und der dritte die Anzahl "Zeilen"; die Ihr sehen wollt - eine Zeile umfasst dabei 16 Bytes; da intern mit einem VARCHAR2 gearbeitet wird, könnt Ihr nicht mehr als 50 "Zeilen" auf einmal zeigen.
FUNCTION display_blob RETURNS VARCHAR2
 Argument Name                  Typ                     In/Out Defaultwert?
 ------------------------------ ----------------------- ------ --------
 P_BLOB                         BLOB                    IN
 P_START                        NUMBER                  IN     DEFAULT
 P_LINES                        NUMBER                  IN     DEFAULT

SQL> select display_blob(content) from  myblobs;

DISPLAY_BLOB(CONTENT)
--------------------------------------------------------------------------------
00000000: 89 50 4E 47 0D 0A 1A 0A 00 00 00 0D 49 48 44 52 | .PNG........IHDR
00000010: 00 00 00 C8 00 00 00 FA 08 02 00 00 00 6B 88 9F | .............k..
00000020: 6F 00 00 00 09 70 48 59 73 00 00 0E C4 00 00 0E | o....pHYs.......
00000030: C4 01 95 2B 0E 1B 00 00 1E F1 49 44 41 54 78 9C | ...+......IDATx.
00000040: ED 9D FB 93 64 47 95 DF BF 27 33 EF A3 1E FD 9A | ....dG...'3.....
00000050: 9E 97 34 92 46 A3 11 A3 D1 20 06 09 21 B1 C0 2F | ..4.F.... ..!../
00000060: A0 C5 B1 AC B1 0D 18 3B D6 26 36 8C 20 C2 86 25 | .......;.&6. ..%
00000070: FC C3 FE 01 76 40 2C B1 E1 DD D8 B5 83 88 F5 0F | ....v@,.........
00000080: E6 A7 B5 31 D8 44 38 58 36 C2 86 DD 8D 58 60 0C | ...1.D8X6....X`.
00000090: 78 59 AD 78 49 02 C4 82 A4 41 48 48 83 34 A3 E9 | xY.xI....AHH.4..
000000A0: E9 47 D5 7D 64 E6 F1 0F 59 75 BB BA BA A7 A7 67 | .G.}d...Yu.....g
000000B0: E6 66 55 DF EE FC 68 66 54 DD 55 95 99 37 F3 7B | .fU...hfT.U..7.{
000000C0: 4F E6 3D 27 1F C4 CC D8 35 B8 C2 10 91 7B 4D 44 | O.='....5....{MD
000000D0: 55 F1 88 88 87 1F B2 CC D6 5A CB CC CC D6 32 83 | U........Z....2.
000000E0: 99 D9 32 1B C3 44 30 D6 82 11 25 31 11 19 63 86 | ..2..D0...%1..c.
000000F0: 6F 59 00 EE 47 00 96 D9 5A 03 06 6F C8 19 00 08 | oY..G...Z..o....
:
Viel Spaß beim Ausprobieren - ich weiss, dass man zu RAW, Binary und Hexcodes noch viel mehr sagen könnte, belasse es für heute aber dabei. Mehr gibt es dann beim Blogposting Nr. 0x1000.
This is my blog posting #256 - and in hex this is 0x100. We are in the IT industry, so I declare this an anniversary. And with such a nice round (hex) number, I take the freedom to post something about working with the Oracle database and hex codes (at the end of the posting you'll find a present). Let's start simple: When selecting a BLOB or RAW value in SQL*Plus, you'll see the contents in hex encoding.
SQL> desc jsontable
 Name                                      Null?    Typ
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL VARCHAR2(255)
 CREATED_ON                                NOT NULL TIMESTAMP(6)
 LAST_MODIFIED                             NOT NULL TIMESTAMP(6)
 VERSION                                   NOT NULL VARCHAR2(255)
 JSON_DOCUMENT                                      BLOB

SQL> select json_document from jsontable;

JSON_DOCUMENT
--------------------------------------------------------------------------------
7B227265747765657465645F737461747573223A7B22636F6E7472696275746F7273223A6E756C6C
2C2274657874223A2223636F756E74646F776E206E616172202341706578776F726C6420404F4768
That is, because SQL*Plus has no other choice. A BLOB or RAW value can contain anything; not necessarily readable text. To avoid confusion or terminal errors due to control characters, SQL*Plus takes the safe way and encodes all in hex. You can force SQL*Plus to display the binary content as text (at your own risk ;-)): to do so, you simply need to let the database treat the BLOB like a VARCHAR2. First, use DBMS_LOB.SUBSTR to clip out a part of the BLOB, which will get you an instance of the RAW datatype. A RAW is related to a BLOB like a VARCHAR2 to a CLOB - it can take up to 2000 bytes in the SQL and up to 32767 bytes in the PL/SQL area. The function UTL_RAW.CAST_TO_VARCHAR2 then converts the RAW to a VARCHAR2 type, but without changing its contents; the RAW bytes are being "treated" as VARCHAR2. Thus SQL*Plus will display as text and in that very instance (where is BLOB contains a JSON document), this works out well ...
SQL> select utl_raw.cast_to_varchar2(dbms_lob.substr(json_document,100,1)) from jsontable;

UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(JSON_DOCUMENT,100,1))
--------------------------------------------------------------------------------
{"retweeted_status":{"contributors":null,"text":"#countdown naar #Apexworld @OGh
_nl #iadvise_live @Y
We can also do the other way around: UTL_RAW.CAST_TO_RAW treats a VARCHAR2 value as a RAW instance; SQL*Plus will then display the bytes as hex codes. For ASCII characters, results are the same in all databases, for umlauts or other non-ASCII characters, results are dependent of the database character set.
SQL> select utl_raw.cast_to_raw('ABCDEF') from dual;

UTL_RAW.CAST_TO_RAW('ABCDEF')
--------------------------------------------------------------------------------
414243444546

SQL> select utl_raw.cast_to_raw('Müller') from dual;

UTL_RAW.CAST_TO_RAW('MÜLLER')
--------------------------------------------------------------------------------
4DC3BC6C6C6572
Note, that these UTL_RAW cast functions do not change the actual bytes; the client only treats them differently. When we have a RAW value, SQL*Plus shows hexcodes, when we have VARCHAR2, SQL*Plus shows text.
When using hexcodes to actually create RAW instances, we don't want to use these CAST functions. Why? Because we want to have one byte in the RAW instance when we type 00. The simple CAST function would give us two. The HEXTORAW SQL function takes hex-encoded bytes as VARCHAR2 - and returns a RAW instance. Now the hexcodes will be interpreted - as the following example shows.
SQL> select hextoraw('000000') as RAWVAL, dump(hextoraw('000000')) as DUMP from dual;

RAWVAL DUMP
------ -------------------
000000 Typ=23 Len=3: 0,0,0
Take care: the VARCHAR2 value we passed to the HEXTORAW function consists of 6 zeroes - which takes 6 bytes. But HEXTORAW returns a RAW value which consists of three (zero) bytes. So the input has been interpreted: two digits make up one byte. When SQL*Plus has to display the three zero bytes, it falls back to hex codes again - therefore I added the DUMP function for more clarity.
RAWTOHEX works the other way around. A RAW value is being converted to a VARCHAR2 where each byte will be "rendered" as a 2-digit hex code. So our three zero bytes become 000000. The important difference between HEXTORAW/RAWTOHEX and the UTL_RAW CAST functions is that the former functions will change the actual bytes, whereas the latter ones don't. The following SQL example illustrates this.
select 
  rawtohex(raw_column) hex, 
  '"' || utl_raw.cast_to_varchar2(raw_column) || '"' as rawval 
from table_with_raw_contents;

HEX                  RAWVAL
-------------------- --------------------
000000               "   "
404142               "@AB"
So when you are about to convert a RAW to a VARCHAR2 (in order to display or further process it), you have the choice: You might use the UTL_RAW cast functions to just treat the RAW values as Text - all bytes with a value less than 32 will lead to funny output on your console (control characters), but all bytes representing ASCII characters will be readably. On the other hand, RAWTOHEX will convert everything to hexcodes - an "A" within the RAW value will become "41" in the output. The "best" approach depends on your requirements.
Sometimes you don't have RAW bytes, but decimal numbers - and these must be converted to hex or vice-versa. We don't need any RAW functions for this - we have hex support in the TO_CHAR and TO_NUMBER functions.
SQL> select to_char(256, 'FM0XXX') HEX from dual;

HEX
----
0100

SQL> select to_number('100', 'XXXX') DEC from dual;

DEC
-------------
          256
All this might lead to an idea: sometimes we want to create RAW or BLOB instances with SQL scripts. We might have a SQL script creating tables, views, PL/SQL objects and finally our script inserts some seed data. In today's times, images might be part of this seed data. One approach is to have two steps: first create all the objects with a script, then run a Data Pump import to loads the BLOBs. But having the above in our mind, we could also do both steps only with SQL scripting.
Oracle Application Express is doing this for years: As the APEX users know, an APEX export is a SQL script. We can install APEX applications by running the export files in SQL*Plus. When such an export file contains static files (images, CSS files or other content), these files will be stored in the target APEX instance as BLOBs - and their binary content is "somehow" encoded in the SQL export file. Let's have a look ...
:

begin
  wwv_flow_api.g_varchar2_table := wwv_flow_api.empty_varchar2_table;
  wwv_flow_api.g_varchar2_table(1) := '73657420646566696E65206F66660A73657420766572696679206F66660A736574...';
  wwv_flow_api.g_varchar2_table(2) := '5F666C6F772E675F696D706F72745F696E5F70726F6772657373203A3D20747275...';
  wwv_flow_api.g_varchar2_table(3) := '414120204141202020202020505020205050202045452020202020202058582020...';
  :
  :
  wwv_flow_api.g_varchar2_table(25) := '20202050505050502020204545454520202020202020585858580A2D2D20202041...';
  wwv_flow_api.g_varchar2_table(26) := '796C6573686565743E';
  wwv_flow_api.create_app_static_file(
   p_id              => 1768395483840570815+wwv_flow_api.g_id_offset
  ,p_file_name       => 'some_static_APEX_image.png'
  ,p_mime_type       => 'image/png'
  ,p_file_content    => wwv_flow_api.varchar2_to_blob(wwv_flow_api.g_varchar2_table)
  );
end;

:
The script contains all binary content hex-encoded in some PL/SQL arrays of type VARCHAR2. First, the script creates the array, then it's being passed to a WWV_FLOW_API procedure, which generates the BLOB from it. We can imagine that this procedure uses HEXTORAW somewhere in the background. If you want to use this approach, without APEX, for your own tables, have a look into this blog posting from 2008; it contains a custom function which takes a BLOB as input, and generates similar PL/SQL code - you can have binary content encoded in your SQL script the same way as APEX does it. The code can then be added to your own SQL script. For a reasonable number of BLOBs, for instance, seed data of an installation script, this is a very nice and working approach to get binary data into your database.
At the end of this "anniversary" blog posting, I have a special "present" for you. We can use the HEXTORAW, RAWTOHEX and UTL_RAW cast functions to present binary data better than SQL*Plus does it out-of-the-box. Here is the SQL Hex Viewer function. Simply create the following PL/SQL function ...
create or replace function display_blob(
  p_blob    in blob
 ,p_start   in number default 1
 ,p_lines   in number default 16
) return varchar2 authid current_user is
  l_raw   raw(2000);
  l_vc    varchar2(4000);

  l_linesize pls_integer := 16;
  l_finish   boolean     := false;
begin
  if p_lines > 50 then
    raise_application_error(-20000, 'MAXIMUM OF 50 LINES');
  end if;

  l_raw := dbms_lob.substr(p_blob, p_lines * l_linesize, p_start);

  for i in 0 .. p_lines - 1 loop
    l_vc := l_vc || to_char((p_start-1) + i * l_linesize, 'FM0XXXXXXX')||': ';
    for j in 1 .. l_linesize loop
      if i * l_linesize + j <= utl_raw.length(l_raw) then
        l_vc := l_vc || rawtohex(utl_raw.substr(l_raw, (i * l_linesize) + j, 1)) ||' ';
      else 
        l_vc := l_vc || '   ';
        l_finish := true;
      end if;
    end loop;
    l_vc := l_vc || '| ';
    for j in 1 .. l_linesize loop
      if i * l_linesize + j <= utl_raw.length(l_raw) then
        if ascii(utl_raw.cast_to_varchar2(utl_raw.substr(l_raw, (i * l_linesize) + j, 1))) between 32 and 126 then 
          l_vc := l_vc || utl_raw.cast_to_varchar2(utl_raw.substr(l_raw, (i * l_linesize) + j, 1)); 
        else 
          l_vc := l_vc || '.';
        end if;
      end if;
    end loop;
    l_vc := l_vc || chr(10);
    if l_finish then exit;  end if;
  end loop;
  return l_vc;
end display_blob;
/
sho err
And try it out with a BLOB. The functions' first parameter is the BLOB itself, the second is the first byte which is to be displayed (defaults to 1). The third parameter determines, how many "lines" are to be returned - each line represents 16 bytes. Internally, the functions works with a VARCHAR2, so we can only retrieve up to 50 lines in one call. You'll get a binary display like the hex viewers in the old days ...
SQL> desc display_blob
FUNCTION display_blob RETURNS VARCHAR2
 Argument Name                  Typ                     In/Out Defaultwert?
 ------------------------------ ----------------------- ------ --------
 P_BLOB                         BLOB                    IN
 P_START                        NUMBER                  IN     DEFAULT
 P_LINES                        NUMBER                  IN     DEFAULT

SQL> select display_blob(content) from  myblobs;

DISPLAY_BLOB(CONTENT)
--------------------------------------------------------------------------------
00000000: 89 50 4E 47 0D 0A 1A 0A 00 00 00 0D 49 48 44 52 | .PNG........IHDR
00000010: 00 00 00 C8 00 00 00 FA 08 02 00 00 00 6B 88 9F | .............k..
00000020: 6F 00 00 00 09 70 48 59 73 00 00 0E C4 00 00 0E | o....pHYs.......
00000030: C4 01 95 2B 0E 1B 00 00 1E F1 49 44 41 54 78 9C | ...+......IDATx.
00000040: ED 9D FB 93 64 47 95 DF BF 27 33 EF A3 1E FD 9A | ....dG...'3.....
00000050: 9E 97 34 92 46 A3 11 A3 D1 20 06 09 21 B1 C0 2F | ..4.F.... ..!../
00000060: A0 C5 B1 AC B1 0D 18 3B D6 26 36 8C 20 C2 86 25 | .......;.&6. ..%
00000070: FC C3 FE 01 76 40 2C B1 E1 DD D8 B5 83 88 F5 0F | ....v@,.........
00000080: E6 A7 B5 31 D8 44 38 58 36 C2 86 DD 8D 58 60 0C | ...1.D8X6....X`.
00000090: 78 59 AD 78 49 02 C4 82 A4 41 48 48 83 34 A3 E9 | xY.xI....AHH.4..
000000A0: E9 47 D5 7D 64 E6 F1 0F 59 75 BB BA BA A7 A7 67 | .G.}d...Yu.....g
000000B0: E6 66 55 DF EE FC 68 66 54 DD 55 95 99 37 F3 7B | .fU...hfT.U..7.{
000000C0: 4F E6 3D 27 1F C4 CC D8 35 B8 C2 10 91 7B 4D 44 | O.='....5....{MD
000000D0: 55 F1 88 88 87 1F B2 CC D6 5A CB CC CC D6 32 83 | U........Z....2.
000000E0: 99 D9 32 1B C3 44 30 D6 82 11 25 31 11 19 63 86 | ..2..D0...%1..c.
000000F0: 6F 59 00 EE 47 00 96 D9 5A 03 06 6F C8 19 00 08 | oY..G...Z..o....
:
Have fun when trying this out. I know, there are so many things which I could talk related to BLOB and RAW values or hex codes. But for today, this concludes my posting - you might see more on this in post #0x1000.

19. Oktober 2015

DOAG2015 und "Modern Application Development" ab Januar 2016

In wenigen Wochen beginnt, wie jedes jahr, die DOAG-Konferenz. Dieses Jahr bin ich mit vier Vorträgen vertreten. Vielleicht ist der eine oder andere interessant für euch.

  • 17.11. 09:00 "Sydney"
    Node.js + Oracle-Datenbank = "node-oracledb" = Cool!
    Wer möchte, kann die Konferenz gleich mit den aktuellen, modernen und coolen Technologien beginnen. Nach einer kurzen Einführung in Node.js geht es in diesem Vortrag um den Oracle-Datenbanktreiber für Node.js, "node-oracledb". Ich werde vorstellen, wie man ein Node.js Programm zum Zugriff auf die Oracle-Datenbank schreibt, und was man mit Node.js und der Oracle-Datenbank alles erreichen kann (Websockets, Mailintegration und einiges mehr).
  • 19.11. 12:00 "Foyer Tokio"
    Das Leben der anderen: Twitter-Analyse mit Oracle12c, JSON und APEX
    Wieder darf ich eines der Demo-Kinos durchführen. Diesmal zeige ich eine "Twitter-Analyse"– mit nichts als den Bordmitteln der Oracle Database 12c. Tweets zum Hashtag #orclapex werden mit PL/SQL abgerufen und als JSON in die Datenbank gespeichert. Mit den Oracle12c SQL/JSON-Funktionen lassen sich dann mächtige Analysen durchführen.
  • 19.11. 14:00 "Istanbul"
    Diagramme – next generation: D3.js im Unternehmen nutzen
    Das freie Visualisierungs-Framework d3js (D3 = DDD = Data Driven Documents) gewinnt mehr und mehr an Popularität. Die Fülle an Diagrammen und Visualisierungen, die sich mit D3 realisieren lassen, grenzt wirklich ans Unglaubliche. Nach einer kurzen Einführung in die Technologie zeigt der Vortrag anhand von Oracle Application Express (APEX), wie man ein D3-Diagramm integrieren und Tabellendaten damit auf moderne Art und Weise visualisieren kann – die Nutzung in anderen Umgebungen ist von dort aus nur ein kleiner Schritt.
  • 17.11. 14:30 "Budapest"
    Anwendungen schnell und einfach – in der Cloud oder On Premise: Oracle Application Express (APEX)
    Der vierte ist kein regulärer DOAH-Vortrag, diesen halte ich im Rahmen der in die DOAG2015 integrierten Veranstaltung Oracle Cloud Day; in 25 Minuten stelle ich die Möglichkeiten von APEX vor - das ist vor allem für die interessant, die APEX noch nicht kennen, aber wissen wollen, was dahinter steckt.

Neben den Vorträgen freue ich mich vor allem auf die Gespräche, Kontakte und zahlreichen Community-Aktivitäten, die neben der DOAG-Konferenz stattfinden. So trifft sich die APEX-Community am Montag abend (also vor der Konferenz) im "Barfüßer" und Mittwochs findet, wie schon im letzten Jahr, eine APEX Open Mic Night statt. Dort ist "das Mikrofon offen", also sind alle eingeladen, in 10 Minuten kurz und knapp vorzustellen, was man mit APEX tolles gebaut hat. Und das ist sicherlich noch nicht alles ...


Im Januar 2016 starten Karin Patenge, Rainer Willems, Beda Hammerschmidt, Kai Donato, Dietmar Aust und ich - in Zusammenarbeit mit der DOAG eine Online-Videoserie zum Thema Modern Application Development - in der Praxis.

In acht Vidoes geht es um JSON, REST-Services, HTML5, Node.js und ähnliche, aktuell heiß diskutierte Entwicklerthemen. Alles wird im Bezug zum Unternehmenseinsatz betrachtet - die Integration in die IT-Landschaft oder mit der Oracle-Datenbank spielt also eine besondere Rolle. Die Teilnahme ist auch hier kostenlos - wenn Ihr auf aktuellem Stand bleiben möchtet, meldet euch gleich an (Klickt oben rechts auf Anmelden). Abgeschlossen wird die Reihe mit einer Online Q & A, bei der Ihr die Gelegenheit habt, mit den Experten zu diskutieren und eure Fragen zu stellen.

Schaut mal rein - sehen wir uns ...?

7. Oktober 2015

Statisches oder Dynamisches SQL? Query oder DML?

Zur in Oracle12c neu eingeführten Möglichkeit, PL/SQL-Code in die WITH-Klausel zu integrieren, hatte ich schon einmal ein Blog-Posting geschrieben. Heute behandle ich das Thema noch einmal, denn es bietet geradezu faszinierende Facetten an ...
Nochmal kurz zum Einstimmen: Die PL/SQL WITH-Klausel wurde mit Oracle12c (12.1.0.1) eingeführt, und sie erlaubt es, PL/SQL Funktionen innerhalb der WITH-Klausel zu definieren und diese in der eigentlichen SQL-Abfrage zu verwenden. Es braucht dazu kein CREATE FUNCTION Privileg; sobald man eine SQL-Query formulieren kann, kann man auch die PL/SQL WITH-Klausel nutzen. Ein einfaches Beispiel.
with function get_sal(p_empno in number) return number as
  l_return number;
 begin
  select sal into l_return from emp where empno = p_empno;
  return l_return;
 end get_sal;
select empno, ename, get_sal(empno) from emp
Das Ergebnis hätte man zugegebermaßen auch ohne PL/SQL-WITH haben können, aber es geht ja auch nur um das Beispiel. Innerhalb der WITH-Klausel wird die Funktion GET_SAL implementiert; diese gilt allein innerhalb der SQL-Abfrage. Allerdings kann man in der WITH-Klausel den vollen Sprachumfang von PL/SQL nutzen - und das nächste Beispiel geht schon etwas weiter: Wir bauen ein EXECUTE IMMEDIATE ein - ist das folgende nun also statisches oder dynamisches SQL ...?
with function get_rowcnt(p_tname in varchar2) return number is
    l_rows number;
  begin
    execute immediate 'select count(*) from ' || 
     dbms_assert.sql_object_name('"'||p_tname||'"') 
    into l_rows;
    return l_rows;
  end get_rowcnt;
select table_name, get_rowcnt(table_name) as row_count 
from user_tables;

TABLE_NAME                      ROW_COUNT
------------------------------ ----------
TAB_ARRAY                          100000
CSV_TABLE                           10000
EMP1                                 8192
EBA_SPATIAL_COUNTRIES                  35
DR$FT_EMP$I                            24
EMP                                    17
VERKAUF                                16
:                                       :
Die PL/SQL Funktion GET_ROWCNT führt nun jedes mal ein EXECUTE IMMEDIATE durch und zählt damit die Zeilen der jeweiligen Tabelle. Verbunden mit einer Query auf die Data Dictionary View USER_TABLES kann man so sehr elegant - und nur mit einer SQL-Abfrage - eigentlich mehrere SQL-Queries auf einmal absetzen. Statisches oder dynamisches SQL - das ist hier die Frage!
Treiben wir das Spiel noch etwas weiter - man könnte ja versuchen, in der Funktion der WITH-Klausel ein SQL UPDATE zu machen. Dazu ändern wir das erste Beispiel mit der Tabelle EMP ein wenig um ...
with function get_sal(p_empno in number) return number as
  l_return number;
 begin
  -- ACHTUNG: Hier passiert was!
  update emp set sal = sal * 2 where empno = p_empno returning sal into l_return;
  commit;
  return l_return;
 end get_sal;
select empno, ename, get_sal(empno) from emp
/
 
FEHLER in Zeile 9:
ORA-14551: DML-Vorgang kann innerhalb einer Abfrage nicht ausgeführt werden
ORA-06512: in Zeile 5
ORA-06512: in Zeile 9
OK, das scheint nicht zu gehen. Oder doch?
with function get_sal(p_empno in number) return number as
  l_return number;
  pragma autonomous_transaction;
 begin
  -- ACHTUNG: Hier passiert was!
  update emp set sal = sal * 2 where empno = p_empno returning sal into l_return;
  commit;
  return l_return;
 end get_sal;
select empno, ename, get_sal(empno) from emp
/

     EMPNO ENAME      GET_SAL(EMPNO)
---------- ---------- --------------
      7369 SMITH                3200
      7499 ALLEN                6400
      7521 WARD                 5000
      7566 JONES               11900
         : :                       :
Das ist schon sehr interessant - eine SQL-Query, die eigentlich ein UPDATE macht. In Oracle12c kann man nur mit einer WITH bzw. SELECT-Abfrage doch einiges erreichen. Doch was macht man, wenn das nicht erwünscht ist? Zunächst wird das SQL in den meisten Fällen ja von der Anwendung kontrolliert - dass der Endanwender freies SQL eingeben kann, ist meist nicht vorgesehen - und so soll es auch sein.
Doch wenn eine Applikation ein Freefrom SQL Window anbietet? Was kann man tun, damit diese Möglichkeiten nicht missbraucht werden können ...? Nun, da gibt es mehrere Ansätze:
  • Der erste und auch wichtigste Ansatz ist, dass die Datenbanksession, mit der die Anwendung verbunden ist, nur die absolut nötigen Privilegien enthalten sollte. Es ist nach wie vor Best Practice, dass man eben nicht als der Eigentümer der Tabellen angemeldet ist, sondern als ein Connection-User. Dieser hat bestenfalls keinen direkten Zugriff auf Tabellen, sondern nur SELECT-Privilegen auf Views und EXECUTE Privilegien auf PL/SQL Packages (welche die Business-Logik beinhalten). In einem solchen Setup ist das UPDATE per SQL WITH nicht mehr möglich:
    with function get_sal(p_empno in number) return number as
      l_return number;
      pragma autonomous_transaction;
     begin
      -- ACHTUNG: Hier passiert was!
      update emp_v set sal = sal * 2 where empno = p_empno returning sal into l_return;
      commit;
      return l_return;
     end get_sal;
    select empno, ename, get_sal(empno) from emp_v
    /
    
    FEHLER in Zeile 1:
    ORA-01031: Nicht ausreichende Berechtigungen
    ORA-06512: in Zeile 6
    ORA-06512: in Zeile 10
    
  • Die PL/SQL WITH-Klausel selbst bietet ebenfalls einen Schutz an: Funktionen in der WITH-Klausel funktionieren generell nur in der Top-Level-Ebene und nicht in einer Subquery. Bietet man in einer Anwendung also ein Freeform SQL Window, so sollte man die Nutzereingabe am besten als Subquery mit einem SELECT * FROM () einrahmen. Schon kann der Anwender SQL eingeben, aber kein PL/SQL mehr nutzen. Rahmt man es dagegen mit einem SELECT /*+ WITH_PLSQL*/ * FROM () ein, geht es dennoch - das sollte aber nur mit Vorsicht - am besten gar nicht - eingesetzt werden.
    select * from (
      with function get_sal(p_empno in number) return number as
        l_return number;
        pragma autonomous_transaction;
       begin
        -- ACHTUNG: Hier passiert was!
        update emp set sal = sal * 2 where empno = p_empno returning sal into l_return;
        commit;
        return l_return;
       end get_sal;
      select empno, ename, get_sal(empno) from emp
    )
    /
    
    with function get_sal(p_empno in number) return number as
    *
    FEHLER in Zeile 2:
    ORA-32034: Nicht unterstützte Benutzung von WITH-Klausel
    
Zusammengefasst kann man sagen, dass die neue PL/SQL WITH-Klausel einfache SQL-Abfragen wesentlich mächtiger macht. Der Unterschied zwischen "statischem" und "dynamischem" SQL verschwimmt allmählich. Man sollte ganz genau überlegen, wem man in einer Applikation die Möglichkeit zum Freeform SQL gibt und wem nicht. Wendet man in seiner Anwendung grundliegende Designkonzepte an, so ist diese mit WITH-Klausel genauso sicher wie ohne.
Wichtig zu wissen ist die Tatsache, dass die WITH-Klausel innerhalb einer Subquery abgeschaltet ist - die Anwendung kann also ganz einfach verhindert werden, indem man die SQL-Abfrage als Subquery ausführt. Das ist übrigens auch der Grund, warum man eine PL/SQL WITH-Klausel in APEX sehr wohl in einem Classic Report anwenden kann, nicht aber in einem Interactive Report.
Some time ago, I already published a blog posting about the new PL/SQL WITH clause which has been introduced in Oracle12c. Today, I'll blog about this again, since this topic has some fascinating aspects ...
Short recap: The PL/SQL WITH clause has been introduced with Oracle12c (12.1.0.1) and it allows to define PL/SQL functions in the WITH clause of a SQL query. These functions only exist during query execution - there are no dictionary objects. As soon as you can execute a SQL query, you can start embedding PL/SQL into that query. Here's a very simple example:
with function get_sal(p_empno in number) return number as
  l_return number;
 begin
  select sal into l_return from emp where empno = p_empno;
  return l_return;
 end get_sal;
select empno, ename, get_sal(empno) from emp
The result of that very query could have been achieved without PL/SQL WITH as well, but it's here as an example: In the WITH clause, a PL/SQL function named GET_SAL is being defined - and used in the main query - the PL/SQL is very simple (and useless in this case as well), but we can start doing other things. Next, I'll start to play with EXECUTE IMMEDIATE.
with function get_rowcnt(p_tname in varchar2) return number is
    l_rows number;
  begin
    execute immediate 'select count(*) from ' || 
     dbms_assert.sql_object_name('"'||p_tname||'"') 
    into l_rows;
    return l_rows;
  end get_rowcnt;
select table_name, get_rowcnt(table_name) as row_count 
from user_tables;

TABLE_NAME                      ROW_COUNT
------------------------------ ----------
TAB_ARRAY                          100000
CSV_TABLE                           10000
EMP1                                 8192
EBA_SPATIAL_COUNTRIES                  35
DR$FT_EMP$I                            24
EMP                                    17
VERKAUF                                16
:                                       :
Again, we define a PL/SQL function named GET_ROWCNT. This function uses EXECUTE IMMEDIATE to execute another, dynamic SQL query. This single query actually executed as much SQL queries as there are tables in the database schema. Is that static or dynamic SQL ...? That is the question.
Let's go further with this. We could try to do a SQL UPDATE as part of our PL/SQL function. Here's the slighly changed first example, based on the table EMP.
with function get_sal(p_empno in number) return number as
  l_return number;
 begin
  -- ATTENTION: It happens here!
  update emp set sal = sal * 2 where empno = p_empno returning sal into l_return;
  commit;
  return l_return;
 end get_sal;
select empno, ename, get_sal(empno) from emp
/
 
ERROR at line 9:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at line 5
ORA-06512: at line 9
OK, does not seem to work. Really?
with function get_sal(p_empno in number) return number as
  l_return number;
  pragma autonomous_transaction;
 begin
  -- ATTENTION: It happens here!
  update emp set sal = sal * 2 where empno = p_empno returning sal into l_return;
  commit;
  return l_return;
 end get_sal;
select empno, ename, get_sal(empno) from emp
/

     EMPNO ENAME      GET_SAL(EMPNO)
---------- ---------- --------------
      7369 SMITH                3200
      7499 ALLEN                6400
      7521 WARD                 5000
      7566 JONES               11900
         : :                       :
That is interesting: a SQL query, which actually does some Data Manipulation. Beginning with Oracle12c, a simple SQL query can do a lot of things. But what, if that behaviour is not wanted ...? In most cases, the SQL is being controlled by the application - users cannot execute any arbitrary SQL - and rightly so.
But as soon as an application offers some kind of freeform SQL window, we need to think about the PL/SQL WITH clause. What can we do, that users can execute SQL queries, but not perform any PL/SQL logic or DML operations ...? We have two approches:
  • First and most important: The database session must have only those privileges which are absolutely required to do its work. It's best practice for years, that the database connection is not done as the table owner, but as a low-privileged connection user. And this user even doesn't have privileges on the tables directly, but only SELECT privileges on some views and EXECUTE privileges on some PL/SQL packages containing the business logic. In such a setup, the PL/SQL WITH clause could be used, but one cannot damage anything.
    with function get_sal(p_empno in number) return number as
      l_return number;
      pragma autonomous_transaction;
     begin
      -- ATTENTION: It happens here!
      update emp_v set sal = sal * 2 where empno = p_empno returning sal into l_return;
      commit;
      return l_return;
     end get_sal;
    select empno, ename, get_sal(empno) from emp_v
    /
    
    ERROR at line 1:
    ORA-01031: insufficient privileges
    ORA-06512: at line 6
    ORA-06512: at line 10
    
  • Another level of protection is within the PL/SQL WITH clause itself. You can only add PL/SQL code to your WITH clause when you code the top level query. Within a subquery, PL/SQL code cannot be added to the WITH clause. That means, when an application is about to provide a freeform SQL window to its users, the user input should be embedded by SELECT * FROM () so that it becomes a subquery. The end user now can execute arbitrary SQL queries, but no PL/SQL anymore. Take care: You can add the /*+ WITH_PLSQL*/ hint to the top level query and thus make PL/SQL code in subqueries work again. But this should be used with care - or (even better) not at all.
    select * from (
      with function get_sal(p_empno in number) return number as
        l_return number;
        pragma autonomous_transaction;
       begin
      -- ATTENTION: It happens here!
        update emp set sal = sal * 2 where empno = p_empno returning sal into l_return;
        commit;
        return l_return;
       end get_sal;
      select empno, ename, get_sal(empno) from emp
    )
    /
    
    with function get_sal(p_empno in number) return number as
    *
    ERROR at line 2:
    ORA-32034: unsupported use of WITH clause
    
In summary, the new PL/SQL WITH clause makes SQL queries in Oracle12c much more powerful. The difference between static and dynamic SQL becomes rather blurry and vague. When you are about to add some freeform SQL window to your application, please think twice about which end users to give that privilege and to which not. Of course, it's always best to have the application designed by well known best practices, when the privileges are missing a PL/SQL WITH clause cannot damage that much.
Most important to know about the WITH clause is that it does not work in a subquery. This is not simply a missing feature, but an important aspect of database security. You can actually use this fact in order to make your freeform SQL window more secure: Simply embed the users' input as a subquery. BTW: That is the reason why PL/SQL WITH clauses can work within an APEX "classic" report, but not in an interactive report.

Beliebte Postings