Parsen von JSON-Daten in einer Oracle-Datenbank

Wer als (APEX-)Entwickler heutzutage Web-Applikationen erstellt, stößt immer häufiger auf Anforderungen der Art „Integration von Daten einer fremden Quelle“.

Also beispielsweise die Integration von Wetterdaten eines Wetterservices. Während vor einigen Jahren überwiegend XML als Format für derartige Datenaustausche verwendet wurde, gehen aktuelle Entwicklungen immer mehr dazu über an dieser Stelle JSON-Strukturen zu verwenden.

Vor allem in etwas älteren APEX-Applikationen kann das mitunter zu Performance-Problemen führen.

In Oracle Datenbank Version 11 gab es etwa noch gar keine native Unterstützung für die Verarbeitung von JSON-Daten – weder in PL/SQL noch in SQL.

Die erste Möglichkeit überhaupt war das Package APEX_JSON, das mit APEX 5.0 eingeführt wurde und mangels Alternativen recht weite Verbreitung fand.

Erst mit 12.1.0.2 begann die Unterstützung in SQL mit der Einführung von JSON_TABLE, mit 12.2 in PL/SQL durch die Einführung der Types JSON_ELEMENT_T, JSON_OBJECT_T etc.

Seither werden diese Funktionalitäten laufend verbessert und erweitert, so dass es sich je nach konkretem Anwendungsfall also durchaus lohnen kann, früher implementierte JSON-Verarbeitung mittels APEX_JSON zumindest zu hinterfragen und bei Bedarf durch native Funktionalitäten abzulösen.

Ein kleiner Test soll die Unterschiede in der Performance aufzeigen:

Vorbereitungen

Um keine Test-Daten generieren zu müssen, verwenden wir an dieser Stelle einen Service des US Geological Survey (USGS): den Erdbeben-Katalog (API Dokumentation: https://earthquake.usgs.gov/fdsnws/event/1/ ). Ein kleines Script erzeugt eine Tabelle für die Testdaten und befüllt diese auch gleich mit einem CLOB mit 20.000 Einträgen im JSON-Format:

CREATE TABLE test_json (id NUMBER GENERATED ALWAYS AS IDENTITY,
data CLOB);
INSERT INTO test_json (data)
VALUES (apex_web_service.make_rest_request(p_url => 'https://earthquake.usgs.gov/fdsnws/event/1/query?format=geojson&limit=20000',
p_http_method => 'GET'));
SELECT t1.id,
dbms_lob.getlength(t1.data)
FROM test_json t1;

ID DBMS_LOB.GETLENGTH(DATA) 
--- -------------------------
1 15727921

Zur Verdeutlichung der Performance-Unterschiede reicht folgende Anforderung aus: Wir wollen ermitteln wie viele Elemente/Erdbeben das Array FEATURES innerhalb der JSON-Daten enthält (auch wenn wir aufgrund der Parameter des Webservice-Calls bereits erahnen können, dass die Antwort 20.000 lauten wird).

Bei der Verwendung von APEX_JSON gehen wir wie folgt vor:

DECLARE
v_count NUMBER;
v_json_data CLOB;
v_time TIMESTAMP;
BEGIN
SELECT t1.data
INTO v_json_data
FROM test_json t1
WHERE t1.id = 1;

v_time := SYSTIMESTAMP;
apex_json.parse(p_source => v_json_data);
dbms_output.put_line('Parsing Time: ' || EXTRACT(SECOND FROM (SYSTIMESTAMP - v_time)));

v_time := SYSTIMESTAMP;
v_count := apex_json.get_count(p_path => 'features' );
dbms_output.put_line('Feature Count: ' || v_count);
dbms_output.put_line('Counting Time: ' || EXTRACT(SECOND FROM (SYSTIMESTAMP - v_time)));
END;

Parsing Time: 23,200156
Feature Count: 20000
Counting Time: 0,000012

Für eine Applikation, die „nur mal eben live ausgeben soll“, wie viele Erdbeben es in einem bestimmten Zeitraum gegeben hat, sind 23 Sekunden zu viel.

Sehen wir uns daher die Verwendung neuerer Funktionalitäten in PL/SQL an:

DECLARE
v_count NUMBER;
v_json_data CLOB;
v_json_object json_object_t;
v_time TIMESTAMP;
BEGIN
SELECT t1.data
INTO v_json_data
FROM test_json t1
WHERE t1.id = 1;

v_time := SYSTIMESTAMP;
v_json_object := json_object_t.parse(jsn => v_json_data);
dbms_output.put_line('Parsing Time: ' || EXTRACT(SECOND FROM (SYSTIMESTAMP - v_time)));

v_time := SYSTIMESTAMP;
v_count := v_json_object.get_array('features').get_size;
dbms_output.put_line('Feature Count: ' || v_count);
dbms_output.put_line('Counting Time: ' || EXTRACT(SECOND FROM (SYSTIMESTAMP - v_time)));
END;

Parsing Time: 6,513685
Feature Count: 20000
Counting Time: 0,000067

Das ist schon deutlich schneller als in der Variante mit APEX_JSON – für moderne Applikationen aber sind 6sec für vermeintlich einfache Datenabfragen zu lange.

Daher versuchen wir nun die Anzahl primär per SQL zu ermitteln:

DECLARE
v_count NUMBER;
v_time TIMESTAMP;
BEGIN
v_time := SYSTIMESTAMP;

SELECT COUNT(*)
INTO v_count
FROM test_json t1, JSON_TABLE(data, '$.features[*]' COLUMNS (row_number FOR ORDINALITY,
mag NUMBER PATH '$.properties.mag',
eq_id VARCHAR2(30) PATH '$.id')) AS t2
WHERE t1.id = 1;

dbms_output.put_line('Feature Count: ' || v_count);
dbms_output.put_line('Total Time: ' || EXTRACT(SECOND FROM (SYSTIMESTAMP - v_time)));
END;

Feature Count: 20000
Total Time: 0,165338

Obwohl hier alles in einem einzigen Schritt ausgeführt wird, braucht die gesamte Abarbeitung der Anfrage nur mehr knappe 0,2sec und ist damit um einen Faktor deutlich jenseits der 100 schneller als die APEX_JSON-Implementierung.

Wer also größere JSON-Objekte in 12.2+ noch mit APEX_JSON verarbeitet, sollte dringend einmal über eine kleine Frischzellenkur nachdenken…

APEX-Version 19.1 veröffentlicht

Am 29. März 2019 wurde die angekündigte APEX-Version 19.1 von Joel Kallmann offiziell vorgestellt und zum Download freigegeben.

Gegenüber dem letzten Blog-Eintrag ist vor allem ein weiteres Feature erwähnenswert: Die Möglichkeit Daten aus unterschiedlichen Datei-Formaten direkt zu integrieren.

Deklarativ steht diese Möglichkeit vorerst nur über den SQL Workshop im Data Workshop innerhalb der Utilities zur Verfügung.
Wie genau dieser Prozess ausschaut hat Carsten Czarski in seinem Blog-Eintrag Quick and Easy Data Loading with APEX 19.1 vorgestellt.

Der für das Parsen und den Import verwendete Code steht Entwicklern allerdings auch in Form des Packages APEX_DATA_PARSER zur Verfügung – und kann damit beispielsweise in eigene Applikationsteile integriert werden. Auch für die Nutzung dieses Packages gibt es bereits einen entsprechenden Blog-Beitrag.

Außerdem wurde mittlerweile in einem Statement of Direction ein Ausblick auf die in APEX 19.2 zu erwartenden Features gewährt.

Neben der Erweiterung der neuen Daten-Parser- und -Importe werden vor allem verbesserte Popup LOVs und erweiterte Shared LOVs die Usability für Endanwender erhöhen – und auch die angekündigte Überarbeitung der Filtermöglichkeiten von Reports wird vor allem unter all jenen Applikations Usern Freude verursachen, die SQL nicht oder nur eingeschränkt beherrschen und daher mit den aktuellen Möglichkeiten beispielsweise eines Interactive Reports wenig anfangen können.

 

Oracle Apex 19.1 Early Adopter

Oracle APEX 19.1 – Early Adopter

Eine gute Nachricht für alle APEX-Fans, die ihre Software aktuell halten wollen und/oder auf neue Features warten:
Seit kurzem gibt es die Early Adopter von APEX 19.1. Unter 19.1 Early Adopter – Features gibt es erste Informationen zu den kommenden Inhalten, wie etwa:

  • REST Enabled Forms (Anzeigen und Bearbeiten von Daten aus Remote-Quellen),
  • Upgrades von jQuery und JET Charts,
  • Deklarative Erweiterungen des Interactive Grid oder auch
  • ein dunkles Layout für die APEX-Entwicklungsumgebung.

Aktuell gibt es noch keinen Download, sodass man sich die Änderungen momentan in einem Workspace bei Oracle anschauen muss. Und auch die verfügbaren Informationen sind noch etwas spärlich – werden aber üblicherweise sukzessive erweitert.

Oracle Apex Dictionary

APEX_DICTIONARY – ein kleines (häufig unbekanntes) Helferlein

Irgendwann einmal kommt wohl jeder APEX-Entwickler an einen Punkt, an dem ihm die deklarativen Wizards innerhalb des Application Builders nicht mehr ausreichen.

Beispielsweise dann, wenn man eine ganze Reihe von Reports auf einer Page definiert hat, deren Inhalte man nun möglichst einfach exportieren will (die DSGVO lässt an dieser Stelle schön grüßen).

Wahrscheinlich hat auch jeder bis dahin einmal mitbekommen, dass APEX-Views gibt, die man zu diesem Zweck konsultieren kann – wenn man nur nicht ständig vergessen würde wie die Views heißen (bei aktuell deutlich über 100 Views aber auch nicht weiter verwunderlich).

In diesem Fall kann einem das Apex Dictionary helfen.

Selbst ein View funktioniert das Dictionary ähnlich wie der allgemeinere View DICT bzw. DICTIONARY – geht in der Funktionalität aber weiter. Wo das allgemeine Dictionary nur View-Namen und Kommentare enthält, stellt das APEX Dictionary einige sehr interessante zusätzliche Informationen zur Verfügung – Spaltennamen zum Beispiel oder den Namen des übergeordneten Views.

Das hat zwar zur Folge, dass man für eine einfache View-Suche…

SELECT *
FROM   apex_dictionary
WHERE  comment_type = 'View'

…absetzen oder zumindest einen Filter …

WHERE column_id = 0 

… verwenden sollte – ermöglicht andererseits in Verbindung mit den aufgelisteten Daten-Views selbst aber sehr einfache Möglichkeiten, um umfangreiche Qualitätssicherungs-Abfragen zu erststellen.

Die Suche nach Pages ohne Checksum Protection gerät so fast schon zum Kinderspiel.

Das APEX Dictionary ist übrigens auch im Application Builder selbst integriert und dort direkt in den Workspace Utilities zu finden.

Es kann auch nicht schaden, nach Erscheinen neuer APEX-Versionen einfach mal so einen Blick in das Apex Dictionary zu werfen um sich über Neuerungen zu informieren.

Von Version 5.0 zu 18.2 sind beispielsweise 32 neue Views hinzugekommen – primär für das Interactive Grid und die neuen Web-Service-Tools.

Regular Expression in APEX Suche

Regular Expressions in der APEX Suche

Die integrierte Suche in Oracle APEX beherrscht auch Regular Expressions.

Das funktioniert auf jeden Fall ab Version 4.1 aufwärts, vermutlich auch schon in Version 4.0 und eventuell auch schon darunter.

Die ganze Sache funktioniert eigentlich sehr einfach und benötigt folgende Syntax in der Anwendung:

regexp:([REGULAR_EXPRESSION])

Regular Expressions in der Apex Suche

Beispielanwendung Regular Expressions

Wobei [REGULAR_EXPRESSION] beliebige Regular Expressions sein können, so wie sie auch in den SQL Funktionen wie z.B. regexp_like verwendet werden. Wichtig zu beachten ist dabei, dass die Anführungszeichen wegfallen. Einige Beispiele dafür wären

  1. regexp:(P\d{4}_NUMMER)
  2. regexp:(P100_.*)
  3. regexp:(P1_EMPNO|P1_EMPNAME)
  4. regexp:(DD.MM.YYYY|YYYY-MM-DD)

Die Bedeutung dieser Regular Expressions ist wie folgt:

  1. Alle Vorkommen von Items die mit P und einer 4-stelligen Zahl beginnen (Standardnamen auf Seite mit 4-stelliger Nummer) und _NUMMER heißen (um alle „Nummer“ Items zu finden)
  2. Alle Vorkommen von Items die mit P100_ beginnen (z.B. um zu prüfen ob diese Items auch außerhalb der Seite 100 aufgerufen werden)
  3. Alle Vorkommen von Items mit den Namen P1_EMPNO oder P1_EMPNAME
  4. Alle Vorkommen von Date Format Models DD.MM.YYYY oder YYYY-MM-DD (um zum Beispiel alle Konvertierungsfunktionen mit diesen Format Models zu finden)

Regular Expressions erweitern das Suchen innerhalb der Applikation um mächtige Möglichkeiten.

Da Regular Expressions aber auf der Performance Seite eher schlecht abschneiden sollten sie nur verwendet werden wenn es wirklich Sinn macht.
Das kann zum Beispiel sein, wenn nicht ganz sicher ist welches Item an welchen Stellen übergeben wird, oder wenn eine Liste mit bestimmt benannten Items benötigt wird.

Die Suche mit einem einfachen Begriff wird deutlich flotter laufen und ist daher zu bevorzugen, wenn möglich.

Oracle LiveSQL um SQL und PL/SQL Scripts zu testen

SQL oder PL/SQL Skripte mit Oracle LiveSQL testen

Wer kennt das Problem nicht, dass man SQL oder PL/SQL Skripte testen will und man hat vielleicht keine Datenbank zur Verfügung oder will die Ergebnisse mit anderen teilen?

Javascript (und in gewissem Sinne auch HTML und CSS) hat dafür z.B. JSFiddle.

Oracle hat nun nachgezogen und sein entsprechende Pendant „LiveSQL“ dazu online gestellt.
Unter https://livesql.oracle.com kann man nun genau das tun.

Alles was man benötigt ist ein kostenloster Oracle Account, der innerhalb von wenigen Minuten angelegt ist.

Entwickelt in der Entwicklungsumgebung Oracle Application Express (APEX) und auf einer Oracle Datenbank Version 12.1 gehostet, kann man in LiveSQL eigene Schemata erstellen und seine Skripte darauf laufen lassen.
Doch das ist noch nicht alles. Man kann seine eigenen Skripte speichern und sie dann entweder öffentlich mit allen LiveSQL Nutzern teilen oder privat mittels Key nur bestimmten Personen zugänglich machen.

Weiters gibt es bereits zu etlichen Themen Skripte/Tutorials.
Zum aktuellen Zeitpunkt gibt es in Summe 133 Skripte/Tutorials auf LiveSQL. Diese umfassen die klassischen Themen wie SQL und PL/SQL allgemein, aber auch fortgeschrittenere Bereiche wie Analytic Functions sowie XML und JSON in der Datenbank.

Die Bedienung ist simpel und auf das Wesentliche beschränkt gehalten.
Die Seite ist auch auf mobilen Endgeräten nutzbar und passt sich der Displaygröße entsprechend an.

Oracle APEX 5.0

Oracle APEX 5.0 released

Seit 15. April 2015 ist Oracle Application Express (APEX) 5.0 offiziell verfügbar. Schon im Vorfeld war es möglich sich mit den neuen Möglichkeiten im Rahmen einer Early Adopter Version vertraut zu machen. DBConcepts setzt intern die neue Version bereits für Projekte ein, auch von Kunden wurde schon Interesse an einem Upgrade gezeigt.

Sehr hervorzuheben sind folgende neue Features:

Ein komplett überarbeiteter Page Designer

Der neue Page Designer spielt seine Stärken nach einer kurzen Umstellungsphase voll aus. Sehr positiv fällt auf, dass nun alles auf einer Seite bearbeitbar ist. Keine Seitenwechsel mehr um Prozesse oder Regionen zu bearbeiten. Zusätzlich ergänzt durch ein „Rückgängig machen“ der letzten Schritte sind damit Bearbeitungen der Seite deutlich rascher möglich als früher. Das spart Zeit in der Entwicklung, gerade auch wenn eine Fernwartung auf Kundensystemen gemacht werden muss und jeder Seitenwechsel potentiell Zeit kostet.

Universal Theme

Ein neues „Universal-Thema“, das speziell für APEX 5 entwickelt wurde. Leicht anzupassen und anpassungsfähig an unterschiedliche Geräte (Stichwort Desktop-Mobil) ohne Expertenkenntnisse in HTML, CSS oder Javascript.

Modale Dialoge

APEX 5 bietet nun out-of-the-box modale Dialoge an. Mit wenigen Klicks sind so aufwändige „Fenster im Fenster“ möglich. Das eröffnet ganz neue Möglichkeiten in der Applikationsentwicklung was Usability und Ease-of-use betrifft.

Mobile Display

Zusätzlich zum Universalen Thema gibt es neue intelligente Möglichkeiten für mobiles Reporting. Dieses passt sich automatisch an schmale Bildschirme an indem Spalten ein- und ausgeblendet werden und zusätzliche Umbrüche automatisiert eingefügt werden.

 Kalender

Ein komplett neuer Kalender wurde integriert. Deutlich leichter anzupassen und mit Unterstützung für Tages-, Wochen- und Monatsansicht sowie Unterstützung für Drag and Drop Operationen.

Das war nur ein kurzer Überblick über die neuen Möglichkeiten.

Wir werden im Lauf der Zeit auf Basis der Erfahrung in unseren Projekten weitere interessante Informationen zu den einzelnen Punkten hier im Blog veröffentlichen.

 

JavaScript für APEX Entwickler

JavaScript für APEX Entwickler

JavaScript ist längst nicht mehr nur ein „nice-to-have“ in Oracle APEX. Spätestens seit Dynamic Actions, Cascading LOVs, Plugins und Mobilen Applikationen ist es ein essentieller Bestandteil jeder Oracle APEX Applikation. Das kann ganz einfach geprüft werden, indem im Browser JavaScript deaktiviert wird. Der Effekt ist sofort sichtbar – Seiten laden nicht und Buttons funktionieren nicht mehr.

Am 14. Februar 2014 fand bei DBConcepts in Rahmen der AOUG „Technisches Frühstück“ eine Veranstaltung mit dem Thema „Javascript für APEX Entwickler – Gewappnet für Dynamic Actions und Plugins“ statt, bei der unser Oracle APEX Spezialist Christoph Hillinger aus dem Nähkästchen plauderte.

Wie bei einem Hausbau wurde beim technischen Frühstück JavaScript als Basis definiert, das Fundament und die Grundmauern. Da JavaScript allen Teilnehmern bekannt war, ging der Vortrag mit einer detaillierten Info zu JQuery los. JQuery ist eine der größten und weit verbreitetsten JavaScript Bibliotheken. JQuery dient primär dem traversieren des HTML-Contents, dem Finden und Veränderung von Elementen und deren Attributen sowie der Event-Verwaltung und Effekt-Erzeugung. Sie wird auch in APEX als Grundlage verwendet.

Live Demos

Es folgten einige live Demonstrationen in JFiddle.net für verschiedene JQuery Funktionalitäten.

Nach JQuery wurden die APEX JavaScript Bibliothek genauer betrachtet.

Diese beinhaltet grundsätzlich JQuery, erweitern es aber um komfortable Zusatzfunktionen und APEX-spezifische Funktionen. Ein Blick in die API macht erfahrenen APEX-Entwicklern sofort klar, dass hier auch die standardisierten Dynamic Actions wie „Set Value“, „Enable“ und „Disable“, „Show“ und „Hide“ definiert sind. Dazu kommen noch einige weiter Funktionen für die Aufrufsteuerung von Seiten sowie globalen Prozesse und Plugins.

Es folgten weitere Demos wie JavaScript ganz grundsätzlich abseits von Plugins eingesetzt werden kann.
Diese Demos sind in der Demo-Applikation (http://apex.oracle.com/pls/apex/f?p=45071) zu finden:

  • Scrollable Reports: Oft ist es unpraktisch das ganze Fenster scrollen zu müssen weil ein Report zu breit ist, da damit jedes Mal ein Teil des Menüs aus dem Bild verschwindet – besser wäre es nur den Report zu scrollen – und dabei über einen Scrollbalken oben und unten zu verfügen.
  • Hide/Show Regions: Das hört sich simpel an ist aber auf mehrere Arten möglich – die Demo Lösung wurde auf leichte Erweiterbarkeit ausgerichtet.
  • Big Text in Reports: Manchmal enthalten Reports sehr lange Texte. Diese können die Zeilen sehr stark in die Höhe ziehen – was den Report unübersichtlich und die Navigation mühsam macht. Dem wird Abhilfe geschaffen, indem die Spalte bei der Abfrage in eine entsprechende Struktur gepackt werden und dann je Zeile mittels Link ein- und ausblendbar ist.

Dynamic Actions

Es folgte eine kurze Demo der bekannten vorgefertigtenDynamic Actions. Auch diese Funktionalität ist in der Demo-Applikation zu finden. Dort wurde auch auf die Möglichkeit eingegangen, Aktionen auf unterschiedliche Arten zu setzen. So können Werte mit oder ohne Submit von Items gesetzt werden, was aber in weiterer Folge bei Verweisen auf andere Seiten beachtet werden muss.

Ebenso kann ein Insert in eine Tabelle mittels Submit und Page-Process durchgeführt werden, oder mittels Ajax in einer Dynamic Action. Auch hier hängt die Wahl der Methode von vielen Faktoren ab, wie auch Überlegungen zur Sicherheit.

Plugins

Als nächster Punkt folgten Plugins – wohl eine der wichtigsten Neuerung der letzten APEX Versionen. Sehr wichtig ist, dass Plugins nicht per se gratis sind – sondern die Kosten von der genutzten Lizenz  abhängen und hier immer genau geprüft werden muss, welche Lizenz verwendet wird. Es folgten live Demonstrationen zur Erstellung von einem Item Plugin sowie einem Dynamic Action Plugin.

Zum Abschluss wurde kurz auf Unterschiede zwischen mobiler und Desktop Applikation hingewiesen sowie einige hilfreiche Tools für den Entwicklungsprozess im Allgemeinen vorgestellt.

Die JavaScript für APEX Entwickler Präsentation downloaden >>

 

Oracle Apex 5.0 Early Adopter

Erste Apex 5.0 Version zum Testen

apex_2

Oracle hat eine Early Adopter Version von Apex Version 5.0 auf https://apexea.oracle.com zur Verfügung gestellt.

Einige Highlights kurz zusammengefasst:

  • Modal Page Unterstützung
  • Eine komplett neue IDE
  • Ein komplett neues Datei handling
  • Neue Themes
  • JQuery, AnyChart und andere third party library wurden aktualisiert
  • und vieles mehr…
Oracle APEX 4.2.3

Oracle APEX Release 4.2.3 veröffentlicht

Die Oracle Application Express Release 4.2.3.00.08 wurde am 16. September 2013 veröffentlicht.
Ab dem genannten Datum ist APEX daher in dieser Version mit allen entsprechenden Verbesserungen verfügbar.

Wenn bereits APEX in Version 4.2.0 oder höher installiert ist, kann man den Patch von My Oracle Support downloaden (Patchnummer #17347169) .

Der Patch bietet etliche Bugfixes – die vollständige Liste ist unter Apex 4.2.3 Relase Notes abrufbar.