JavaScript in Quick Picks in Oracle APEX

Wer diesen Blog schon öfter besucht hat, wird das Feature der Quick Picks bereits kennen. Diese dienen dazu, Usereingaben zu optimieren, indem mittels eines Klicks auf ein entsprechendes Quick Pick direkt ein Wert in ein Feld eingetragen wird.

 Quickpicks_dynamic_01

Ein Nachteil der Quick Picks ist aber, dass standardmäßig nur vorgefertigte (sprich beim Rendern der Seite bereits definierte) Werte eingesetzt werden können. Auch im letzten Blogeintrag über Quick Picks, in dem wir die Anzahl der Quick Picks dynamisch variierbar gemacht haben, haben wir dieses Problem.
Würde man mit den Quick Picks nun beispielsweise immer den aktuellen Wert eines anderen Feldes übernehmen wollen, so ist dies nicht einfach so möglich.
Angenommen es wäre zum Beispiel gewünscht, den Wert des Feldes „Betrag“ (in der oben angeführten Abbildung) mittels eines Klicks auf einen Quick Pick in das Feld „Betrag bezahlt“ zu übernehmen.
Mit den bisher bekannten Methoden kann man zwar das Feld als Quick Pick angeben (siehe folgende Abbildung), allerdings wurde der zu setzende Betrag bereits beim Rendern der Seite erstellt. War dieser davor „1234“ und wurde nachträglich vom Endbenutzer auf „12345678“ geändert, so wäre auch der Quick Pick immer noch mit dem Wert „1234“ definiert und zwar solange, bis die Region (oder die gesamte Seite) erneut geladen werden.

Quickpick_Eigenschaften_marked

Wie Leser des letzten Beitrages über Quick Picks bereits wissen, arbeiten diese selbst mit JavaScript-Code. Es ist also möglich, beim Erstellen eigener Quick Picks beliebigen JavaScript-Code auszuführen. Anstatt also einen Quick Pick zu generieren, der mittels JavaScript ein Item mit einem vordefinierten Wert befüllt, kann dieser Code auch direkt den Wert zum aktuellen Zeitpunkt aus einem anderen Item auslesen.
Der HTML-Code für das entsprechende Quick Pick würde in diesem Beispiel also wie folgt aussehen:

<span class="apex-quick-picks"><a href="javascript:$s('#CURRENT_ITEM_NAME#',$v('P1_BETRAG'), 'JS-QP')">JS-QP</a></span>

„$s“ ist die in Oracle APEX verwendete JavaScript-Methode zum setzten von Items, während „$v“ die Methode zum Auslesen eines Wertes eines beliebigen Items verwendet werden kann. In diesem Beispiel wird also der Wert aus Item „P1_BETRAG“ in das Item „#CURRENT_ITEM_NAME#“ gesetzt.
„#CURRENT_ITEM_NAME#“ ist nur ein Platzhalter für das gewünschte Item. Wenn dieser HTML-Code nun im Post-Text eines gewünschten Items eingetragen wird (in diesem Fall im Item „P1_BETRAG_BEZAHLT“), wird dieser durch den korrekten Itemnamen ersetzt.
Quickpicks advanced

Um das Layout der Quick Picks korrekt darzustellen muss noch im selben Eigenschaftsfenster im Bereich „Quick Picks“ das Flag „Show Quick Picks“ auf „Yes“ gesetzt werden.

Nun wird der entsprechende HTML-Code mittels CSS in die bekannte Formatierung und Positionierung gebracht. Bei Klick auf den Quick Pick wird immer der aktuelle Wert des Feldes „Betrag“ übernommen, ganz egal, wann dieser geändert wurde.

Dynamic Quick Picks

Um die Quick Picks nun wieder dynamischer zu erstellen, kann man natürlich auf „Shortcuts“ in den Shared Components“ zurückgreifen und die Quick Picks über PL/SQL dynamisch erstellen.

Oracle APEX

Wenn man einen neuen Shortcut erstellt muss als Type „PL/SQL Function Body“ ausgewählt und im Feld „Shortcut“ ein entsprechender PL/SL-Code geschrieben werden.

declare
  v_qp varchar2(32767);
begin
  v_qp := apex_string.format(q'{%1}', 'P1_BETRAG', 'JS-QP');
  return apex_string.format('%0', v_qp);
end;

Create Shortcut

Danach muss der Name des Shortcuts noch im Eigenschaftsfenster des gewünschten Items unter „Post Text“ mittels Anführungszeichen angegeben werden, um APEX mitzuteilen, dass hier der aus dem Shortcut generierte HTML-Code ausgegeben werden soll.

Advanced Post

Man erhält dasselbe gewünschte Ergebnis, es ist aber bei der Umsetzung mit Shortcuts möglich, beliebig viele Quick Picks mit unterschiedlicher Funktion zu erstellen, ohne ständig das Item anpassen zu müssen.

Dynamic Quick Picks

Generell ist es so nun möglich, jeden erdenklichen JavaScript-Code in Quick Picks zu verpacken, es muss nicht einmal eine Quick Pick-Funktionalität dahinterstehen.

Dynamische Quick Picks in Oracle APEX

Wer seine Web-Applikation in Oracle APEX auf Usability optimiert, wird wahrscheinlich schon einmal über das Feature der sogenannten Quick Picks gestoßen sein.
Unter Quick Picks versteht man vordefinierte Usereingaben, die ein Endanwender mittels Mausklick auslösen kann und das entsprechende Feld mit einem vordefinierten Wert befüllt, ohne diesen extra eintippen zu müssen.

 Quickpicks_dynamic_01

Konfiguriert werden diese Quick Picks über das Eigenschafts-Fenster des jeweiligen Page-Items.

Quickpick Eigenschaften

Quick Picks können also die Eingabe des Endbenutzers ein wenig beschleunigen, allerdings sieht es auf den ersten Blick so aus, als ob nur statische Werte vergeben werden können. Dies ist nicht ganz korrekt, denn mit der üblichen Notation in Oracle APEX können auch Werte aus beliebigen Items angesprochen werden (Kaufmännisches Und + Item-Name + Punkt), dies sieht zum Beispiel wie folgt aus:

Quickpick_Eigenschaften_marked

Diese Art der Definition führt dazu, dass der Wert aus dem jeweiligen Item als Quick Pick gesetzt wird.

Quickpick dynamic zweiter wert

Einzige Bedingung hierfür ist allerdings, dass der Wert bereits beim Aufbau der Seite gesetzt wird, denn der Quick Pick wird vorab mit dem Wert des entsprechenden referenzierten Items erstellt und ändert sich nicht dynamisch mit. Auch ist die Anzahl der Quick Picks vorab auf die statisch definierten Labels und Values beschränkt, es ist also auf diese Art beispielsweise nicht möglich dynamisch einmal zwei und einmal drei Quick Picks unter einem Feld anzuzeigen.

Um nun beliebig viele Werte dynamisch als Quick Picks anzuzeigen, muss man etwas tiefer in die Trickkiste greifen. Um dies zu ermöglichen, bedient man sich dem Wissen, dass die Quick Picks über eine CSS-Klasse positioniert und gestaltet werden und der entsprechende HTML-Code einfach an das gewünschte Item angehängt wird. Mit diesem Wissen ist es nun möglich, die Quick Picks eigenständig zu erstellen.

Für die hier verwendete Testseite wurde eine simple Tabelle erstellt, welche beliebig viele Werte und Bezeichnungen für Quick Picks enthält. Die Tabelle wurde QP_TAB genannt und beinhaltet aktuell folgende Werte:

Werte Tabelle Quickpick

Ziel soll es nun sein für jede Zeile dieser Tabelle einen separaten Quick Pick für ein Item zu generieren. (Allgemein ist es egal, woher die Daten für die Quick Picks stammen, einzige Bedingung ist, dass man diese mittels PL/SQL ermitteln kann.)

Um die Anzahl der Quick Picks nun auch dynamisch umzusetzen zu können, benötigt man ein weiteres Oracle APEX Feature: die sogenannten „Shortcuts“. Diese befinden sich in den Shared Components wie im folgenden Screenshot ersichtlich ist.

Oracle Apex

Klickt man nun auf Shortcuts, kann man auf der Folgeseite ein neues Objekt erstellen.

Create Shortcut

Wichtig hierbei ist, dass als „Type“ „PL/SQL Function Body” ausgewählt wird und dann im Feld „Shortcut“ ein entsprechender Code geschrieben wird, der die gewünschten Daten in eine entsprechende HTML-Struktur bringt. Der Code für das hier verwendete Beispiel sieht nun folgendermaßen aus:

declare
  v_qp varchar2(32767);
begin
  for rec_qp in (select bezeichnung, wert from qp_tab) loop
    if v_qp is not null then
      v_qp := v_qp || ', ';
    end if;
      v_qp := v_qp || apex_string.format(q'{%1}', rec_qp.wert, rec_qp.bezeichnung);
  end loop;
  return apex_string.format('%0', v_qp);
end;

Wichtig ist hier eigentlich nur, dass die einzelnen Links (die resultierenden Quick Picks) in einem Span-Container der CSS-Klasse „apex-quick-picks“ liegen.

Der eigentliche Link ist dann Javascript-Code, welcher mit „$s“ das Setzen eines Items mit der entsprechenden Angabe des Wertes umsetzt. (Der Platzhalter „#CURRENT_ITEM_NAME#“ wird im folgenden Schritt aufgelöst.)

Wieder zurück auf der Seite kann man im Eigenschaftsfenster des gewünschten Items nun im „Advanced“-Bereich im „Post Text“-Feld den Namen des zuvor erstellten Shortcuts in Anführungszeichen eintragen. Dadurch wird der durch den Shortcut generierte HTML-Text nach dem Item im Quellcode eingefügt. Oracle APEX weiß nun auch, dass der Platzhalter „#CURRENT_ITEM_NAME#“ mit dem Namen des Items ersetzt wird (diese Funktionalität des Ansprechens des Item-Namens ist über den Post Text allgemein möglich).

Bei den Quick Picks werden diesmal keine statischen Werte angegeben (da der Code für die Quick Picks ja bereits durch den Shortcut an das Item angehängt wurde). Um aber die Positionierung des durch den Shortcut erstellten HTML-Code auch korrekt anzuzeigen, wird das Flag „Show Quick Picks“ auf „Yes“ gesetzt.

So erhält man die dynamisch erstellten Quick Picks:

Würde man in Tabelle QP_TAB nun einen weiteren Eintrag erfassen, würde dieser auch entsprechend auf der APEX-Seite als weiterer Quick Pick erscheinen, ohne dass man dafür das Item anpassen müsste.

Die einzige Limitierung der Anzahl der Quick Picks entsteht durch die Zeichenanzahl des VARCHAR2 im angelegten Shortcut.

Oracle APEX: Custom error messages mit APEX_ERROR

Die Haupt-Anwendungsfälle im Zusammenhang mit der Ausgabe von Fehlermeldungen in APEX-Applikationen dürften die meisten Entwickler nach einer verhältnismäßig kurzen Einarbeitungszeit kennen:

  • Entweder werden Validations ausgeführt, schlagen an und sollen den Endanwender darüber informieren, dass eine entsprechende Bedingung verletzt wurde.
  • Oder während der Ausführung eines Page Processes tritt ein Fehler auf, über den der Endanwender in Kenntnis gesetzt werden soll.

 

Wollten Entwickler in älteren APEX-Versionen eigene Fehlermeldungen ausgeben, geschah dies häufig über die Nutzung der Variable apex_application.g_print_success_message unter zusätzlicher Angabe von Style-Informationen:

apex_application.g_print_success_message := '<span style="color:red;">Achtung: Bei der Aufbereitung der Datei-Inhalte sind insgesamt 23 Fehler aufgetreten, die Daten konnten daher nicht importiert werden.</span>';

Mittlerweile jedoch stellt das APEX-eigene Package APEX_ERROR viel elegantere Möglichkeiten für den Umgang mit eigenen Fehlermeldungen zur Verfügung:

 

Die überladene Prozedur ADD_ERROR bietet verschiedene Varianten an um eigene Fehlermeldungen auf den Error Stack zu legen, die Darstellung übernimmt dabei APEX, so dass die Notwendigkeit zu expliziten Style-Angaben entfallen kann. Ein entsprechender Prozess im Page Processing könnte damit beispielsweise so aussehen:

DECLARE
 -- define some variables
 v_status NUMBER;
 ...
BEGIN
-- do some fancy stuff
...
 v_status := my_schema.my_package.my_function(p_var => '[MY_VALUE]');

IF v_status > 0 THEN
apex_error.add_error(p_display_location => apex_error.c_inline_in_notification,
p_message => 'Achtung: Es ist ein Fehler aufgetreten, so dass…');
 END IF;
EXCEPTION
WHEN OTHERS THEN
-- do some error logging
...
RAISE;
END;

Das eigentliche Potential entfaltet dieses Package aber dann, wenn man auf auftretende Fehler speziell reagieren will, beispielsweise weil gewisse Fehler häufiger auftreten und die Seite speziell für bestimmte Fehlerfälle bestimmte zusätzliche Button-Aktionen oder Auswertungen zur Verfügung stellen soll. Dafür kann man statt der Variable v_status im obigen Code ein Hidden Item P12_STATUS setzen und nach dem Ausführen der Prozesse über einen Branch die Seite neu aufrufen und das Item dabei übergeben. Der Aufruf von APEX_ERROR geschieht nun nicht mehr im Page Processing sondern im Page Rendering und nur dann wenn P12_STATUS einen Wert > 0 enthält

IF :P12_STATUS = '1' THEN
apex_error.add_error(p_display_location => apex_error.c_inline_in_notification,
 p_message => 'Achtung: Das Hochladen der Daten war nicht erfolgreich, so dass…');
ELSIF :P12_STATUS = '2' THEN
 apex_error.add_error(p_display_location => apex_error.c_inline_in_notification,
p_message => 'Achtung: Das Parsen der Daten ist fehlgeschlagen, so dass…');
ELSE
...
END IF;

Zusätzlich besteht nun natürlich die Möglichkeit status-abhängig zusätzliche Items etc. anzubieten – zum Beispiel ein Button um die Daten erneut hochzuladen für P12_STATUS = ‚1‘ oder ein Report mit den Parsing-Fehlern für P12_STATUS = ‚2‘.

Oracle APEX Ein Stolperstein im Upgrade auf Version 5.1

Oracle APEX: Ein Stolperstein im Upgrade auf Version 5.1

Beim Upgrade von Oracle APEX Applikationen auf die schon etwas ältere Version 5.1 wird gerne ein kleines Detail übersehen – vor allem auch deswegen weil die Auswirkungen nur dann sichtbar sind, wenn man alte und neue Version wirklich genau testet und vergleicht oder alternativ die Release Notes aufmerksam liest:

Im Abschnitt 1.4.4 der Release Notes befindet sich der Absatz:

End Date Displayed Inclusive — In release 5.0, the CSS calendar considered the end date of an all-day event as exclusive (similar to the jQuery FullCalendar Plugin).
In release 5.1, the end date is inclusive like all other Oracle Application Express components.

Das bedeutet, dass für die korrekte Darstellung eines mehrtägigen Termins in einer Calendar-Region in Version 5.0 folgende Abfrage notwendig war:

SELECT 'SomeCSSClass'         AS cal_class,
       t1.appointment_start   AS cal_date_from,
       t1.appointment_end + 1 AS cal_date_upto,
       'SomeLabel'            AS cal_label,
       'SomeLink'             AS cal_link
FROM   appointment_table t1

Mit dem Upgrade auf 5.1 entfällt die Notwendigkeit für das un-intuitive „+ 1“ und die Abfrage reduziert sich auf:

SELECT 'SomeCSSClass'         AS cal_class,
       t1.appointment_start   AS cal_date_from,
       t1.appointment_end     AS cal_date_upto,
       'SomeLabel'            AS cal_label,
       'SomeLink'             AS cal_link
FROM   appointment_table t1

Vor allem beim Überspringen von Applikations-Versionen kommt es dann mitunter vor, dass die Zwischenschritte ein wenig stiefmütterlich behandelt werden und sich Probleme wie dieses einschleichen.

Daher bietet es sich bei Oracle APEX Upgrades immer an, noch einmal einen kurzen Blick in die entsprechenden Release Notes zu werfen.

OAUTH2 Authentifizierung für ORDS REST Services

OAUTH2 Authentifizierung für ORDS REST Services

Oracle bietet über den ORDS (Oracle REST Data Services) die Möglichkeit REST Services in der Datenbank zu erstellen.

Um diese auch entsprechend zu schützen kann man den Zugriff darauf mittels einer OAUTH2 Authentifizierung einschränken. Die Authentifizierung und Autorisierung arbeiten mit Benutzern, Rollen und Berechtigungen (Privileges).

Der Aufbau ist dabei wie folgt:

Authentifizierung und Autorisierung

Abbildung 1: Rollen

Das bedeutet, ein Benutzer kann mehrere Rollen besitzen, welche ihm wiederum jeweils mehrere Berechtigungen zuweisen. Eine Berechtigung wiederum gilt für 1 oder mehrere Module (=Services) und darin eingebettete Ressourcen. Die Einschränkung auf Ressourcen wird allerdings nicht über eine Verknüpfung festgelegt, sondern einen Textfilter, welcher mit Regular Expressions arbeitet.

Die einzelnen Elemente können folgendermaßen angelegt werden:

Rolle

  • Per PL/SQL mit einem Aufruf der Prozedur create_role im Package ords.
  • Über das entsprechende REST Service Interface in APEX

Abbildung 1: Rollen

Berechtigung

  • Per PL/SQL mit einem Aufruf der Prozedur create_privilege im Package ords. Hier wird gibt es zwei Aufrufe, entweder wird nur 1 Rolle übergeben oder ein Array an Rollen.
  • Über das entsprechende REST Service Interface in APEX

Abbildung 2: Berechtigungen

Abbildung 3: Berechtigungen Detail

Berechtigungen Details

Zuweisungen der Berechtigung zum Modul werden mittels der Prozedur set_module_privilege im Package ords erstellt oder wie in Abbildung 3 zu sehen über das REST Service Interface in APEX.

Zuweisungen der Berechtigung zur jeweiligen Ressource werden mittels der Prozedur create_privilege_mapping im Package ords erstellt oder wie in Abbildung 3 zu sehen über das REST Service Interface in APEX.

 

ACHTUNG!!!

Es scheint hier einen Bug zu geben wenn ein Universal-Model verwendet wird – sprich „/*“.

Dieser wird dann nicht modulmäßig eingeschränkt und wirkt sich dann auch auf alle APEX Applikationen aus, welche dann potentiell nicht mehr aufgerufen werden können.

Benutzer

Benutzer können AUSSCHLIESSLICH per PL/SQL mittels der Prozedur create_client im Package oauth erzeugt werden!

Die Rolle muss dann ebenfalls per PL/SQL zugewiesen werden mittels der Funktion grant_client_role im Package oauth.

Die Tatsache, dass die Benutzer nicht über das Interface angelegt werden können, muss unbedingt berücksichtig werden.

Einsatz der angelegten Objekte

Um das Service nun zu nutzen muss eine Authentifizierung durchgeführt werden. Beim Anlegen des OAUTH Clients wird ein Benutzername übergeben. Mit diesem können dann die benötigten Benutzerdaten aus der Tabelle user_ords_clients geholt werden z.B. mit folgendem Statement:

SELECT client_id, client_secret FROM user_ords_clients WHERE name = 'NAME_DES_USERS';

Die client_id und das client_secret werden dann wiederum benötigt um den OAUTH Zugriffstoken zu erhalten. Dazu wird standardmäßig ein eigenes Webservice bereitgestellt, welches mit einer speziellen URL erreicht werden kann. Die URL setzt sich aus dem Basispfad zusammen und wird am Ende mit oauth/token ergänzt – z.B. https://www.myserver.at/ords/apex/oauth/token

Dieser URL werden client_id und client_secret als HTTP-Authentifizierung mitgegeben (User:Passwort), im Payload wird „grant_type=client_credentials“ mitgegeben. Als Antwort erhält man ein JSON mit folgenden Attributen:

  • access_token: der Token selbst
  • token_type: ist immer „bearer“ – muss mit angegeben werden mit dem Token
  • expires_in: Gültigkeitsdauer in Sekunden – standardmäßig 1 Stunde (dieser Wert kann nicht individuell je Service angepasst werden, nur global für alle Services dieser ORDS Instanz)

Beim Aufruf der eigentlichen Services welches durch die OAUTH Authentifizierung geschützt wird, wird dann im Header des Requests folgendes mitgegeben:

„Authorization: Bearer [OAUTH Token]“

OAUTH Token ist der vorhin mittels Webservice generierte Token. Falls der Token nicht mehr gilt (sprich die Gültigkeit abgelaufen ist oder der Token schlicht falsch ist) – gibt das Service einen HTTP 403 Forbidden Fehler zurück.

Oracle APEX: WWV_FLOW_FND_USER_FK-Verletzung beim Importieren von Workspaces

Ich bin jüngst im Rahmen eines APEX-Projekts auf ein Problem gestoßen, für das ich im Oracle Support Portal keinen einzigen Eintrag finden konnte – nach einigem Überlegen und Testen aber auf eine relativ einfache Lösung gekommen bin.

Ausgangslage

Das erste Arbeitspaket im Rahmen des Projektes ist fertig entwickelt und soll dem Kunden zur Abnahme in der dafür von diesem bereitgestellten Test-Umgebung deployed werden. Da es in dieser Umgebung keine APEX-Entwicklungs-Umgebung gibt, sind die Sourcen per SQLplus, PL/SQL-Developer oder mit ähnlichen Tools zu deployen.

Nach der fehlerfreien Erstellung von Schemas, Tabellen, Packages etc. folgt der Import des APEX-Workspaces; das aus APEX exportierte Script hierfür schaut sinngemäß wie folgt aus:

set define off verify off feedback off
whenever sqlerror exit sql.sqlcode rollback
--------------------------------------------------------------------------------
--
-- ORACLE Application Express (APEX) export file
--
-- You should run the script connected to SQL*Plus as the Oracle user
-- APEX_050100 or as the owner (parsing schema) of the application.
--
-- NOTE: Calls to apex_application_install override the defaults below.
--
--------------------------------------------------------------------------------
begin
wwv_flow_api.import_begin (
p_version_yyyy_mm_dd=>'[DATE]'
,p_default_workspace_id=>276...332
);
end;
/
prompt WORKSPACE 276...332
--
-- Workspace, User Group, User, and Team Development Export:
-- Date and Time: [DATE_AN_TIME]
-- Exported By: ADMIN
-- Export Type: Workspace Export
-- Version: 5.1.2.00.09
-- Instance ID: 714...698
--
-- Import:
-- Using Instance Administration / Manage Workspaces
-- or
-- Using SQL*Plus as the Oracle user APEX_050100

begin
wwv_flow_api.set_security_group_id(p_security_group_id=>276...332);
end;
/
----------------
-- W O R K S P A C E
-- Creating a workspace will not create database schemas or objects.
-- This API creates only the meta data for this APEX workspace
prompt Creating workspace LEHM...
begin
wwv_flow_fnd_user_api.create_company (
p_id => 276...354
,p_provisioning_company_id => 276...332
,p_short_name => '[SHORT_NAME]'
,p_display_name => '[DISPLAY_NAME]'
,p_first_schema_provisioned => '[SCHEMA_NAME]'
,p_company_schemas => '[SCHEMA_NAMES]'
,p_account_status => 'ASSIGNED'
,p_allow_plsql_editing => 'Y'
,p_allow_app_building_yn => 'Y'
,p_allow_packaged_app_ins_yn => 'Y'
,p_allow_sql_workshop_yn => 'Y'
,p_allow_websheet_dev_yn => 'Y'
,p_allow_team_development_yn => 'Y'
,p_allow_to_be_purged_yn => 'Y'
,p_allow_restful_services_yn => 'Y'
,p_source_identifier => '[IDENTIFIER]'
,p_path_prefix => '[PATH_PREFIX]'
,p_files_version => 1
);
end;
/
----------------
-- G R O U P S
--
prompt Creating Groups...
begin
wwv_flow_api.create_user_groups (
p_id => 927...937,
p_GROUP_NAME => 'OAuth2 Client Developer',
p_SECURITY_GROUP_ID => [ID],
p_GROUP_DESC => 'Users authorized to register OAuth2 Client Applications');
end;
/
begin
wwv_flow_api.create_user_groups (
p_id => 927...936,
p_GROUP_NAME => 'RESTful Services',
p_SECURITY_GROUP_ID => [ID],
p_GROUP_DESC => 'Users authorized to use RESTful Services with this workspace');
end;
/
begin
wwv_flow_api.create_user_groups (
p_id => 927...912,
p_GROUP_NAME => 'SQL Developer',
p_SECURITY_GROUP_ID => [ID],
p_GROUP_DESC => 'Users authorized to use SQL Developer with this workspace');
end;
/
prompt Creating group grants...
----------------
-- U S E R S
-- User repository for use with APEX cookie-based authentication.
--
prompt Creating Users...
begin
wwv_flow_fnd_user_api.create_fnd_user (
p_user_id => '276...332',
p_user_name => 'ADMIN',
p_first_name => '',
p_last_name => '',
p_description => '',
p_email_address => '[MAIL_ADDRESS]',
p_web_password => '[PASSWORD_STRING]',
p_web_password_format => '[PASSWORD_FORMAT]',
p_group_ids => '',
p_developer_privs => 'ADMIN:CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL',
p_default_schema => '[SCHEMA_NAME]',
p_account_locked => 'N',
p_account_expiry => to_date('[DATE_AND_TIME]','YYYYMMDDHH24MI'),
p_failed_access_attempts => 0,
p_change_password_on_first_use => 'Y',
p_first_password_use_occurred => 'Y',
p_allow_app_building_yn => 'Y',
p_allow_sql_workshop_yn => 'Y',
p_allow_websheet_dev_yn => 'Y',
p_allow_team_development_yn => 'Y',
p_allow_access_to_schemas => '');
end;
/
[MORE_USER_CREATION_STEPS]
prompt Check Compatibility...
begin
-- This date identifies the minimum version required to import this file.
wwv_flow_team_api.check_version(p_version_yyyy_mm_dd=>'[DATE]');
end;
/

begin wwv_flow.g_import_in_progress := true; wwv_flow.g_user := USER; end; 
/
[SOME_EMPTY_ANONYMOUS_BLOCKS]
begin
wwv_flow_api.import_end(p_auto_install_sup_obj => nvl(wwv_flow_application_install.get_auto_install_sup_obj, false));
commit;
end;
/
set verify on feedback on define on
prompt ...done

Das Problem

An der Stelle, wo der erste User-Account erstellt werden soll, schlägt die Script-Ausführung dann fehl:

ORA-02291: Integritäts-Constraint (APEX_050100.WWV_FLOW_FND_USER_FK) verletzt – übergeordneter Schlüssel nicht gefunden

ORA-06512: in „APEX_050100.WWV_FLOW_FND_USER_INT“, Zeile 2010

ORA-06512: in „APEX_050100.WWV_FLOW_FND_USER_API“, Zeile 328

ORA-06512: in Zeile 2

 

Eine Suche im Oracle Support Portal nach WWV_FLOW_FND_USER_FK liefert (heute am 14.01.2020) exakt Null Ergebnisse – es ist also Eigeninitiative gefragt.
Eine Web-Suche offenbart recht schnell, dass der genannte Foreign Key ein Verweis auf den Workspace ist – was einigermaßen überraschend ist, da der Abschnitt der Workspace-Erstellung aus dem Script mit PL/SQL procedure successfully completed erfolgreich durchgelaufen war.

 

Die Lösung

Auf die Lösung bin ich erst gestoßen als ich im PL/SQL-Developer jeden Schritt einzeln ausgeführt habe. Bereits nach dem ersten Block erkannte der Developer eine aktive offene Transaktion – dies ist an entsprechenden Icons zu erkennen.

Die nachfolgenden Schritte verursachten keinerlei Probleme – bis die erste User Group importiert werden sollte. Hier fiel dann auf, dass dem Developer zufolge die Transaktion verschwand. Und der nachfolgende Import von User Accounts schlug daraufhin mit der bereits beschriebenen Fehlermeldung fehl.

Als ich die User-Group-Erstellung ausließ, funktionierte auch der User-Account-Import wieder problemlos. 😊

Da die User Groups im Rahmen des genannten Projekts nicht weiter verwendet werden, ist die Lösung ausreichend: das Entfernen der Erstellung der User Groups aus dem Workspace-Erstellungs-Scripts.

Die Ursache

Die genaue Ursache lässt sich ohne den entschlüsselten Code des Packages WWV_FLOW_API nur schwer ermitteln – höchstwahrscheinlich fehlt in der Procedure CREATE_USER_GROUPS aber ein RAISE um einen auftretenden Fehler weiterzureichen…

 

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.