22. Januar 2016

APEX ohne APEX: APEX PL/SQL API für Alle!

Das erste Blog-Posting des Jahres 2016 steht unter dem Motto APEX ohne APEX. Was das bedeuten soll? Nun, mit APEX werden eine ganze Reihe PL/SQL Pakete in die Datenbank installiert - das ist logisch, da APEX in PL/SQL geschrieben ist. Die meisten der PL/SQL Pakete sind nur im Zusammenspiel mit APEX sinnvoll - einige andere aber (und genau um die geht es heute), lassen sich auch ohne APEX, aus ganz normalen PL/SQL-Prozeduren, sinnvoll nutzen - und genau um die soll es in diesem Blog Posting gehen. Ich werde nicht alle im Detail erklären; zu einigen gibt es auch schon fertige Howtos, auf die ich einfach verweisen werde.

JSON-Parsing: APEX_JSON (seit APEX 5.0)

Ab dem Release 12.1.0.2 bringt die Datenbank SQL/JSON-Funktionen mit, um JSON mit SQL parsen und verarbeiten zu können. Nutzer einer 11g-Datenbank haben diese Möglichkeit nicht. Und die Möglichkeit, JSON zu generieren oder mit PL/SQL zu verarbeiten, ist in 12.1.0.2 ebenfalls noch nicht vorhanden (die SQL/JSON-Funktionen stehen nur auf SQL-Ebene bereit).

Eine Lösung ist das Paket APEX_JSON, welches ab APEX 5.0 enthalten ist. Es kann auch außerhalb von APEX, in "gewöhnlichen PL/SQL-Prozeduren" problemlos genutzt werden. Zu diesem Thema gab es im letzten Jahr bereits zwei Blog-Postings, auf die ich hier nur verweisen möchte.

ZIP-Archive: APEX_ZIP

Das Paket APEX_ZIP erlaubt es, ZIP-Archive, die als BLOB vorliegen, auszupacken oder neue zu erstellen. Das folgende Beispiel listet alle Dateien, die in einem ZIP-Archiv vorhanden sind. Das Zipfile selbst liegt als BLOB in einer Tabelle.

declare
  l_zipfile_list apex_zip.t_files;
  l_zip_archive  blob;
begin
  select filecontent into l_zip_archive
  from zip_archives where filename = 'p22298106_503_Generic.zip';
 
  l_zipfile_list := apex_zip.get_files(
    p_zipped_blob => l_zip_archive
  );

  dbms_output.put_line('ZIP Archive contains: ' || l_zipfile_list.count || ' files.');
  for f in l_zipfile_list.first..l_zipfile_list.last loop
    dbms_output.put_line(l_zipfile_list(f));
  end loop;
end;
/

ZIP Archive contains: 12312 files.
patch/images/lowerboxL.gif
patch/images/FNDSUBSM.gif
patch/images/wwv_quick_picks2.gif
patch/images/dotted_dbl_line.gif
patch/images/wcenter.gif
patch/images/bottomredright.gif
patch/images/pobcol.gif
:

Analog dazu lassen sich mit APEX_ZIP auch neue ZIP-Archive erstellen. Dazu dienen die Prozeduren ADD_FILE und FINISH.

LDAP-Zugriffe ganz einfach: APEX_LDAP

Zum Zugriff auf einen LDAP-Server gibt es bereits seit langer Zeit das Paket DBMS_LDAP bereit. Allerdings ist der Umgang mit diesem Paket eher umständlich, so dass das APEX-Entwicklerteam für die wichtigsten Aufgaben das Paket APEX_LDAP bereitgestellt hat. Ab APEX 5.0 ist vor allem die Funktion SEARCH interessant.

select dn, name, val from table(
  apex_ldap.search(
    p_host            => 'ldap.mycompany.com'
   ,p_port            => 389
   ,p_search_base     => 'dc=domain,dc=tld'
   ,p_search_filter   => 'cn=Czarski*'
   ,p_attribute_names => 'cn,title,ou,city'
  )
)
/

DN                                            NAME            VAL
--------------------------------------------- --------------- -------------------------
cn=CARSTEN_CZARSKI,L=EMEA,DC=ORACLE,DC=COM    cn              CZARSKI,CARSTEN
cn=CARSTEN_CZARSKI,L=EMEA,DC=ORACLE,DC=COM    cn              CARSTEN,CZARSKI
cn=CARSTEN_CZARSKI,L=EMEA,DC=ORACLE,DC=COM    cn              CCZARSKI_DE
cn=CARSTEN_CZARSKI,L=EMEA,DC=ORACLE,DC=COM    cn              CARSTEN.CZARSKI
cn=CARSTEN_CZARSKI,L=EMEA,DC=ORACLE,DC=COM    title           Senior Leitende/R System
cn=CARSTEN_CZARSKI,L=EMEA,DC=ORACLE,DC=COM    ou              Bu St-Dbtec
cn=CARSTEN_CZARSKI,L=EMEA,DC=ORACLE,DC=COM    city            Munich
:

Setzt man die Möglichkeiten der SQL PIVOT Klausel geschickt ein, so kann man folgendes Ergebnis erzielen.

select cn, org, title,  city
from (
  select * from table(
    apex_ldap.search(
      p_host            => 'ldap.mycompany.com'
     ,p_port            => 389
     ,p_search_base     => 'dc=domain,dc=tld'
     ,p_search_filter   => 'cn=Czarski*' 
     ,p_attribute_names => 'cn,title,ou,city'
    )
  )
) pivot (
  listagg(val,','||chr(10)) within group (order by val) for name in (
    'cn'    as cn,
    'title' as title,
    'ou'    as org,
    'city'  as city
  )
)
/

CN                   ORG                  TITLE                                    CITY
-------------------- -------------------- ---------------------------------------- ----------
CARSTEN,             Bu St-Dbtec          Senior Leitende/R Systemberater/In       Munich
CARSTEN,CZARSKI,
CARSTEN.CZARSKI,
CARSTEN_CZARSKI,
CCZARSKI,
CCZARSKI_DE,
CZARSKI,
CZARSKI,CARSTEN

1 Zeile wurde ausgewählt.

Neben der Funktion SEARCH stehen noch einige Standard-Funktionen bereit, die vor allem für den APEX-Entwickler interessant sind. So führt AUTHENTICATE eine LDAP-Authentifizierung durch; das lässt sich zur Validierung eines LDAP-Passworts nutzen. Die Funkionen MEMBER_OF bzw. MEMBER_OF2 geben ein Array bzw. eine Liste der LDAP-Gruppen zurück, denen ein LDAP-Username zugeordnet ist.

Escaping mit APEX_ESCAPE

Mit APEX_ESCAPE können verschiedenste Sonderzeichen maskiert werden. APEX-Entwickler benötigen das am häufigsten, um HTML-Sonderzeiten wie <, > und & zu maskieren, damit diese auf der Webseite als solche dargestellt und nicht interpretiert werden (das ist insbesondere wichtig beim Schutz vor XSS-Schwachstellen).

Aber APEX_ESCAPE bietet auch andere hilfreiche Funktionen an. So maskiert die Funktion JSON JSON-spezifische Sonderzeichen wie " oder '.

SQL> select apex_escape.json('Text: "Zu Maskieren", ''Zu Maskieren''') escaped from dual;

ESCAPED
------------------------------------------------
Text: \"Zu Maskieren\", \u0027Zu Maskieren\u0027

Analog dazu stehen Funktionen für Reguläre Ausdrücke (REGEXP), LDAP Distinguished Names (LDAP_DN und LDAP-Suchausdrücke (LDAP_SEARCH_FILTER) bereit.

REST-Dienste aufrufen mit APEX_WEB_SERVICE

Das Paket APEX_WEB_SERVICE erlaubt das Konsumieren von REST- und SOAP-Webservices mit PL/SQL. Die Funktion MAKE_REST_REQUEST erlaubt das Angeben einer URL (Webservice Endpoint), einer HTTP-Methode und ggfs. zusätzlicher Parameter zur Authentifizierung oder zum Setzen von HTTP-Headern.

select apex_web_service.make_rest_request(
  p_url       => 'http://sql-plsql-de.blogspot.com/feeds/posts/default?alt=json',
  http_method => 'GET'
) from dual;

APEX_WEB_SERVICE.MAKE_REST_REQUEST('HTTP://SQL-PLSQL-DE.BLOGSPOT.COM/FEEDS/POSTS/
--------------------------------------------------------------------------------
{"version":"1.0","encoding":"UTF-8","feed":{"xmlns":"http://www.w3.org/2005/Atom
","xmlns$openSearch":"http://a9.com/-/spec/opensearchrss/1.0/","xmlns$blogger":"
http://schemas.google.com/blogger/2008","xmlns$georss":"http://www.georss.org/ge
orss","xmlns$gd":"http://schemas.google.com/g/2005","xmlns$thr":"http://purl.org
:

Hier ist jedoch schon etwas Vorsicht geboten: Seit Oracle11g braucht ein Datenbankuser, der einen HTTP-Request machen möchte, vorher eine Netzwerk-ACL, die vom DBA mit dem Paket DBMS_NETWORK_ACL_ADMIN eingerichtet wird. APEX_WEB_SERVICE führt die HTTP-Requests mit den Rechten des APEX-Engine Users (APEX_050000) durch. Sobald diesem User also eine ACL zum Zugriff auf eine Netzwerkressource eingeräumt wurde, können alle anderen Datenbankuser diese Netzwerkressource ansprechen.

Kleinere Helfer im Paket APEX_UTIL

APEX_UTIL ist der "Gemischtwarenladen" der APEX PL/SQL API. Hier finden sich viele verschiedene Funktionen, die teilweise auch rein historisch und aus Gründen der Rückwärtskompatibilität vorhanden sind. Die meisten Funktionen in APEX_UTIL haben einen sehr konkreten APEX-Bezug und deren Verwendung ist außerhalb einer APEX-Applikation nicht sinnvoll. Einige wenige sind aber dabei, mit denen man auch als PL/SQL Entwickler etwas anfangen kann ...

  • GET_HASH errechnet einen Hashwert aus einer gegebenen Liste von VARCHAR2-Werten. Im APEX-Umfeld wird das oft gebraucht, wenn man Änderungen an Tabellendaten feststellen möchte (Lost Update Detection), aber auch in einer PL/SQL Stored Procedure kann das nützlich sein.
    SQL> select apex_util.get_hash(apex_t_varchar2('WERT 1','WERT 2')) as HASH from dual; 
    
    HASH
    --------------------------------------------------------------------------------------
    Ohp8_wWM0lC8rR7Wmz8tzp_sLrSCjqRj5mTo6XMBVqrphnsv2C5Ec9inJHeOqydJLM-z394dOLp8zIjcI0h-zQ
    
  • STRING_TO_TABLE wandelt eine Werteliste, die als separierte Liste als VARCHAR2 Datentyp vorliegt, in ein PL/SQL Assoziatives Array um.
    declare
      l_array APEX_APPLICATION_GLOBAL.VC_ARR2;
    begin
      l_array := apex_util.string_to_table('Wert 1#Wert 2#Wert 3#', '#');
      for i in l_array.first..l_array.last loop
        dbms_output.put_line(l_array(i));
      end loop;
    end;
    
    Wert 1
    Wert 2
    Wert 3
    
  • TABLE_TO_STRING geht den umgekehrten Weg und wandelt eine Werteliste, die als PL/SQL Array vorliegt, in eine separierte Liste als VARCHAR2-Datentyp um.
    declare
      l_array APEX_APPLICATION_GLOBAL.VC_ARR2;
    begin
      l_array(1) := 'Wert A';
      l_array(2) := 'Wert B';
      l_array(3) := 'Wert C';
    
      dbms_output.put_line(apex_util.table_to_string(l_array, '$'));
    end;
    
    Wert A$Wert B$Wert C
    

Viel Spaß beim Ausprobieren der verschiedenen Funktionen und Prozeduren der APEX PL/SQL APIs. Und es lohnt sich, bei künftigen APEX-Releases in die Application Express API Reference hineinzusehen - da werden sich mit Sicherheit Änderungen und Neuerungen zeigen.

The first blog posting in 2016 will be about APEX without APEX. What does that mean? Well, APEX installs several PL/SQL packages into the database (which is obvious since APEX is implemented in PL/SQL). But some packages of the documented APEX PL/SQL API can be used also outside the context of APEX applications. And these are what this blog posting is about. I will not explain all these in the very detail - for some there are already existing how tos which I will refer to.

JSON parsing: APEX_JSON (APEX 5.0 or higher)

Beginning with release 12.1.0.2, the Oracle database contains SQL/JSON functions which allow to parse and process JSON documents with SQL functions. These functions are very powerful and fast, so when it's about parsing JSON in a 12c database, one should always use the native SQL/JSON functions. But there are also several applications still running on 11g. Also, the native JSON capabilities only cover JSON parsing, not JSON generation.

The APEX_JSON package which was introduced with APEX 5.0, comes to a rescue here. It's part of APEX, but it can easily be used outside of APEX as well. In 2015 I already had two blog postings about working with APEX_JSON, so I'll just reference these here.

ZIP-Archives: APEX_ZIP

The APEX_ZIP package allows to work with ZIP archives directly in the database and with PL/SQL. Having a ZIP archive stored as BLOB in the database, the APEX_ZIP package can extract the individual files as BLOBs. The following code example illustrates how this works.

declare
  l_zipfile_list apex_zip.t_files;
  l_zip_archive  blob;
begin
  select filecontent into l_zip_archive
  from zip_archives where filename = 'p22298106_503_Generic.zip';
 
  l_zipfile_list := apex_zip.get_files(
    p_zipped_blob => l_zip_archive
  );

  dbms_output.put_line('ZIP Archive contains: ' || l_zipfile_list.count || ' files.');
  for f in l_zipfile_list.first..l_zipfile_list.last loop
    dbms_output.put_line(l_zipfile_list(f));
  end loop;
end;
/

ZIP Archive contains: 12312 files.
patch/images/lowerboxL.gif
patch/images/FNDSUBSM.gif
patch/images/wwv_quick_picks2.gif
patch/images/dotted_dbl_line.gif
patch/images/wcenter.gif
patch/images/bottomredright.gif
patch/images/pobcol.gif
:

Building a new ZIP archive from existing BLOB data is also possible. The functions ADD_FILE and FINISH serve that purpose.

Access an LDAP server the easy way: APEX_LDAP

To access an LDAP server with PL/SQL, we have the DBMS_LDAP package for a very long time now. But the API is rather cumbersome and not easy to use. Even simple tasks like checking group membership require a lot of (boilerplate) code. So, the APEX development team added the APEX_LDAP package for the most important task. And since APEX 5.0, the SEARCH function is really interesting.

select dn, name, val from table(
  apex_ldap.search(
    p_host            => 'ldap.mycompany.com'
   ,p_port            => 389
   ,p_search_base     => 'dc=domain,dc=tld'
   ,p_search_filter   => 'cn=Czarski*'
   ,p_attribute_names => 'cn,title,ou,city'
  )
)
/

DN                                            NAME            VAL
--------------------------------------------- --------------- ------------------------
cn=CARSTEN_CZARSKI,L=EMEA,DC=ORACLE,DC=COM    cn              CZARSKI,CARSTEN
cn=CARSTEN_CZARSKI,L=EMEA,DC=ORACLE,DC=COM    cn              CARSTEN,CZARSKI
cn=CARSTEN_CZARSKI,L=EMEA,DC=ORACLE,DC=COM    cn              CCZARSKI_DE
cn=CARSTEN_CZARSKI,L=EMEA,DC=ORACLE,DC=COM    cn              CARSTEN.CZARSKI
cn=CARSTEN_CZARSKI,L=EMEA,DC=ORACLE,DC=COM    title           Senior Leitende/R System
cn=CARSTEN_CZARSKI,L=EMEA,DC=ORACLE,DC=COM    ou              Bu St-Dbtec
cn=CARSTEN_CZARSKI,L=EMEA,DC=ORACLE,DC=COM    city            Munich
:

Applying the SQL PIVOT clause to that query result leads to the following ...

select cn, org, title,  city
from (
  select * from table(
    apex_ldap.search(
      p_host            => 'ldap.mycompany.com'
     ,p_port            => 389
     ,p_search_base     => 'dc=domain,dc=tld'
     ,p_search_filter   => 'cn=Czarski*' 
     ,p_attribute_names => 'cn,title,ou,city'
    )
  )
) pivot (
  listagg(val,','||chr(10)) within group (order by val) for name in (
    'cn'    as cn,
    'title' as title,
    'ou'    as org,
    'city'  as city
  )
)
/

CN                   ORG                  TITLE                                    CITY
-------------------- -------------------- ---------------------------------------- ----------
CARSTEN,             Bu St-Dbtec          Senior Leitende/R Systemberater/In       Munich
CARSTEN,CZARSKI,
CARSTEN.CZARSKI,
CARSTEN_CZARSKI,
CCZARSKI,
CCZARSKI_DE,
CZARSKI,
CZARSKI,CARSTEN

1 row selected.

Beyond the SEARCH function, APEX_LDAP provides other functions for standard LDAP requirements. The AUTHENTICATE function does exactly what the name indicates - it just logs into the LDAP server. So this function can be used to validate LDAP username/password combinations. The MEMBER_OF and MEMBER_OF2 functions return the groups, a given LDAP user belongs to, as PL/SQL array or VARCHAR2 separated list, respectively.

Escaping with APEX_ESCAPE

The APEX_ESCAPE package consolidates several escaping functions. For APEX developers this is a very important package, they need it all the time to escape HTML special characters like <, > and & (important to protect an application against XSS (cross site scripting) attacks).

But APEX_ESCAPE offers further helpful functions. The JSON function escapes JSON-specigic characters like " or '. Developers can pass data through that functions when it is to be added to a JSON document.

SQL> select apex_escape.json('Text: "To escape", ''To escape''') escaped from dual;

ESCAPED
------------------------------------------
Text: \"To escape\", \u0027To escape\u0027

APEX_ESCAPE also provides functions to escape for regular expressions (REGEXP), LDAP Distinguished Names (LDAP_DN and LDAP search filters (LDAP_SEARCH_FILTER).

Calling REST services with APEX_WEB_SERVICE

The APEX_WEB_SERVICE package allows to consume REST or SOAP webservices with PL/SQL calls. For instance, the MAKE_REST_REQUEST function calls a REST service at the given URL endpoint with the given HTTP method. Additional parameters allow to pass HTTP haeder fiels or authentication data.

select apex_web_service.make_rest_request(
  p_url       => 'http://sql-plsql-de.blogspot.com/feeds/posts/default?alt=json',
  http_method => 'GET'
) from dual;

APEX_WEB_SERVICE.MAKE_REST_REQUEST('HTTP://SQL-PLSQL-DE.BLOGSPOT.COM/FEEDS/POSTS/
--------------------------------------------------------------------------------
{"version":"1.0","encoding":"UTF-8","feed":{"xmlns":"http://www.w3.org/2005/Atom
","xmlns$openSearch":"http://a9.com/-/spec/opensearchrss/1.0/","xmlns$blogger":"
http://schemas.google.com/blogger/2008","xmlns$georss":"http://www.georss.org/ge
orss","xmlns$gd":"http://schemas.google.com/g/2005","xmlns$thr":"http://purl.org
:

But there is one caveat regarding APEX_WEB_SERVICE: Since Oracle11g, network resources are protected by PL/SQL Network ACLs. In order to connect to a network location, the database user needs to be granted a network ACL by the DBA. The DBA does this with the DBMS_NETWORK_ACL_ADMIN package. APEX_WEB_SERVICE performs its HTTP requests as the APEX engine user (APEX_050000, APEX_040200, ...) - so as soon as the APEX engine user has been granted a network ACL, all database users can connect to that network location with APEX_WEB_SERVICE.

Little helpers in APEX_UTIL

APEX_UTIL is the general store within the APEX PL/SQL packages. It contains many procedures and functions for various purposes. In the meantime, specialized packages like APEX_ESCAPE or APEX_IR have been introduced; the APEX_UTIL procedures are still present for backwards compatibility. Some very few functions might be useful also for the non-APEX PL/SQL developer.

  • GET_HASH calculates a hash value from a given set of VARCHAR2 items. APEX developers need this often to detect changes in underlying database tables (Lost update detection). But within a generic stored procedure, the function might also be useful.
    SQL> select apex_util.get_hash(apex_t_varchar2('WERT 1','WERT 2')) as HASH from dual; 
    
    HASH
    --------------------------------------------------------------------------------------
    Ohp8_wWM0lC8rR7Wmz8tzp_sLrSCjqRj5mTo6XMBVqrphnsv2C5Ec9inJHeOqydJLM-z394dOLp8zIjcI0h-zQ
    
  • STRING_TO_TABLE converts a separated string to a PL/SQL associative ARRAY as follows:
    declare
      l_array APEX_APPLICATION_GLOBAL.VC_ARR2;
    begin
      l_array := apex_util.string_to_table('Item 1#Item 2#Item 3#', '#');
      for i in l_array.first..l_array.last loop
        dbms_output.put_line(l_array(i));
      end loop;
    end;
    
    Item 1
    Item 2
    Item 3
    
  • TABLE_TO_STRING takes the other way around: It converts a PL/SQL associative array to a separated string.
    declare
      l_array APEX_APPLICATION_GLOBAL.VC_ARR2;
    begin
      l_array(1) := 'Item A';
      l_array(2) := 'Item B';
      l_array(3) := 'Item C';
    
      dbms_output.put_line(apex_util.table_to_string(l_array, '$'));
    end;
    
    Item A$Item B$Item C
    

Have fun trying these things out - perhaps one or the other function of the APEX PL/SQL API is useful to you. And for upcoming APEX releases we can expect additions and changes - so it should be worth the effort to have a look into the Application Express API Reference from time to time.

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.

Beliebte Postings