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.

Beliebte Postings