15. März 2016

APEX Connect 2016: Early Bird endet heute am 15. März

In diesem Blog Posting möchte ich nochmals auf die APEX Connect hinweisen: Am 15. März endet der Early Bird Tarif für die APEX-Konferenz im deutschsprachigen Raum. Drei Tage lang (vom 26. bis 28. April) stehen APEX und PL/SQL im Mittelpunkt. Nach dem großen Erfolg im letzten Jahr ist die Agenda wiederum hochkarätig und mit Top-Sprechern aus der deutschen und internationalen APEX-Community besetzt. So kann man sich jetzt schon auf die Keynote von Mike Hichwa, dem "Vater" und APEX-Entwicker der ersten Stunde. Am besten also noch heute anmelden.

Ich hoffe, wir sehen uns dort.

1. März 2016

Veränderung ...

Dieses Blog-Posting behandelt mal kein technisches SQL oder PL/SQL Thema, sondern ist eines in eigener Sache.

Das Beste zuerst: Ich bin extrem glücklich, ab dem 1. März 2016 (also ab heute) Teil des Oracle Application Express Entwicklerteams zu sein.

Das ganze fällt mit meinem "15. Oracle-Geburtstag" zusammen. Am 1. März 2001 habe ich im Presales bei Oracle Deutschland angefangen und arbeite nun also 15 Jahre mit der deutschsprachigen Entwicklercommunity zu Themen wie APEX, aber auch XML DB, JSON, Spatial, SQL-Funktionen, PL/SQL und andere Themen - rund um die Datenbank - zusammen. Dieses breite Themenspektrum und die Zusammenarbeit mit den Kunden, Partnern und der Community in Deutschland war sehr bereichernd und hat viel Spaß gemacht.

Fast ebenso lange arbeite ich schon mit dem APEX-Entwicklerteam zusammen - für das Release 4.2 hatte ich die Gelegenheit, den Sample Geolocation Showcase zu erstellen, welcher ja Teil der Packaged Applications ist. Nun werde ich noch tiefer in Application Express einsteigen und (hoffentlich) die eine oder andere Idee und Vorstellung für euch umsetzen können - das wird auf jeden Fall herausfordernd, spannend und ich freue mich sehr drauf. Von der Bildfläche verschwinden werde ich nicht, auf Konferenzen wie der DOAG APEX Connect oder der DOAG Jahreskonferenz im November, aber auch auf anderen Veranstaltungen könnt Ihr mich treffen - ich freue mich auf die Diskussionen und Gespräche.

This blog posting is not about some SQL or PL/SQL topic, but about myself.

First (and best) things first: I'm happy and excited to join the Oracle Application Express Development Team as of March 1st, 2016 (as of today).

This happens at my 15th Oracle anniversary - at March 1st, 2001, I started in the presales organization of Oracle Germany. In the last 15 years, I worked on database-centric development and on topics like SQL, PL/SQL, XML, Spatial, JSON and (of course) Application Express. This broad spectrum and working together with the vibrant community in Germany was a lot of fun.

I'm working with APEX and the APEX team since the first public release, HTML DB 1.5. For APEX 4.2 I had the opportunity to add the Sample Geolocation Showcase to the collection of packaged applications in APEX and now my focus will be on further developing and improving Application Express. Having the chance to bring some of my ideas directly into the APEX product is very exciting and cool.

I won't be totally off the grid, I still plan to attend conferences like DOAG APEX Connect, the annual DOAG conference or others. I'm looking forward to meeting you there and to talk and discuss with you.

19. Februar 2016

"gzip" und "gunzip" - in der Datenbank: UTL_COMPRESS

Heute möchte ich auf ein PL/SQL Paket hinweisen, was es schon sehr, sehr lange in der Datenbank gibt, was aber ein wenig in Vergessenheit geraten ist: UTL_COMPRESS. Die Funktionen LZ_COMPRESS und LZ_UNCOMPRESS sind dabei die Datenbank-Äquivalente zu gzip und gunzip auf dem Betriebssystem.

Probiert das doch gleich einmal aus. Ihr könnt in eurer Datenbank sehr einfach an einen BLOB kommen, indem Ihr die XMLDB-Konfiguration abruft. Ihr bekommt ein XML-Dokument zurück. Das folgende Beispiel ruft zuerst als CLOB ab, dann als BLOB (UTL_COMPRESS arbeitet nur mit BLOBs).

select xmlserialize(document dbms_xdb.cfg_get() as clob) XML_CLOB from dual;

XML_CLOB
--------------------------------------------------------------------------------
<xdbconfig xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd" xmlns:xsi="http://w
ww.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.oracle.com/x
db/xdbconfig.xsd http://xmlns.oracle.com/xdb/xdbconfig.xsd">
  <sysconfig>

select xmlserialize(document dbms_xdb.cfg_get() as blob) XML_BLOB from dual;

XML_BLOB
--------------------------------------------------------------------------------
3C786462636F6E66696720786D6C6E733D22687474703A2F2F786D6C6E732E6F7261636C652E636F
6D2F7864622F786462636F6E6669672E7873642220786D6C6E733A7873693D22687474703A2F2F77
77772E77332E6F72672F323030312F584D4C536368656D612D696E7374616E636522207873693A73
6368656D614C6F636174696F6E3D22687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F78
64622F786462636F6E6669672E78736420687474703A2F2F786D6C6E732E6F7261636C652E636F6D

Die Größe des BLOBs (das brauchen wir gleich noch) ...

select dbms_lob.getlength(
  xmlserialize(document dbms_xdb.cfg_get() as blob)
) original_len from dual;

ORIGINAL_LEN
------------
       13513

Jetzt komprimieren wir mal ...

select utl_compress.lz_compress(
  xmlserialize(document dbms_xdb.cfg_get() as blob)
) from dual;

1F8B0800000000000003E55BDD73DB36127FCF5FA1E974EEE16E28CA76ECF3E554CE246D33D79BB4
75E36426F7E481C095841824680094E8FCF5B70049F143944DD282CF9DF38BC5FDC0FEB0BB582E41
709E850B2AE2255B4DB288C7EA87EFD65A276F7CDF5E4D852494C3948AC847417F273CCD54F85DAE
F126536CA7B5DD6EA7DB33545BF9A7B3D989FFE5D70FD7740D11F158AC348929A096626F94257E10
946826E2014627FDE105AF2693B9BA5739C95CE135CA7B11C93CB282E0E47CEED7AF2B094A109FA7

select dbms_lob.getlength(
  utl_compress.lz_compress(
    xmlserialize(document dbms_xdb.cfg_get() as blob)
  )
) zipped_len from dual;

ZIPPED_LEN
----------
      1874

Nun schreiben wir den komprimierten BLOB ins Filesystem - dazu könnt Ihr bspw. mein Package für das Filesystem und Betriebsystem-Kommandos hernehmen. Auf Unix-Ebene kann die Datei dann mit gunzip ausgepackt werden.

select file_pkg.get_file('/home/oracle/xdbconfig.xml.gz').write_to_file(
  utl_compress.lz_compress(
    xmlserialize(document dbms_xdb.cfg_get() as blob)
  )
) bytes_written from dual;

BYTES_WRITTEN
-------------
         1874

Auf Unix-Ebene kann die Datei dann mit gunzip ausgepackt werden. Man sieht dann die XML-Inhalte des urspränglichen BLOBs ...

$ gunzip xdbconfig.xml.gz
$ more xdbconfig.xml
<xdbconfig xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd" xmlns:xsi="http://w
ww.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.oracle.com/x
db/xdbconfig.xsd http://xmlns.oracle.com/xdb/xdbconfig.xsd">
  <sysconfig>
  :

Und der umgekehrte Weg funktioniert genauso. Insbesondere wenn es darum geht, BLOBs oder CLOBs über das Netzwerk zum Datenbankserver zu übertragen, kann dieses Paket eine wertvolle Hilfe sein. Wenn man LOB-Inhalte vor dem Absenden an die Datenbank mit gzip verpackt und in der Datenbank mit UTL_COMPRESS.LZ_UMCOMPRESS wieder auspackt, so lässt sich die meist kostbare Netzwerk-Bandbreite gleich viel besser ausnutzen.

Auch APEX-Entwickler sollten sich UTL_COMPRESS ansehen, sobald sie mit Datei-Uploads arbeiten. Zwar ist APEX seit der Version 5.0 in der Lage, ZIP-Archive zu verarbeiten, mit GZIP-Dateien kann APEX_ZIP aber nicht umgehen. Genau hier kann man sehr schön mit dem UTL_COMPRESS Paket arbeiten. Es ist schnell eingesetzt und kann in manchen Situationen ein echter Quick Win sein. Schaut's euch an.

This blog posting will be about a PL/SQL package which is part of the database for a very long time, but which is also forgotten by most Oracle users: UTL_COMPRESS. Its funktions LZ_COMPRESS and LZ_UNCOMPRESS are the equivalents to the OS utilities gzip and gunzip.

It's very easy to try these functions out. First, you need a BLOB to compress. If you don't have some BLOB values ready in a table, you can easily generate one by retrieving the XML configuration of your database. This is actually an XML document.

select xmlserialize(document dbms_xdb.cfg_get() as clob) XML_CLOB from dual;

XML_CLOB
--------------------------------------------------------------------------------
<xdbconfig xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd" xmlns:xsi="http://w
ww.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.oracle.com/x
db/xdbconfig.xsd http://xmlns.oracle.com/xdb/xdbconfig.xsd">
  <sysconfig>

select xmlserialize(document dbms_xdb.cfg_get() as blob) XML_BLOB from dual;

XML_BLOB
--------------------------------------------------------------------------------
3C786462636F6E66696720786D6C6E733D22687474703A2F2F786D6C6E732E6F7261636C652E636F
6D2F7864622F786462636F6E6669672E7873642220786D6C6E733A7873693D22687474703A2F2F77
77772E77332E6F72672F323030312F584D4C536368656D612D696E7374616E636522207873693A73
6368656D614C6F636174696F6E3D22687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F78
64622F786462636F6E6669672E78736420687474703A2F2F786D6C6E732E6F7261636C652E636F6D

Determine the BLOB size (we'll need this later on) ...

select dbms_lob.getlength(
  xmlserialize(document dbms_xdb.cfg_get() as blob)
) original_len from dual;

ORIGINAL_LEN
------------
       13513

OK - let's compress.

select utl_compress.lz_compress(
  xmlserialize(document dbms_xdb.cfg_get() as blob)
) from dual;

1F8B0800000000000003E55BDD73DB36127FCF5FA1E974EEE16E28CA76ECF3E554CE246D33D79BB4
75E36426F7E481C095841824680094E8FCF5B70049F143944DD282CF9DF38BC5FDC0FEB0BB582E41
709E850B2AE2255B4DB288C7EA87EFD65A276F7CDF5E4D852494C3948AC847417F273CCD54F85DAE
F126536CA7B5DD6EA7DB33545BF9A7B3D989FFE5D70FD7740D11F158AC348929A096626F94257E10
946826E2014627FDE105AF2693B9BA5739C95CE135CA7B11C93CB282E0E47CEED7AF2B094A109FA7

select dbms_lob.getlength(
  utl_compress.lz_compress(
    xmlserialize(document dbms_xdb.cfg_get() as blob)
  )
) zipped_len from dual;

ZIPPED_LEN
----------
      1874

Then we'll write the compressed BLOB to the file system - you might use my Package for file system and OS commands for this, but working with UTL_FILE or similar is also OK.

select file_pkg.get_file('/home/oracle/xdbconfig.xml.gz').write_to_file(
  utl_compress.lz_compress(
    xmlserialize(document dbms_xdb.cfg_get() as blob)
  )
) bytes_written from dual;

BYTES_WRITTEN
-------------
         1874

Now you can inflate the file with the gunzip utility - and having done this, you can see the original XML contents.

$ gunzip xdbconfig.xml.gz
$ more xdbconfig.xml
<xdbconfig xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd" xmlns:xsi="http://w
ww.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.oracle.com/x
db/xdbconfig.xsd http://xmlns.oracle.com/xdb/xdbconfig.xsd">
  <sysconfig>
  :

The other way around does, of course, also work. So you can upload a gzip-compressed LOB to your database and before storing it into your table, you run the UTL_COMPRESS.LZ_UNCOMPRESS function on it. This might be a very nice approach to better utilize the network bandwith to your database.

UTL_COMPRESS is also interesting for APEX developers - when building APEX applications, you sometimes have to build file uploads. APEX can deal with ZIP archives since the APEX_ZIP package has been introduced with APEX 5.0. But APEX_ZIP cannot handle GZIP files - to process these, UTL_COMPRESS comes to your rescue. I think, the UTL_COMPRESS package is a real quick win, using it is extemely easy and you get an immediate effect.

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.

Beliebte Postings