20. Juli 2015

Native Compilation für PL/SQL

Nachdem ich, in meinem monatlichen Oracle Developer Monthly Webcast, im Juli etwas zum Thema PL/SQL Native Compilation vorgestellt hatte, kam mir die Idee, das ganze gleich auf diesem Blog etwas ausführlicher zu behandeln.
Seit geraumer Zeit bietet die Oracle-Datenbank an, PL/SQL Code in Native Code zu übersetzen, natürlich mit dem Ziel, dass der Code schneller ausgeführt wird ("Native Compilation"). Standardmäßig wird beim Kompilieren eines PL/SQL-Objekts ein "Bytecode" generiert, der dann von einer "PL/SQL VM" ausgeführt wird. Natürlich speichert die Datenbank den Quellcode ebenso ab - denn der kann ja über die View USER_SOURCE angesehen werden. So weit, so gut.
In früheren Versionen war das Native Compile noch sehr aufwändig. Das Konzept war, dass der PL/SQL-Code zunächst in C-Code übersetzt wird; aus dem generiert ein C-Compiler dann Native-Code für die jeweilige Plattform, auf welcher die Datenbank läuft. Das hatte allerdings einige Probleme.
  • C-Compiler sind nicht auf allen Plattformen frei verfügbar; oft müssen sie teuer eingekauft werden
  • Der Setup für Compiler und Linker musste mit Makefiles eingerichtet werden, dass war je nach Plattform sehr aufwändig
  • Das entstandene Shared Object (.so oder .dll) lag außerhalb der Datenbank; das hat Auswirkungen auf Backup und Recovery oder allgemein gesagt: auf den Betrieb der Datenbank
Daher wurder der Vorgang mit dem Release 11.1 der Datenbank massiv vereinfacht. Das wichtigste ist, dass externe Compiler und Linker aus dem Vorgang eliminiert wurden - die Datenbank generiert den Native Code selbstständig. Demzufolge fallen auch alle einzurichtenden Makefiles weg. Und schließlich kann der Native-Code als Teil der Datenbank gespeichert werden, so dass keine externen Objekte mehr nötig sind. Das alles wird durch Umstellen eines Session-Parameters aktiviert: PLSQL_CODE_TYPE. Ein gutes Beispiel, um den Effekt einer Native-Compilation zu zeigen, sind rechenintensive Programme - hier mit den Fibonacci-Zahlen ein üblicher Verdächtiger als Beispiel.
create or replace function fib (n number) return number is
begin
  if (n = 1) or (n = 2) then
     return 1;
  else
     return fib(n - 1) + fib(n - 2);
  end if;
end fib;
/
sho err

Funktion wurde erstellt.

select fib(32) from dual
/

   FIB(32)
----------
   2178309

1 Zeile wurde ausgewählt.

Abgelaufen: 00:00:01.88
Standardmäßig wird PL/SQL-Code nicht nativ kompiliert; im Moment sollten wir also die Laufzeit von interpretiertem Code sehen. Das kann aber auch anhand der Data Dictionary View USER_PLSQL_OBJECT_SETTINGS geprüft werden.
SQL> select name, type, PLSQL_CODE_TYPE from user_plsql_object_settings where name='FIB'

NAME                           TYPE         PLSQL_CODE_TYPE
------------------------------ ------------ --------------------
FIB                            FUNCTION     INTERPRETED

1 Zeile wurde ausgewählt.
Dann stellen wir die Funktion auf Native-Code um - also Parameter umstellen und das PL/SQL Objekt neu kompilieren.
SQL> alter session set PLSQL_CODE_TYPE=NATIVE;

Session wurde geändert.

SQL> alter function FIB compile;

Funktion wurde geändert.

SQL> select name, type, PLSQL_CODE_TYPE from user_plsql_object_settings where name='FIB';

NAME                           TYPE         PLSQL_CODE_TYPE
------------------------------ ------------ --------------------
FIB                            FUNCTION     NATIVE

1 Zeile wurde ausgewählt.
Nun sollte man ja auch eine Änderung im Laufzeitverhalten sehen ... wir erinnern uns: der folgende Aufruf brauchte im interpretierten Modus 1,88 Sekunden.
select fib(32) from dual;

   FIB(32)
----------
   2178309

1 Zeile wurde ausgewählt.
 
Abgelaufen: 00:00:00.98
Das ganze ist schon mal doppelt so schnell - und wir werden das noch schneller machen. Vorher jedoch noch eine Anmerkung zu den Erwartungen, die man an eine nativ kompilierte PL/SQL-Funktion oder Prozedur haben kann. Bei rechenintensivem Code (wie dem Beispiel hier) verspricht native Compilation gute Ergebnisse. Keine besonderen Vorteile bringt die Native Compilation, wenn die Prozedur sich größtenteils mit I/O beschäftigt - und dieses I/O kann die Arbeit mit Datenbankobjekten (SELECT und DML), dem Netzwerk (UTL_HTTP, UTL_TCP, UTL_SMTP) oder auch dem Dateisystem (UTL_FILE) sein. In all diesen Fällen wird der Effekt der Native Compilation nicht so stark ins Gewicht fallen. Auch hierzu ein Beispiel: Diese Prozedur selektiert die View ALL_OBJECTS und addiert die String-Längen aller Objektnamen zusammen.
create or replace function get_len_allobjects return number is
  l_len number := 0;
begin
  for i in (select object_name from all_objects) loop
    l_len := l_len + length(i.object_name);
  end loop;
  return l_len;
end;
/ 
sho err
Der Löwenanteil dieser Prozedur ist die Cursor-Loop, mit der sie sich durch die Inhalte der View ALL_OBJECTS durcharbeitet; also I/O. Probieren wir es aus - erst mal führen wir das ganze interpretiert aus ...
SQL> alter session set plsql_code_type=interpreted;

SQL> alter function get_len_allobjects compile;

SQL> select get_len_allobjects from dual;

GET_LEN_ALLOBJECTS
------------------
           1739009

1 Zeile wurde ausgewählt.

Abgelaufen: 00:00:11.95
... danach als Native Code ...
SQL> alter session set plsql_code_type=native;

SQL> alter function get_len_allobjects compile;

SQL> select get_len_allobjects from dual;

GET_LEN_ALLOBJECTS
------------------
           1739009

1 Zeile wurde ausgewählt.

Abgelaufen: 00:00:11.42
Man sieht keinen gewaltigen Unterschied - und das ist absolut logisch, denn der PL/SQL-Teil, der hier vom Native-Compile profitieren kann, ist im Vergleich zur Cursor-Loop verschwindend gering ... solche Kandidaten lassen sich auch per Native Compile nicht schneller machen.
Aber zurück zu obigem Beispiel mit den Fibonacci-Zahlen. Der PL/SQL-Code verwendet den Datentypen NUMBER, welcher in der Oracle-Datenbank zwar die größten Zahlenraum und die bestmögliche Präzision bietet; die Frage ist aber, ob man das wirklich braucht. Speziell für ganzzahlige Variablen kommen in PL/SQL die Datentypen PLS_INTEGER (BINARY_INTEGER ist ein Synonym) oder SIMPLE_INTEGER in Frage. Besonders der SIMPLE_INTEGER, den es seit Version 11.2 gibt, ist interessant. Er bietet den gleichen Zahlenraum wie PLS_INTEGER, verhält sich im Gegensatz zu diesem aber bei numerischen Überläufen anders und er kennt kein SQL NULL. Er ist vergleichbar mit int-Datentypen in Programmiersprachen außerhalb der Datenbank. Das ist für Native-Code deshalb interessant, weil sich Logik mit diesem Datetypen gut auf die CPU auslagern lässt. Hier ein Auszug aus dem Handbuch zu SIMPLE_INTEGER.
If you know that a variable will never have the value NULL or need overflow checking, declare it as SIMPLE_INTEGER rather than PLS_INTEGER. Without the overhead of checking for nullness and overflow, SIMPLE_INTEGER performs significantly better than PLS_INTEGER.
Probieren wir es aus und ersetzen den NUMBER-Datentypen in der Funktion durch SIMPLE_INTEGER.
create or replace function fib (n simple_integer) return simple_integer is
begin
  if (n = 1) or (n = 2) then
     return 1;
  else
     return fib(n - 1) + fib(n - 2);
  end if;
end fib;
/
sho err
Zur Erinnerung: Mit dem NUMBER-Datentypen lief diese Prozedur 1,88 Sekunden interpretiert, und 0,98 Sekunden als Native-Code - schauen wir uns das mit dem SIMPLE_INTEGER an ...
SQL> alter session set plsql_code_type=interpreted;

SQL> alter function fib compile;

SQL> select fib(32) from dual;

   FIB(32)
----------
   2178309

1 Zeile wurde ausgewählt.

Abgelaufen: 00:00:01.43
... also bereits im interpretierten Modus etwas schneller. Mit Native Code sieht das ganze so aus:
SQL> alter session set plsql_code_type=native;

SQL> alter function fib compile;

SQL> select fib(32) from dual;

   FIB(32)
----------
   2178309

1 Zeile wurde ausgewählt.

Abgelaufen: 00:00:00.53
... das ist fast doppelt so schnell wie mit dem NUMBER-Datentypen - die Wahl des richtigen Datentypen kann hier also nochmal einen richtigen Unterschied machen. Die folgende Tabelle zeigt die Performance-Unterschiede für die verschiedenen möglichen Datentypen.
Nochmal zusammengefasst:
  • PLS_INTEGER (BINARY_INTEGER): Liefert einen Integer-Datentypen für PL/SQL, unterstützt aber auch SQL NULL-Handling. Für die Arbeit mit ganzzahligen Werten in PL/SQL ist PLS_INTEGER typischerweise schneller als NUMBER.
  • SIMPLE_INTEGER: Ist ein Subtyp von PLS_INTEGER, kann aber keine NULL-Werte annehmen und hat auch ein anderes Overflow-Verhalten. Passt sehr gut zu nativ kompiliertem Code.
  • BINARY_FLOAT und BINARY_DOUBLE: Diese funktionieren analog zu den float und double-Datentypen in C oder Java. Auch diese Datentypen passen sehr gut zu nativ kompiliertem Code, da moderne CPUs große Teile der Fließkommaarithmetik übernehmen können. Allerdings, im Gegensatz zu NUMBER, können durch die Fließkommaarithmetik Rundungsfehler auftreten (erzeugt euch ein Variable vom Typ BINARY_FLOAT und addiert zehn Mal 0,1). NUMBER ist und bleibt in der Oracle-Datenbank der numerische Datentyp mit der besten Präzision.
Zum Abschluß vielleicht noch ein paar kurze Worte zur konkreten Speicherung des Native Code - eingangs sagte ich ja schon, dass der Code als Teil der Datenbank gespeichert wird. Wer nun ganz neugierig ist, kann sich mal mal die Tabelle SYS.NCOMP_DLL$ ansehen; darin befinden sich die BLOBs mit dem Native Code in der Spalte DLL. Wirklich anfangen kann man damit aber nix ...
Zusammengefasst kann man sagen, dass Native Compilation ein nützliches Werkzeug, aber keine Allzweckwaffe ist. Sie eignet sich gut für rechenintensive Logik, und weniger für Prozeduren, die vor allem mit Datenbank-Cursors arbeiten. Denkt aber (nicht nur bei Native Compilation) über die verwendeten Datentypen nach - wenn Ihr in einer PL/SQL-Prozedur nur mit ganzen Zahlen arbeitet, sind BINARY_INTEGER, PLS_INTEGER oder SIMPLE_INTEGER einfach sinnvoller als der NUMBER. Viel Spaß beim Ausprobieren ...
This blog posting will be about PL/SQL Native Compilation, which is available in the Oracle database for quite a while - the first implementation was in Oracle9i. By default, PL/SQL code is being compiled to "bytecode" which is then being executed by the "PL/SQL engine". This execution is still an interpreted execution, but interpreting compact bytecode is (of course) much more efficient than interpreting source code. The source code itself is also being stored in the database, as we all can see by selecting the data dictionary view USER_SOURCE.
In the early days, using native compilation for PL/SQL was cumbersome: The concept was to generate C code from the PL/SQL source and then to use an external compiler and linker to generate native code for the platform the database runs on. But this approach - in practice - led to problems.
  • C compilers and linkers are freely available on Linux, but not necessarily on other platforms. To use native compilation, some installations would have to purchase compiler licenses
  • Setup for compiler and linker had to be done by using Makefiles. The database then called the make utility in order to generate the native code. Editing Makefiles can be hard
  • The resulting native code exists in an external file (.so on Unix/Linux platforms, .dll on Windows). This has consequences for database backup and recovery in particular and the operating processes in general
So, beginning with release 11.1, native compilation was simplified dramatically. The most important difference between today and the past is that the need for an external compiler and linker had been eliminated: the database generates the platform-specific native code itself. Therefore all compiler-related parts like Makefiles and directories had also been dropped. Today, we simply have one session parameter: PLSQL_CODE_TYPE. I'll now use a fibonacci number example (the usual suspect) to show the simplicity and the effects of enabling native compilation.
create or replace function fib (n number) return number is
begin
  if (n = 1) or (n = 2) then
     return 1;
  else
     return fib(n - 1) + fib(n - 2);
  end if;
end fib;
/
sho err

Function created.

select fib(32) from dual
/

   FIB(32)
----------
   2178309

1 row selected.

Elapsed: 00:00:01.88
By default, PL/SQL code will execute in interpreted mode. So the elapsed time we have seen, should reflect the performance of interpreted PL/SQL. We can also check this using the data dictionary view USER_PLSQL_OBJECT_SETTINGS.
SQL> select name, type, PLSQL_CODE_TYPE from user_plsql_object_settings where name='FIB'

NAME                           TYPE         PLSQL_CODE_TYPE
------------------------------ ------------ --------------------
FIB                            FUNCTION     INTERPRETED

1 row selected.
Now we'll change this to native execution. Change the parameter and recompile the PL/SQL code.
SQL> alter session set PLSQL_CODE_TYPE=NATIVE;

Session altered.

SQL> alter function FIB compile;

Session altered.

SQL> select name, type, plsql_code_type from user_plsql_object_settings where name='FIB';

NAME                           TYPE         PLSQL_CODE_TYPE
------------------------------ ------------ --------------------
FIB                            FUNCTION     NATIVE

1 row selected.
We now should see a change in elapsed time - remember: in interpreted mode, elapsed time was 1.88 seconds.
select fib(32) from dual;

   FIB(32)
----------
   2178309

1 row selected.
 
Elapsed: 00:00:00.98
This is twice as fast - which is good (but we'll make it even faster). But before moving on, I'd like to say that such an improvement will not happen all the time. If you have PL/SQL code which does computations and nothing else, you'll see the above or similar improvement. If, on the other hand, your PL/SQL is about I/O - and I/O can mean working with database objects (SQL), with the network (UTL_TCP, UTL_HTTP, UTL_SMPT) or with the file system (UTL_FILE), then you might see only a small or no benefit at all. I also have an example for this - the following procedure loops over the rows of the ALL_OBJECTS dictionary view and calculates the sum off all "object name lengths".
create or replace function get_len_allobjects return number is
  l_len number := 0;
begin
  for i in (select object_name from all_objects) loop
    l_len := l_len + length(i.object_name);
  end loop;
  return l_len;
end;
/ 
sho err
The major part of that procedure is the cursor loop for ALL_OBJECTS. The computation part (which could benefit from native compilation) is close to nothing. So - try it out. First execution is interpreted.
SQL> alter session set plsql_code_type=interpreted;

SQL> alter function get_len_allobjects compile;

SQL> select get_len_allobjects from dual;

GET_LEN_ALLOBJECTS
------------------
           1739009

1 row selected.

Elapsed: 00:00:11.95
The second run will be native.
SQL> alter session set plsql_code_type=native;

SQL> alter function get_len_allobjects compile;

SQL> select get_len_allobjects from dual;

GET_LEN_ALLOBJECTS
------------------
           1739009

1 row selected.

Elapsed: 00:00:11.42
You don't see a big change - the code part which profited from native compilation was so small that it couln't make a difference. These kind of PL/SQL functions cannot benefit from native compilation.
But let's now get back to the Fibonacci example - we wanted to make this even faster - and to do this, we'll have to look into the data types which are being used by the function. In the example it's the NUMBER data type, which offers the greatest number range and precision, but the question is whether we really need this. And since the function only deals with integer numbers, we have alternatives: PLS_INTEGER (BINARY_INTEGER is a synonym) and SIMPLE_INTEGER. Both types are available in PL/SQL only, so we cannot use them in tables. But when we execute a function with PLS_INTEGER arguments, Oracle automatically converts between NUMBER and the PL/SQL data type.
SIMPLE_INTEGER is interesting. It's available since Oracle 11.2. Compared to PLS_INTEGER, it has different overflow semantics and it does not know about SQL NULL. So it's pretty comparable to the int data type in the C/C++ or Java programming languages. For native compilation it's particularly interesting since it allows to push down more logic to the CPU. Here's a statement on SIMPLE_INTEGER from the documentation.
If you know that a variable will never have the value NULL or need overflow checking, declare it as SIMPLE_INTEGER rather than PLS_INTEGER. Without the overhead of checking for nullness and overflow, SIMPLE_INTEGER performs significantly better than PLS_INTEGER.
So - we'll try this and change the NUMBER data types in the function signature to SIMPLE_INTEGER.
create or replace function fib (n simple_integer) return simple_integer is
begin
  if (n = 1) or (n = 2) then
     return 1;
  else
     return fib(n - 1) + fib(n - 2);
  end if;
end fib;
/
sho err
Remember again. Using the NUMBER data type, the function ran 1.88 seconds in interpreted mode and 0.98 seconds in native mode. Here are the numbers for the SIMPLE_INTEGER type.
SQL> alter session set plsql_code_type=interpreted;

SQL> alter function fib compile;

SQL> select fib(32) from dual;

   FIB(32)
----------
   2178309

1 row selected.

Elapsed: 00:00:01.43
It runs faster, even in interpreted mode. But let's also have a look to native mode.
SQL> alter session set plsql_code_type=native;

SQL> alter function fib compile;

SQL> select fib(32) from dual;

   FIB(32)
----------
   2178309

1 row selected.

Elapsed: 00:00:00.53
This is close to twice as fast - compared to native mode with the NUMBER type. So choosing a different data type can make a significant difference. It tried it also with the other numeric data types - the following table shows the results.
A short summary on numeric data types in PL/SQL.
  • PLS_INTEGER (BINARY_INTEGER) is a data type for integer numbers in PL/SQL only. It does support SQL NULL and is typically faster than NUMBER
  • SIMPLE_INTEGER is a subtype of PLS_INTEGER, but it has a different overflow semantics and it does not support SQL NULL. It's comparable to an integer data type outside a database and suits very well to native compiled code
  • BINARY_FLOAT and BINARY_DOUBLE are data types for floating point numbers. These work similar to the float and double types in programming languages outside the database. Since floating point logic can be pushed down to the CPU, these suit also very well to native code. But (and this is important) opposed to NUMBER, we can see rounding differences - simply declare a BINARY_FLOAT variable and add 0.1 ten times. You'll then see the difference between NUMBER and BINARY_FLOAT. NUMBER is always to most precise numeric data type in Oracle
Finally, a few words on where native code is actually being stored in the database. If you are curious, have a look into the SYS.NCOMP_DLL$ table - it has the BLOB column DLL which contains the actual native code - but ... can you really do something with this ...?
In summary, PL/SQL Native Compilation can be a very handy tool - but it's not the silver bullet performance tuning method. If you have PL/SQL objects doing intensive computations, compiling these to native code can improve performance. Taking care on the used data types can lead to even better results. But if your code is about Cursors, SQL and database tables, native compilation might have close to no effect at all ... have fun while playing around.

9. Juli 2015

Oracle12c Jetzt. Webinare im Sommer und Herbst 2015

This posting is about a webinar series in german language and therefore in german only.
Ab August führen meine Kollegen (unter anderen Ulrike Schwinn und Sebastian Solbach) und ich eine Webinar-Reihe zum Datenbankrelease Oracle12c durch. Inhalte sind das konkrete Upgrade einer älteren Datenbank auf 12c, aber auch Testen, Hochverfügbarkeit, mögliche Datenbankarchitekturen und mehr. Dass Entwicklerthemen wie SQL, PL/SQL, XML, JSON und andere nicht zu kurz kommen, ist dabei meine Aufgabe ☺. Die Reihe nennt sich 12c jetzt - die Teilnahme ist kostenlos. Meldet euch am besten an - wir können euch dann benachrichtigen, kurz bevor ein Webinar startet oder wenn es Änderungen am Programm gibt.


Zusätzlich wird es noch eine LIVE Veranstaltung im Oktober in Köln geben - die Details werden gerade ausgearbeitet - lasst euch überraschen und meldet euch am besten gleich zu 12cJetzt an.

24. Juni 2015

REST-Enabling einer Tabelle in fünf Minuten - mit ORDS 3.0

In diesem Blog Posting möchte ich mich den Oracle Rest Data Services (ORDS) widmen. ORDS hat seine Ursprünge im APEX Listener, dem javabasierten Webserver für Application Express. Dieser wurde Stück für Stück um Funktionen zum Bereitstellen von REST-Webservices erweitert - und in diesem Zusammenhang wurde der Name von "APEX Listener" auf "Oracle Rest Data Services geändert". ORDS bietet folgende Funktionen an.
  • Webserver für Application Express
  • REST-Endpoint für relationale Tabellen und Views
  • REST-Endpoint für PL/SQL Funktionen, Prozeduren und anonyme Blöcke
  • REST-Endpoint für die JSON-Funktionen in der Oracle Datenbank 12c (SODA)
  • REST-Endpoint für die Oracle NoSQL DB
Man sieht, dass der Namenswechsel hin zum Thema "REST" absolut gerechtfertigt ist; "APEX Listener" würde den Möglichkeiten nicht wirklich gerecht werden. Im ersten Blog-Posting zu ORDS möchte ich den zweiten Punkt herausgreifen: ORDS bietet wirklich sehr schöne Möglichkeiten an, mit sehr wenig Aufwand REST-Endpoints für Tabellen und Views in einem Datenbankschema bereitzustellen.
Ladet euch ORDS zunächst herunter und packt das ZIP-Archiv aus. Haltet euch Verbindungdaten zur Datenbank, mit der Ihr arbeiten möchtet bereit - Ihr müsst euch als SYS anmelden. Für das heutige Blog Posting muss es nicht zwingend eine 12c-Datenbank sein, eine 11g tut es auch. Nach dem Herunterladen und Auspacken solltet Ihr einen Ordner mit folgenden Dateien haben.
O:\>dir
 Volume in drive O is Data
 Volume Serial Number is 5054-5D26

 Directory of O:\

24.06.2015  15:34    <DIR>          .
24.06.2015  15:34    <DIR>          ..
24.06.2015  15:15    <DIR>          docs
24.06.2015  15:15    <DIR>          examples
12.05.2015  17:04        46.105.880 ords.war
01.05.2015  10:28            23.806 readme.html
               2 File(s)     46.129.686 bytes
               4 Dir(s)  195.599.679.488 bytes free
Nun geht es daran, ORDS erstmalig zu starten - ORDS wird dabei ein Metadaten-Schema in die Datenbank installieren, denn die Definitionen der REST-Services werden in der Datenbank gespeichert. Startet die Installation also mit java -jar ords.war install. Der Installer fragt euch nun nach den Angaben zur Datenbank, also Hostnamen, Listener Port, Service Name und schließlich auch das DBA-Password - das wird zur Installation des Metadaten-Schemas gebraucht.
O:\>java -jar ords.war install
Enter the name of the database server [localhost]:sccloud034
Enter the database listen port [1521]:1521
Enter 1 to specify the database service name, or 2 to specify the database SID [1]:1
Enter the database service name:pdb01.de.oracle.com
Enter 1 if you want to verify/install Oracle REST Data Services schema or 2 to skip this step [1]:1
Enter the database password for ORDS_PUBLIC_USER:******
Confirm password:******

Please login with SYSDBA privileges to verify Oracle REST Data Services schema. Installation may be required.


Enter the username with SYSDBA privileges to verify the installation [SYS]:SYS
Enter the database password for SYS:******
Confirm password:******
Jun 24, 2015 3:42:57 PM oracle.dbtools.rt.config.setup.SchemaSetup addSchemaParams
INFO:
Oracle REST Data Services schema does not exist and will be created.


Enter the default tablespace for ORDS_METADATA [SYSAUX]:SYSAUX
Enter the temporary tablespace for ORDS_METADATA [TEMP]:TEMP
Enter the default tablespace for ORDS_PUBLIC_USER [USERS]:USERS
Enter the temporary tablespace for ORDS_PUBLIC_USER [TEMP]:TEMP
:
Danach geht es mit einer Frage zum APEX Listener weiter - diese ist nur interessant, wenn der ORDS gleichzeitig als APEX-Webserver dienen soll. Das steht heute nicht im Mittelpunkt, daher könnt Ihr den Punkt überspringen.
Enter 1 if you want to use PL/SQL Gateway or 2 to skip this step [1]:2
Enter 1 to specify passwords for Application Express RESTful Services database users (APEX_LISTENER, APEX_REST_PUBLIC_USER) or 2 to skip this step [1]:2
Dann seht Ihr einige Statusmeldungen ...
Jun 24, 2015 3:43:04 PM oracle.dbtools.common.config.file.ConfigurationFilesBase update
INFO: Updated configurations: defaults, apex_pu
Jun 24, 2015 3:43:04 PM oracle.dbtools.installer.Installer installORDS
INFO:
Installing Oracle REST Data Services version 3.0.0.121.10.23
... Log file written to C:\Users\cczarski\ordsinstall_2015-06-24_154305_00043.log
... Verified database prerequisites
... Created Oracle REST Data Services schema
... Granted privileges to Oracle REST Data Services
... Created Oracle REST Data Services database objects
... Created Oracle REST Data Services proxy user
Jun 24, 2015 3:43:50 PM oracle.dbtools.installer.Installer installORDS
INFO: Completed installation for Oracle REST Data Services version 3.0.0.121.10.23. Elapsed time: 00:00:45.343
Zum Abschluß kommt noch die Frage, ob ORDS im Standalone Modus starten soll oder ob Ihr diesen in einen Java-Server wie Weblogic oder Tomcat deployen wollt. Für heute reicht uns der Standalone-Modus aus; den HTTP-Port, nach dem er uns dann fragen wird, legen wir mit 8081 fest.
Enter 1 if you wish to start in standalone mode or 2 to exit [1]:1
Enter the HTTP port [8080]:8081
Soweit ist die Installation fertig; die letzte Statusmeldung sagt euch, dass ORDS jetzt läuft.
:
Jun 24, 2015 3:46:35 PM oracle.dbtools.common.config.db.DatabasePools validatePool
INFO: Pool: apex_pu is correctly configured
2015-06-24 15:46:35.505:INFO:/ords:main: INFO: Oracle REST Data Services initialized|Oracle REST Data Services version :
 3.0.0.121.10.23|Oracle REST Data Services server info: jetty/9.2.z-SNAPSHOT|
2015-06-24 15:46:35.508:INFO:oejsh.ContextHandler:main: Started o.e.j.s.ServletContextHandler@4bb4de6a{/ords,null,AVAILA
BLE}
2015-06-24 15:46:35.588:INFO:oejs.ServerConnector:main: Started ServerConnector@17c386de{HTTP/1.1}{0.0.0.0:8081}
2015-06-24 15:46:35.589:INFO:oejs.Server:main: Started @307536ms
Doch was macht man damit? Wenn man nun die URL {hostname}:8081/ords aufruft, passiert noch gar nichts: Es gibt eine HTTP-404-Fehlermeldung (Not Found) und weiter sieht man nichts.
Grund ist, dass noch keine REST-Services definiert wurden. ORDS bringt dafür keine Web-Oberfläche mit, vielmehr kann man die REST-Services mit dem Oracle SQL Developer oder auf dem SQL-Prompt mit PL/SQL Calls einrichten. Für dieses Blog-Posting nehmen wir letzteren Ansatz. Verbindet euch also (mit dem SQL-Werkzeug eurer Wahl) auf das Schema SCOTT in der Datenbank, die Ihr beim Installieren von ORDS angegeben habt. Schaut euch darin das PL/SQL Paket ORDS an.
SQL> sho user
USER ist "SCOTT"

SQL> desc ords
PROCEDURE CREATE_PRIVILEGE
 Argument Name                  Typ                     In/Out Defaultwert?
 ------------------------------ ----------------------- ------ --------
 P_NAME                         VARCHAR2                IN
 P_ROLES                        TABLE OF VARCHAR2(32000) IN
 P_LABEL                        VARCHAR2                IN     DEFAULT
 P_DESCRIPTION                  VARCHAR2                IN     DEFAULT
PROCEDURE CREATE_PRIVILEGE
 Argument Name                  Typ                     In/Out Defaultwert?
 ------------------------------ ----------------------- ------ --------
 P_NAME                         VARCHAR2                IN
 P_ROLE_NAME                    VARCHAR2                IN
 P_LABEL                        VARCHAR2                IN     DEFAULT
 P_DESCRIPTION                  VARCHAR2                IN     DEFAULT
PROCEDURE CREATE_PRIVILEGE_MAPPING
 Argument Name                  Typ                     In/Out Defaultwert?
 ------------------------------ ----------------------- ------ --------
 P_PRIVILEGE_NAME               VARCHAR2                IN
 P_PATTERNS                     TABLE OF VARCHAR2(32000) IN
:
Besonders interessant sind die Prozeduren ENABLE_SCHEMA und ENABLE_OBJECT. Diese machen das Auto-Rest Feature aus, mit dem sich in wenigen Handgriffen ein REST-Endpoint für eine Tabelle erstellen lässt. Und das machen wir nun. Zuerst müssen wir REST grundsätzlich für das Schema SCOTT freischalten.
begin
  ords.enable_schema (
    P_ENABLED        => true,
    P_SCHEMA         => 'SCOTT',
    P_AUTO_REST_AUTH => false
  );
end;
/
sho err
Die Benutzung von ENABLE_SCHEMA ist wirklich einfach. Der erste Parameter (P_ENABLED) legt fest, ob das Schema freigegeben oder gesperrt sein soll; danach kommt das Schema selbst (P_SCHEMA). Der letzte Parameter P_AUTO_REST_AUTH legt fest, ob sich ein REST-Client authentizieren muss, wenn er die REST-Endpoints verwenden möchte. Der Default ist true, was das Sicherheitslevel etwas erhöht. Für unsere Tests setzen wir es jedoch auf false; Authentifizierung bleibt in diesem Blog-Posting zunächst außen vor. Setzt noch ein COMMIT ab; wir sind ja in der Datenbank. Allerdings steht nun immer noch kein REST-Endpoint bereit, denn wir haben noch keine Tabellen oder Views freigegeben. Das kommt jetzt - mit einem Aufruf von ENABLE_OBJECT.
begin
  ords.enable_object (
    P_ENABLED        => true,
    P_SCHEMA         => 'SCOTT',
    P_OBJECT         => 'EMP',
    P_OBJECT_TYPE    => 'TABLE',
    P_OBJECT_ALIAS   => 'the-emp-table',
    P_AUTO_REST_AUTH => false
  );
end;
/
sho err
Die Parameter P_ENABLED, P_SCHEMA und P_AUTO_REST_AUTH haben die gleiche Bedeutung wie bei ENABLE_SCHEMA. Zusätzlich muss man hier natürlich noch den Namen der Tabelle oder View (P_OBJECT) und (optional) einen URL-Alias (P_OBJECT_ALIAS) angeben. Nach dem obligatorischen COMMIT steht ein REST-Endpoint für die Tabelle EMP unter der URL /ords/scott/the-emp-table bereit. Probiert es aus, und ruft die URL mit dem Browser auf - ihr solltet die Inhalte der Tabelle EMP im JSON-Format sehen.
Stellt nun sicher, dass die Spalte EMPNO der Tabelle EMP als Primärschlüssel definiert ist; oft ist das nicht der Fall.
SQL> alter table EMP add constraint PK_EMP primary key (EMPNO);
Jetzt könnt Ihr per URL auch einzelne Zeilen ansteuern; während die URL /ords/scott/the-emp-table alle Zeilen der Tabelle zurückliefert, liefert die URL /ords/scott/the-emp-table/7839 nur die eine Zeile mit der EMPNO 7839 zurück. Darüber hinaus unterstützt ORDS auch eine "JSON-Query"-Syntax. Probiert mal folgende URLs aus:
  • /ords/scott/the-emp-table/?q={"sal":{"$lt":1000}}
  • /ords/scott/the-emp-table/?q={"ename":{"$like":"S%25"}}
  • /ords/scott/the-emp-table/?q={"ename":{"$eq":"KING"}}
Die komplette Beschreibung der Query-Syntax findet Ihr in der Dokumentation. Zum Lesen der Tabelle gibt es also schon eine sehr elegante REST-Schnittstelle - aber ORDS kann noch mehr.
Setzt man anstelle des HTTP-GET-Requests einen PUT, POST oder DELETE-Request ab, so können die Inhalte der Tabelle auch verändert werden. Hierzu reicht der einfache Browser aber nicht aus; Ihr braucht einen REST-Client. Diese sind auch als Browser-Plugins erhältlich - so gibt es für Chrome die App Advanced REST Client; für Firefox ist das Addon REST-Client verfügbar und als Standalone-Anwendung kommen Kandidaten wie Postman in Frage. Das folgende Bild zeigt die Chrome-App Advanced REST Client.
Nun wollen wir per REST-Request eine Zeile in die Tabelle einfügen. Macht im Chrome Advanced Rest Client folgende Angaben (andere REST-Clients sehen ähnlich aus).
  • Legt PUT als Request-Type fest.
  • Als URL legt Ihr /ords/scott/the-emp-table/8999 fest; hier geben wir den Wert für die EMPNO schon in der URL an, weil die Tabelle EMP keine Sequence und keinen Trigger hat, um die ID automatisch zu generieren. Wenn Ihr eine Tabelle hat, deren Primary Key-Spalte automatisch generiert wird, könnt Ihr zum Einfügen einer Zeile einfach einen POST-Request an die URL der Tabelle (hier: /ords/scott/the-emp-table/) absetzen.
  • Als Payload oder Request Body tragt Ihr die Daten der neuen Zeile im JSON-Format ein, also wie folgt:
    {"empno": 8999, "ename": "CZARSKI", "job": "BLOGGER", "sal": 0, "comm": 0, "mgr": 7839, "deptno": 30}
    
  • Achtet schließlich darauf, dass der HTTP-Header Content-Type auf application/json gesetzt ist; bei der Chrome-App ist das der Default; anderswo muss man es explizit einstellen.
Wenn Ihr den Request dann absendet, bekommt Ihr eine Antwort, welche nochmals die neue Zeile im JSON-Format enthält. Eine Prüfung der Tabelle EMP im SQL-Tool zeigt euch, dass tatsächlich eine Zeile eingefügt wurde.
SQL> select * from emp;

EMPNO ENAME      JOB         MGR HIREDATE              SAL  COMM DEPTNO
----- ---------- --------- ----- ------------------- ----- ----- ------
 7369 SMITH      CLERK      7902 17.12.1980 00:00:00   800           20
 7499 ALLEN      SALESMAN   7698 20.02.1981 00:00:00  1600   300     30
 
 : 

 7934 MILLER     CLERK      7782 23.01.1982 00:00:00  1300           10
 8999 CZARSKI    BLOGGER    7839                         0     0     30
Ein erneutes HTTP-PUT auf die gleiche URL (mit der 8999) führt zu einem Update der Zeile. Um sie zu löschen, braucht es einen DELETE-Request.
  • Legt DELETE als Request-Type fest.
  • Als URL legt Ihr /ords/scott/the-emp-table/8999 fest.
  • Als Payload oder Request Body tragt Ihr nichts ein
  • Stellt sicher, dass der HTTP-Header Content-Type nun auf text/plain gesetzt ist; wenn er noch auf application/json steht, müsst Ihr ihn umstellen.
Ihr solltet die Antwort bekommen, dass eine Zeile gelöscht wurde; eine Kontrolle der EMP-Tabelle sollte dann ergeben, dass die neue Zeile tatsächlich weg ist. Für DELETE-Requests könnt Ihr auch die JSON-Query-Syntax verwenden, die weiter oben bei den GET-Requests beschrieben wurde. Diese Syntax erlaubt euch auch, mehrere Zeilen auf einmal zu löschen.
Für heute soll das mal genügen; allerdings haben wir nur ein wenig an der Oberfläche der neuen Möglichkeiten gekratzt; ORDS erlaubt auch das Erstellen von REST-Services mit eigenen SQL-Queries oder PL/SQL-Objekten - dazu jedoch mehr in späteren Blog-Postings - bis dahin viel Spaß beim Ausprobieren ...
This blog posting will be about Oracle Rest Data Services (ORDS). ORDS is known in the APEX developer community as the APEX Listener, since it was originally introduced as a Java-based Webserver for Oracle Application Express. In the meantime, the development team added more features to provide REST services on top of the Oracle database or Oracle NoSQL DB and to reflect this, its name was changed to "ORDS". ORDS can still act as a webserver for APEX; but beyond this it ...
  • ... can act as the Webserver for Oracle Application Express
  • ... provides REST endpoints for relational tables and views
  • ... provides REST endpoints for PL/SQL functions, procedures or anonymous blocks
  • ... acts as a REST endpoint for the Oracle12c JSON functionalit (SODA, JSON document store)
  • ... provides REST endpoints for Oracle NoSQL Database
So, the name change to ORDS was more than appropriate. In this first blog posting about ORDS I'd like to describe the installation and then I'll pick the second feature in the list above. You'll see that it's very easy to create a REST endpoint on an existing table - this REST endpoint will allow to retrieve and change table data.
First, download ORDS from OTN and unpack the ZIP archive. Also make sure, that you have DBA credentials for your database at hand, since ORDS will need to connect as SYSDBA during installation. You don't necessarily need an Oracle12c database, for this blog posting, 11g will also do. After unpacking the downloaded archive, your directory should look like this.
O:\>dir
 Volume in drive O is Data
 Volume Serial Number is 5054-5D26

 Directory of O:\

24.06.2015  15:34    <DIR>          .
24.06.2015  15:34    <DIR>          ..
24.06.2015  15:15    <DIR>          docs
24.06.2015  15:15    <DIR>          examples
12.05.2015  17:04        46.105.880 ords.war
01.05.2015  10:28            23.806 readme.html
               2 File(s)     46.129.686 bytes
               4 Dir(s)  195.599.679.488 bytes free
When ORDS is being started the first time, it will prompt you for configuration parameters. The most important ones are the hostname, TCP/IP port and service name for your database. Then ORDS will connect to the database as SYS (you'll need the SYS password, then) and install a metadata schema - that schema will contain the REST service definitions. Start the installation by executing java -jar ords.war install.
O:\>java -jar ords.war install
Enter the name of the database server [localhost]:sccloud034
Enter the database listen port [1521]:1521
Enter 1 to specify the database service name, or 2 to specify the database SID [1]:1
Enter the database service name:pdb01.de.oracle.com
Enter 1 if you want to verify/install Oracle REST Data Services schema or 2 to skip this step [1]:1
Enter the database password for ORDS_PUBLIC_USER:******
Confirm password:******

Please login with SYSDBA privileges to verify Oracle REST Data Services schema. Installation may be required.


Enter the username with SYSDBA privileges to verify the installation [SYS]:SYS
Enter the database password for SYS:******
Confirm password:******
Jun 24, 2015 3:42:57 PM oracle.dbtools.rt.config.setup.SchemaSetup addSchemaParams
INFO:
Oracle REST Data Services schema does not exist and will be created.


Enter the default tablespace for ORDS_METADATA [SYSAUX]:SYSAUX
Enter the temporary tablespace for ORDS_METADATA [TEMP]:TEMP
Enter the default tablespace for ORDS_PUBLIC_USER [USERS]:USERS
Enter the temporary tablespace for ORDS_PUBLIC_USER [TEMP]:TEMP
:
The next installer questions will be about Application Express; you can have ORDS providing REST services and acting as the APEX webserver at the same time. For this blog posting, we'll skip all APEX related steps.
Enter 1 if you want to use PL/SQL Gateway or 2 to skip this step [1]:2
Enter 1 to specify passwords for Application Express RESTful Services database users (APEX_LISTENER, APEX_REST_PUBLIC_USER) or 2 to skip this step [1]:2
Then you'll see some more status messages ...
Jun 24, 2015 3:43:04 PM oracle.dbtools.common.config.file.ConfigurationFilesBase update
INFO: Updated configurations: defaults, apex_pu
Jun 24, 2015 3:43:04 PM oracle.dbtools.installer.Installer installORDS
INFO:
Installing Oracle REST Data Services version 3.0.0.121.10.23
... Log file written to C:\Users\cczarski\ordsinstall_2015-06-24_154305_00043.log
... Verified database prerequisites
... Created Oracle REST Data Services schema
... Granted privileges to Oracle REST Data Services
... Created Oracle REST Data Services database objects
... Created Oracle REST Data Services proxy user
Jun 24, 2015 3:43:50 PM oracle.dbtools.installer.Installer installORDS
INFO: Completed installation for Oracle REST Data Services version 3.0.0.121.10.23. Elapsed time: 00:00:45.343
After this, you'll see the question whether to start ORDS in standalone mode or to exit. ORDS can be installed into a Java container like Oracle Weblogic, Glassfish or Tomcat (which is recommended for production systems), but can also run in standalone mode. Standalone is totally sufficient for testing and development. In standalone mode, the installer will finally ask you for an HTTP-Port number. In this example, we choose 8081 - just make sure that the chosen port is available on your system.
Enter 1 if you wish to start in standalone mode or 2 to exit [1]:1
Enter the HTTP port [8080]:8081
This will finish the installation process; the last status message should tell you, that ORDS is now up and running.
:
Jun 24, 2015 3:46:35 PM oracle.dbtools.common.config.db.DatabasePools validatePool
INFO: Pool: apex_pu is correctly configured
2015-06-24 15:46:35.505:INFO:/ords:main: INFO: Oracle REST Data Services initialized|Oracle REST Data Services version :
 3.0.0.121.10.23|Oracle REST Data Services server info: jetty/9.2.z-SNAPSHOT|
2015-06-24 15:46:35.508:INFO:oejsh.ContextHandler:main: Started o.e.j.s.ServletContextHandler@4bb4de6a{/ords,null,AVAILA
BLE}
2015-06-24 15:46:35.588:INFO:oejs.ServerConnector:main: Started ServerConnector@17c386de{HTTP/1.1}{0.0.0.0:8081}
2015-06-24 15:46:35.589:INFO:oejs.Server:main: Started @307536ms
But what to do with this running ORDS instance ...? Calling the URL {hostname}:8081/ords simply leads to an HTTP-404 (Not Found) message. The reason is that we did not define any REST service so far. ORDS does not provide a web interface to define REST services. We can either use Oracle SQL Developer, or the PL/SQL package ORDS within the database. In this blog posting, I'll show how to use the latter option. So, with the SQL tool of your choice, connect (as user SCOTT) to the database, which you configured during ORDS installation. Upon connected, check out the PL/SQL package ORDS.
SQL> sho user
USER ist "SCOTT"

SQL> desc ords
PROCEDURE CREATE_PRIVILEGE
 Argument Name                  Typ                     In/Out Defaultwert?
 ------------------------------ ----------------------- ------ --------
 P_NAME                         VARCHAR2                IN
 P_ROLES                        TABLE OF VARCHAR2(32000) IN
 P_LABEL                        VARCHAR2                IN     DEFAULT
 P_DESCRIPTION                  VARCHAR2                IN     DEFAULT
PROCEDURE CREATE_PRIVILEGE
 Argument Name                  Typ                     In/Out Defaultwert?
 ------------------------------ ----------------------- ------ --------
 P_NAME                         VARCHAR2                IN
 P_ROLE_NAME                    VARCHAR2                IN
 P_LABEL                        VARCHAR2                IN     DEFAULT
 P_DESCRIPTION                  VARCHAR2                IN     DEFAULT
PROCEDURE CREATE_PRIVILEGE_MAPPING
 Argument Name                  Typ                     In/Out Defaultwert?
 ------------------------------ ----------------------- ------ --------
 P_PRIVILEGE_NAME               VARCHAR2                IN
 P_PATTERNS                     TABLE OF VARCHAR2(32000) IN
:
Within the ORDS PL/SQL package, the ENABLE_SCHEMA and ENABLE_OBJECT procedures allow you to create a REST endpoint on a table or view with only two PL/SQL calls: Auto-Rest. First, enable a database schema for "Auto-Rest" by calling ENABLE_SCHEMA.
begin
  ords.enable_schema (
    P_ENABLED        => true,
    P_SCHEMA         => 'SCOTT',
    P_AUTO_REST_AUTH => false
  );
end;
/
sho err
The first parameter of ENABLE_SCHEMA, P_ENABLED is a boolean: true enables a schema, false disables it. The second parameter P_SCHEMA denotes the schema itself. The last parameter P_AUTO_REST_AUTH is about authentication: If set to true (which is the default), a REST client needs to authenticate before performing requests on a table which has been enabled with Auto-Rest. We set this parameter to false since we don't want to use authentication at this time. After this call, execute a COMMIT - we are in a database.
But we still have no working REST endpoint. We now need to explicitly enable a table or view with ORDS.ENABLE_OBJECT.
begin
  ords.enable_object (
    P_ENABLED        => true,
    P_SCHEMA         => 'SCOTT',
    P_OBJECT         => 'EMP',
    P_OBJECT_TYPE    => 'TABLE',
    P_OBJECT_ALIAS   => 'the-emp-table',
    P_AUTO_REST_AUTH => false
  );
end;
/
sho err
The P_ENABLED, P_SCHEMA and P_AUTO_REST_AUTH parameters have the same meaning as for the ENABLE_SCHEMA procedure. For ENABLE_OBJECT, we need to additionally pass the name of the table or view (P_OBJECT) and, optionally a URL alias (P_OBJECT_ALIAS). After executing the final COMMIT, a REST endpoint for the EMP table is available under the URL /ords/scott/the-emp-table. Try it out and call this URL with a browser - you should see the EMP table data in JSON format.
Now make sure, that the EMPNO column is defined as primary key - often, this is not the case.
SQL> alter table EMP add constraint PK_EMP primary key (EMPNO);
The primary key allows to access individual rows by simply appending the PK value to the URL. While /ords/scott/the-emp-table returns all table rows, /ords/scott/the-emp-table/7839 only returns one row (with EMPNO 7839). Beyond this, ORDS also provides a JSON query syntax with more complex filtering capabilities. Here are three examples:
  • /ords/scott/the-emp-table/?q={"sal":{"$lt":1000}}
  • /ords/scott/the-emp-table/?q={"ename":{"$like":"S%25"}}
  • /ords/scott/the-emp-table/?q={"ename":{"$eq":"KING"}}
The ORDS Documentation contains a complete description of the JSON query syntax. So we have a nice REST interface to read data from our table. But ORDS can do more ...
When using HTTP PUT, POST or DELETE methods, instead of GET, you can also manipulate table data. But for this, the plain browser is not enough (browsers can only to GET and POST). So we need a "REST client" application, which allows us to use the full range of HTTP requests. REST clients are also available as browser Add-Ons; we have the Advanced REST client for Chrome, RESTClient for Firefox or Postman as a standalone application. Of course, there several more, also as addons for other browsers. The following screenshot shows Advanced REST client for Chrome.
Now lets's create a new table row with a REST request. In Advanced REST client for Chrome, use the following settings (other REST clients should look similar).
  • Choose PUT as the HTTP method.
  • Use the URL /ords/scott/the-emp-table/8999. In this case, we'll append the primary key value for the new row, explicitly, to the URL. If the table's primary key column had a trigger, and a sequence, which generated the value automatically, we would issue a POST request to the URL of the table (without the primary key value): /ords/scott/the-emp-table/.
  • Provide the data for the new row as Payload or Request Body in JSON format - as follows:
    {"empno": 8999, "ename": "CZARSKI", "job": "BLOGGER", "sal": 0, "comm": 0, "mgr": 7839, "deptno": 30}
    
  • Finally, make sure that the HTTP header named Content-Type is being set to application/json. Advanced Rest Client does this by default; in other clients you might have to adjust this.
After submitting the request, you'll see the server's response. If everything works well, you'll get an HTTP-200 status and the row data in JSON format as the response body. At the SQL level, you might check whether the new row is really present.
SQL> select * from emp;

EMPNO ENAME      JOB         MGR HIREDATE              SAL  COMM DEPTNO
----- ---------- --------- ----- ------------------- ----- ----- ------
 7369 SMITH      CLERK      7902 17.12.1980 00:00:00   800           20
 7499 ALLEN      SALESMAN   7698 20.02.1981 00:00:00  1600   300     30
 
 : 

 7934 MILLER     CLERK      7782 23.01.1982 00:00:00  1300           10
 8999 CZARSKI    BLOGGER    7839                         0     0     30
Another PUT request with the same URL leads to a SQL UPDATE operation on the same row. To delete the row via REST, execute an HTTP DELETE request.
  • Choose DELETE as the HTTP method.
  • Choose a URL pointing to a specific row, for example /ords/scott/the-emp-table/8999.
  • Leave the Payload or Request Body empty.
  • Make sure that the HTTP header Content-Type is now set to text/plain; when it's still set to application/json, change it.
You should get a response indicating that your row has been deleted. For DELETE requests, you can also use the "JSON query syntax", described earlier for GET requests. This syntax allows you to delete multiple rows in one request.
This concludes my blog posting on the first steps with Oracle Rest Data Services (ORDS); expect more in upcoming postings. ORDS provides much more than simple "Auto-Rest" for tables and views - we can create REST services for PL/SQL procedures, functions or packages. More on this to come - stay tuned.

26. Mai 2015

APEX_JSON ohne APEX: JSON parsen mit SQL und PL/SQL

Dies ist das zweite von zwei Blog-Postings zum neuen Package APEX_JSON, mit dem man JSON-Dokumente auch ohne APEX, nur mit PL/SQL, verarbeiten kann. Nachdem das erste Blog-Posting sich mit dem Erzeugen von JSON beschäftigt hat, geht es heute um das Parsen und Auslesen von JSON mit APEX_JSON. Vorher noch wichtiger Hinweis.
Ab Oracle12c, genauer: Ab der Version 12.1.0.2 stellt die Datenbank native SQL-Funktionen zum Parsen von JSON bereit. Diese sind im C-Code des Datenbankkerns implementiert und dürften daher wesentlich effizienter sein als eine PL/SQL-Lösung.
Wenn Ihr könnt, solltet Ihr JSON immer mit den nativen SQL/JSON-Funktionen parsen; in 12c also grundsätzlich diese verwenden. APEX_JSON sollte zum Parsen von JSON nur in 11g-Datenbanken verwendet werden. Weiter unten werden wir noch einen Vergleich durchführen.
Los geht's - als Beispiel nehme ich JSON-Dateien, wie sie von der Twitter-API angeliefert werden; hier ein Ausschnitt.
{
  "id": 578903819884585000,
  "text": "RT @iAdvise_live: #countdown naar #Apexworld @OGh_nl ...
  "geo": {...}
  "lang": "nl",
  "retweet_count": 2,
  "created_at": "Fri Mar 20 13:00:00 +0000 2015",
  "user": {
      "statuses_count": 266,
      "lang": "en",
      "id": 1237892781789,
      "favourites_count": 62,        
      "name": "Jonathan ...",
      "screen_name": "jvanvianen78",
  }
  :
}
Wenn man nun eine Tabelle mit diesen JSON-Dateien hat, kann man diese mit APEX_JSON.PARSE parsen und die Inhalte dann mit GET_VARCHAR2, GET_NUMBER und ähnlichen Calls auslesen. Ein Beispiel könnte dann so aussehen:
Hinweis: Wenn Ihr mit der allerersten APEX-Version 5.0.1 und einer deutschsprachigen Session-Language arbeitet, setzt vorher ein ALTER SESSION SET NLS_NUMERIC_CHARACTERS='.,' ab.
declare
  l_parsed_json apex_json.t_values;
  l_value       varchar2(4000);
begin
  for i in (select tweet from apextweets) loop
    apex_json.parse(
      p_values => l_parsed_json,
      p_source => i.tweet
    );
    dbms_output.put_line(
      'User "'||
      apex_json.get_varchar2(
        p_path => 'user.screen_name',
        p_values => l_parsed_json
      ) ||
      '" tweeted at ' ||
      apex_json.get_varchar2(
        p_path => 'created_at',
        p_values => l_parsed_json
      ) 
    );
  end loop;
end;
/
Das Ergebnis wird in diesem Fall mit DBMS_OUTPUT auf die Konsole geschrieben; natürlich ist auch eine andere Verarbeitung denkbar.
User "jvanvianen78" tweeted at Fri Mar 20 13:00:00 +0000 2015
User "Yvke1983" tweeted at Fri Mar 20 12:58:08 +0000 2015
User "iAdvise_live" tweeted at Fri Mar 20 12:57:35 +0000 2015
User "reynde75" tweeted at Fri Mar 20 11:54:56 +0000 2015
User "Smart4Apex" tweeted at Fri Mar 20 11:42:38 +0000 2015
User "brost" tweeted at Fri Mar 20 11:06:20 +0000 2015
User "johnnyq72" tweeted at Fri Mar 20 10:45:55 +0000 2015
User "crokitta" tweeted at Fri Mar 20 10:44:31 +0000 2015
User "johnnyq72" tweeted at Fri Mar 20 10:27:25 +0000 2015
User "PretiusSoftware" tweeted at Fri Mar 20 09:29:02 +0000 2015
:
Allerdings haben wir hier die gleiche Situation wie im letzten Blog-Posting: Man muss jede Abfragesituation in PL/SQL prozedural kodieren; bei Abfragen wäre es ja interessanter, direkt mit SQL auf die JSON-Dokumente zugreifen zu können. APEX_JSON bietet uns hier einen Trick an: Die Funktion TO_XMLTYPE wandelt das JSON in einen XMLTYPE um ...
select apex_json.to_xmltype(tweet) from tweets_json where rownum = 1
/

APEX_JSON.TO_XMLTYPE(TWEET)
--------------------------------------------------------------------------------
<json>
  <retweeted_status>
    <text>#countdown naar #Apexworld @OGh_nl #iadvise_live @Yvke1983 ...
    <retweeted>false</retweeted>
    <truncated>false</truncated>
    <lang>nl</lang>
    <entities>
      <symbols/>
      <urls/>
      <hashtags>
        <row>
          <text>countdown</text>
          <indices>
            <row>0</row>
            <row>10</row>
:
... und darauf lässt sich nun sehr elegant mit den SQL/XML-Funktionen der Datenbank arbeiten.
select
  screen_name,
  created_at,
  lang 
from tweets_json, xmltable(
  '/json'
  passing apex_json.to_xmltype(tweet)
  columns
    screen_name varchar2(30)  path 'user/screen_name',
    created_at  varchar2(100) path 'created_at',
    lang        varchar2(5)   path 'lang'
)
where rownum <= 20
/  

SCREEN_NAME                    CREATED_AT                          LANG
------------------------------ ----------------------------------- ----
jvanvianen78                   Fri Mar 20 13:00:00 +0000 2015      nl
Yvke1983                       Fri Mar 20 12:58:08 +0000 2015      nl
iAdvise_live                   Fri Mar 20 12:57:35 +0000 2015      nl
reynde75                       Fri Mar 20 11:54:56 +0000 2015      und
Smart4Apex                     Fri Mar 20 11:42:38 +0000 2015      nl
brost                          Fri Mar 20 11:06:20 +0000 2015      en
johnnyq72                      Fri Mar 20 10:45:55 +0000 2015      nl
crokitta                       Fri Mar 20 10:44:31 +0000 2015      nl
johnnyq72                      Fri Mar 20 10:27:25 +0000 2015      en
PretiusSoftware                Fri Mar 20 09:29:02 +0000 2015      en
josepcoves                     Mon Mar 23 18:57:22 +0000 2015      en
Fr4ncis                        Mon Mar 23 18:48:29 +0000 2015      en
BIntsifuL                      Mon Mar 23 17:58:38 +0000 2015      en
pauljacobs123                  Mon Mar 23 17:45:28 +0000 2015      en
S3v3n11                        Mon Mar 23 17:20:52 +0000 2015      en
Das ist doch schon sehr bequem - und man kann zusätzliche Attribute einfach durch Anpassen der SQL-Abfrage dazunehmen. Auch in die Hierarchie kann man einsteigen, genau wie bei XML. Nun wollen wir eine etwas anspruchsvollere Aufgabe lösen: "Wieviele Tweets (ohne "Retweets") hat jeder User abgesetzt?"
select
  screen_name,
  count(*) anzahl_tweets
from tweets_json, xmltable(
  '/json'
  passing apex_json.to_xmltype(tweet)
  columns
    screen_name varchar2(30) path 'user/screen_name',
    created_at  varchar2(100) path 'created_at'
)
where not xmlexists(
  '/json/retweeted_status'
  passing apex_json.to_xmltype(tweet)
)
group by screen_name
order by 2 desc
/  

SCREEN_NAME                    ANZAHL_TWEETS
------------------------------ -------------
orclapexblogs                            186
joelkallman                               62
swesley_perth                             45
flederbine                                39
andre_blu                                 39
:

Elapsed: 00:01:17:55
Man kann mit den XML-Funktionen also eine ganze Menge erreichen. Würde man die gleiche Aufgabe mit PL/SQL Logik lösen, wäre dieser Code hier nötig (die absteigende Sortierung fehlt sogar noch).
declare
  type t_tabtype is table of number index by varchar2(200);

  l_parsed_json apex_json.t_values;
  l_user        varchar2(4000);
  l_tweetsbyusr t_tabtype;
  l_retweeted   boolean; 
begin
  for i in (select tweet from tweets_json) loop
    apex_json.parse(
      p_values => l_parsed_json,
      p_source => i.tweet
    );
    l_retweeted := apex_json.does_exist(
      p_path   => 'retweeted_status',
      p_values => l_parsed_json
    );
    if not l_retweeted then 
      l_user := apex_json.get_varchar2(
        p_path => 'user.screen_name',
        p_values => l_parsed_json
      );
      if l_tweetsbyusr.exists(l_user)  then
        l_tweetsbyusr(l_user) := l_tweetsbyusr(l_user) + 1;
      else 
        l_tweetsbyusr(l_user) := 1;
      end if;
    end if;
  end loop;
  l_user := l_tweetsbyusr.first;
  while l_user is not null loop
    dbms_output.put_line(l_user||':'||l_tweetsbyusr(l_user));
    l_user := l_tweetsbyusr.next(l_user);
  end loop;
end;
/

APEXORADEV:13
AliUK12:1
AljazMali:1
AntonScheffer:2
BIASCareers:2
BIntsifuL:7
BigBen212:1
BlueberryCoder:1
CPiasecki23:1
:

Elapsed: 00:01:06.36
Interessant ist nun aber die Ausführungszeit - für diese Auswertung auf 4.526 JSON-Dateien wurden mit dem XMLTYPE-Ansatz etwa 1 Minute und 17 Sekunden verbraucht, der prozedurale PL/SQL-Ansatz verbrauchte 1 Minute und 6 Sekunden. Halten wir da mal die nativen SQL/JSON-Funktionen, die ab 12.1.0.2 bereitstehen, dagegen.
select
  screen_name,
  count(*) anzahl_tweets
from tweets_json, json_table(
  tweet,
  '$'
  columns (
    screen_name varchar2(30)  path '$.user.screen_name',
    created_at  varchar2(100) path '$.created_at'
  )
)
where not json_exists(tweet, '$.retweeted_status')
group by screen_name
order by 2 desc
/  

SCREEN_NAME                    ANZAHL_TWEETS
------------------------------ -------------
orclapexblogs                            186
joelkallman                               62
swesley_perth                             45
flederbine                                39
andre_blu                                 39
:

Elapsed: 00:00:00:79
Es kommt das gleiche Ergebnis heraus - aber Sub-Second! 0.79 Sekunden, um genau zu sein. Wenn Ihr also auf einer 12c-Datenbank seid, verwendet auf jeden Fall die SQL/JSON-Funktionen zum JSON-Parsong - diese sind wesentlich effizienter. Mit APEX_JSON solltet Ihr nur dann parsen, wenn die nativen SQL/JSON-Funktionen nicht in Frage kommen - das wäre bspw. in einer Oracle11g-Datenbank der Fall.
Das mit APEX 5.0 eingeführte PL/SQL-Paket APEX_JSON eignet sich auch außerhalb von APEX sehr gut, um in PL/SQL mit JSON zu arbeiten. Neben dem Generieren von JSON bietet es auch Möglichkeiten an, JSON zu parsen. Besonders interessant ist die Möglichkeit, ein JSON mit TO_XMLTYPE in einen XMLTYPE zu wandeln, so dass man danach mit den SQL/XML-Funktionen darauf auch komplexere Abfragen durchführen kann.
Die mit Oracle12c eingeführten, nativen SQL/JSON-Funktionen bieten hierfür allerdings eine um Längen bessere Performance, so dass SQL-Funktionen wie JSON_VALUE, JSON_QUERY oder JSON_TABLE zum Parsen von JSON sicherlich zu bevorzugen wären.
This is the second of two blog postings about the new PL/SQL package APEX_JSON, which allows to work with JSON documents in a PL/SQL environment, even outside of APEX. While the first blog posting was about generating JSON with APEX_JSON, this posting will concentrate on the parsing side. We already have a bunch of JSON documents and want to work with their data. Before we begin, here is a very important note.
Beginning with Oracle12c, more specific: 12.1.0.2, the Oracle database provides native SQL/JSON fucntions zu parse and access JSON data. These are implemented within the database kernel and therefore much more efficient than a PL/SQL-based approach.
So, in an Oracle12c environment, always try to work with the nativer SQL/JSON functions instead of programming PL/SQL with APEX_JSON. JSON parsing with APEX_JSON is suited for pre-12c databases, where the native functions are not available. In this bloh posting, you'll also see a comparison between the two approaches.
Here we go: As an example, I'll take some JSON files which I obtained from the Twitter API. Here's a snippet.
{
  "id": 578903819884585000,
  "text": "RT @iAdvise_live: #countdown naar #Apexworld @OGh_nl ...
  "geo": {...}
  "lang": "nl",
  "retweet_count": 2,
  "created_at": "Fri Mar 20 13:00:00 +0000 2015",
  "user": {
      "statuses_count": 266,
      "lang": "en",
      "id": 1237892781789,
      "favourites_count": 62,        
      "name": "Jonathan ...",
      "screen_name": "jvanvianen78",
  }
  :
}
Assumed, we have a table containing instances of these JSON documents, we can start parsing these with APEX_JSON.PARSE. APEX_JSON will generate an in-Memory representation of the JSON data. We can then access the JSON data by calling methods like GET_VARCHAR2 or GET_NUMBER. A first procedure looks like this:
Important: If you are working with the very first APEX 5.0.1 release, and you are using a german or french NLS environment, execute the following call at the beginning of your session: ALTER SESSION SET NLS_NUMERIC_CHARACTERS='.,'
declare
  l_parsed_json apex_json.t_values;
  l_value       varchar2(4000);
begin
  for i in (select tweet from apextweets) loop
    apex_json.parse(
      p_values => l_parsed_json,
      p_source => i.tweet
    );
    dbms_output.put_line(
      'User "'||
      apex_json.get_varchar2(
        p_path => 'user.screen_name',
        p_values => l_parsed_json
      ) ||
      '" tweeted at ' ||
      apex_json.get_varchar2(
        p_path => 'created_at',
        p_values => l_parsed_json
      ) 
    );
  end loop;
end;
/
In this example, the processing results are being written onto the console with DBMS_OUTPUT. Of course, we could also process it otherwise or create a table function which returns structured output. So far, so good.
User "jvanvianen78" tweeted at Fri Mar 20 13:00:00 +0000 2015
User "Yvke1983" tweeted at Fri Mar 20 12:58:08 +0000 2015
User "iAdvise_live" tweeted at Fri Mar 20 12:57:35 +0000 2015
User "reynde75" tweeted at Fri Mar 20 11:54:56 +0000 2015
User "Smart4Apex" tweeted at Fri Mar 20 11:42:38 +0000 2015
User "brost" tweeted at Fri Mar 20 11:06:20 +0000 2015
User "johnnyq72" tweeted at Fri Mar 20 10:45:55 +0000 2015
User "crokitta" tweeted at Fri Mar 20 10:44:31 +0000 2015
User "johnnyq72" tweeted at Fri Mar 20 10:27:25 +0000 2015
User "PretiusSoftware" tweeted at Fri Mar 20 09:29:02 +0000 2015
:
But this is the same situation as described in the first blog posting. We will have to create procedural code for each individual query requirement. It would be much more convenient, to execute a SQL-like query direcly on the JSON data. And APEX_JSON offers a trick: The function TO_XMLTYPE converts JSON to an XMLTYPE instance ...
select apex_json.to_xmltype(tweet) from tweets_json where rownum = 1
/

APEX_JSON.TO_XMLTYPE(TWEET)
--------------------------------------------------------------------------------
<json>
  <retweeted_status>
    <text>#countdown naar #Apexworld @OGh_nl #iadvise_live @Yvke1983 ...
    <retweeted>false</retweeted>
    <truncated>false</truncated>
    <lang>nl</lang>
    <entities>
      <symbols/>
      <urls/>
      <hashtags>
        <row>
          <text>countdown</text>
          <indices>
            <row>0</row>
            <row>10</row>
:
... and as soon as we have XMLTYPE, we can start using the native SQL/XML functions which allow us to author SQL queries directly on XML data.
select
  screen_name,
  created_at,
  lang 
from tweets_json, xmltable(
  '/json'
  passing apex_json.to_xmltype(tweet)
  columns
    screen_name varchar2(30)  path 'user/screen_name',
    created_at  varchar2(100) path 'created_at',
    lang        varchar2(5)   path 'lang'
)
where rownum <= 20
/  

SCREEN_NAME                    CREATED_AT                          LANG
------------------------------ ----------------------------------- ----
jvanvianen78                   Fri Mar 20 13:00:00 +0000 2015      nl
Yvke1983                       Fri Mar 20 12:58:08 +0000 2015      nl
iAdvise_live                   Fri Mar 20 12:57:35 +0000 2015      nl
reynde75                       Fri Mar 20 11:54:56 +0000 2015      und
Smart4Apex                     Fri Mar 20 11:42:38 +0000 2015      nl
brost                          Fri Mar 20 11:06:20 +0000 2015      en
johnnyq72                      Fri Mar 20 10:45:55 +0000 2015      nl
crokitta                       Fri Mar 20 10:44:31 +0000 2015      nl
johnnyq72                      Fri Mar 20 10:27:25 +0000 2015      en
PretiusSoftware                Fri Mar 20 09:29:02 +0000 2015      en
josepcoves                     Mon Mar 23 18:57:22 +0000 2015      en
Fr4ncis                        Mon Mar 23 18:48:29 +0000 2015      en
BIntsifuL                      Mon Mar 23 17:58:38 +0000 2015      en
pauljacobs123                  Mon Mar 23 17:45:28 +0000 2015      en
S3v3n11                        Mon Mar 23 17:20:52 +0000 2015      en
This is much better - by changing the SQL query we can access any JSON attribute we want. By nesting XMLTABLE expressions, we can also access hierarchical structures; this has been an XML requirement for years. Using this powerful tool, we can take a more sophisticated challenge: "We want to know, how many tweets each user posted, but without retweets". The query is straightforward.
select
  screen_name,
  count(*) anzahl_tweets
from tweets_json, xmltable(
  '/json'
  passing apex_json.to_xmltype(tweet)
  columns
    screen_name varchar2(30) path 'user/screen_name',
    created_at  varchar2(100) path 'created_at'
)
where not xmlexists(
  '/json/retweeted_status'
  passing apex_json.to_xmltype(tweet)
)
group by screen_name
order by 2 desc
/  

SCREEN_NAME                    ANZAHL_TWEETS
------------------------------ -------------
orclapexblogs                            186
joelkallman                               62
swesley_perth                             45
flederbine                                39
andre_blu                                 39
:

Elapsed: 00:01:17:55
So, the SQL/XML functions take us a long way in parsing and working with JSON. Without those functions, we would have to author an awful lot of procedural code. In the following example, the descending sort is even missing.
declare
  type t_tabtype is table of number index by varchar2(200);

  l_parsed_json apex_json.t_values;
  l_user        varchar2(4000);
  l_tweetsbyusr t_tabtype;
  l_retweeted   boolean; 
begin
  for i in (select tweet from tweets_json) loop
    apex_json.parse(
      p_values => l_parsed_json,
      p_source => i.tweet
    );
    l_retweeted := apex_json.does_exist(
      p_path   => 'retweeted_status',
      p_values => l_parsed_json
    );
    if not l_retweeted then 
      l_user := apex_json.get_varchar2(
        p_path => 'user.screen_name',
        p_values => l_parsed_json
      );
      if l_tweetsbyusr.exists(l_user)  then
        l_tweetsbyusr(l_user) := l_tweetsbyusr(l_user) + 1;
      else 
        l_tweetsbyusr(l_user) := 1;
      end if;
    end if;
  end loop;
  l_user := l_tweetsbyusr.first;
  while l_user is not null loop
    dbms_output.put_line(l_user||':'||l_tweetsbyusr(l_user));
    l_user := l_tweetsbyusr.next(l_user);
  end loop;
end;
/

APEXORADEV:13
AliUK12:1
AljazMali:1
AntonScheffer:2
BIASCareers:2
BIntsifuL:7
BigBen212:1
BlueberryCoder:1
CPiasecki23:1
:

Elapsed: 00:01:06.36
Let's have a look on the exeution time: The table contains 4.526 JSON documents. For these documents, the SQL/XML approach took about 1 minute and 17 seconds, whereas the procedural approach took 1 minute and 6 seconds. We see, the XML overhead is rather small compared to the JSON parsing time. Let's compare this to the native SQL/JSON functions in Oracle12c.
select
  screen_name,
  count(*) anzahl_tweets
from tweets_json, json_table(
  tweet,
  '$'
  columns (
    screen_name varchar2(30)  path '$.user.screen_name',
    created_at  varchar2(100) path '$.created_at'
  )
)
where not json_exists(tweet, '$.retweeted_status')
group by screen_name
order by 2 desc
/  

SCREEN_NAME                    ANZAHL_TWEETS
------------------------------ -------------
orclapexblogs                            186
joelkallman                               62
swesley_perth                             45
flederbine                                39
andre_blu                                 39
:

Elapsed: 00:00:00:79
We get the same result, but in less than a second! 0.79 Seconds, to be precise. So, once more: If you are working in an Oracle12c environment, make sure to use the native SQL/JSON functions when parsing and accessing JSON data. APEX_JSON is suitable when the SQL/JSON functions are not available (like in Oracle 11.2) or not appropriate (you might want to use pure PL/SQL and avoid SQL in specific situations).
Summarized, the new APEX_JSON package introduced with APEX 5.0, is very usable outside of APEX as well. Beyond generating JSON it also allows o parse JSON documents and to access JSON data. After converting JSON to XMLTYPE, we can perform even flexible queries and access any JSON attribute we want - without authoring additional procedural code.
But the native SQL/JSON functions, which are available in Oracle12c and higher, offer much better query performance since these have been implemented directly into the database kernel. So, if possible, JSON parsing is done best with the JSON_VALUE, JSON_QUERY or JSON_TABLE SQL functions.

4. Mai 2015

APEX_JSON ohne APEX: JSON erzeugen mit SQL und PL/SQL

Dieses Blog Posting dreht sich um ein PL/SQL-Paket, welches mit APEX 5.0 eingeführt wurde, welches aber auch für Nicht-APEX-Entwickler sehr interessant ist: APEX_JSON. Dieses Paket enthält JSON-Unterstützung für PL/SQL-Programmierer - JSON lässt sich damit sowohl parsen und auslesen als auch erzeugen. Erstellt man sich damit eigene Funktionen, so kommt die JSON-Unterstützung auch in die SQL-Welt. APEX_JSON ist zwar Teil von APEX 5.0 (und wird auch mit diesem zusammen installiert), die Nutzung erfolgt aber komplett losgelöst von APEX selbst; man kann es auch ohne APEX verwenden. Für den SQL und PL/SQL Entwickler bieten sich damit zwei Anwendungsfelder für APEX_JSON:
  • JSON kann nun auch in Datenbankversionen vor 12.1.0.2 geparst werden. Ab 12.1.0.2 empfehle ich aber dringend den Einsatz der nativen JSON-Unterstützung - die SQL/JSON-Funktionen wie JSON_VALUE, JSON_QUERY oder JSON_TABLE sind direkt im Datenbankkern implementiert und dürften damit effizienter sein, als eine PL/SQL-Lösung. Wer aber noch 11.2 oder 12.1.0.1 einsetzen muss, findet in APEX_JSON eine Alternative.
  • Das native Erzeugen von JSON mit SQL anhand von Tabellendaten geht auch mit 12.1.0.2 noch nicht; dieses Feature ist zwar geplant, aber eben noch nicht da. Hier kann APEX_JSON ebenfalls Abhilfe leisten.
In diesem Blog-Posting werde ich daher zeigen, wie man JSON mit APEX_JSON aus Tabellendaten generieren kann. Wie man JSON mit APEX_JSON ausliest, bleibt dem nächsten Posting vorbehalten. Wir starten (wie immer) einfach: Es soll ein JSON aus der wohlbekannten Tabelle EMP erzeugt werden. Für den Anfang reicht die Spalte ENAME. Der Code mit APEX_JSON sieht in etwa wie folgt aus.
declare
  l_json clob;
begin
  apex_json.initialize_clob_output(DBMS_LOB.CALL, true, 2);
  apex_json.open_object();
  apex_json.open_array('emp');
  for i in (select ename from emp) loop
    apex_json.open_object();
    apex_json.write('ename', i.ename);
    apex_json.close_object;
  end loop;
  apex_json.close_array;
  apex_json.close_object;
  dbms_output.put_line(apex_json.get_clob_output);
end;
Als Ergebnis kommt heraus ...
{
  "emp":[
    { "ename":"SMITH" }
   ,{ "ename":"ALLEN" }
   ,{ "ename":"WARD" }
   ,{ "ename":"JONES" }

:

   ,{ "ename":"FORD" }
   ,{ "ename":"MILLER" }
  ]
}
Zu Beginn wird mit INITIALIZE_CLOB_OUTPUT festlegt, dass das generierte JSON in einen CLOB geschrieben werden soll (alternative steht für den APEX-Entwickler der HTP-Buffer bereit). INITIALIZE_CLOB_OUTPUT nimmt drei Parameter entgegen. Die ersten beiden Parameter benötigt APEX_JSON zum Erzeugen des temporären CLOBs - sie werden analog zu DBMS_LOB.CREATETEMPORARY genutzt. Der dritte Parameter legt fest, welche Einrückung für die JSON-Hierarchie verwendet werden soll.
Natürlich kann man auch komplexere JSON-Hierarchien generieren; man muss die Aufrufe von OPEN_OBJECT und OPEN_ARRAY nur entsprechend schachteln. Das folgende PL/SQL generiert JSON mit einer Hierarchieebene für die Tabellen DEPT und EMP.
declare
  l_json clob;
begin
  apex_json.initialize_clob_output(DBMS_LOB.CALL, true, 2);
  apex_json.open_object();
  apex_json.open_array('dept');
  for i in (select * from dept) loop
    apex_json.open_object();
    apex_json.write('deptno', i.deptno);
    apex_json.write('dname',  i.dname);
    apex_json.open_array('emp');
    for e in (select * from emp where deptno = i.deptno) loop
      apex_json.open_object();
      apex_json.write('empno', e.empno);
      apex_json.write('ename', e.ename);
      apex_json.write('sal',   e.sal);
      apex_json.close_object;
    end loop;
    apex_json.close_array;
    apex_json.close_object;
  end loop;
  apex_json.close_array;
  apex_json.close_object;
  dbms_output.put_line(apex_json.get_clob_output);
end;
/

{
  "dept":[
    {
      "deptno":10
     ,"dname":"ACCOUNTING"
     ,"emp":[
        {
          "empno":7782
         ,"ename":"CLARK"
         ,"sal":2450
        }
       ,{
          "empno":7839
         ,"ename":"KING"

:

   ,{
      "deptno":40
     ,"dname":"OPERATIONS"
     ,"emp":[
      ]
    }
  ]
}
Achtet als Entwickler darauf, die OPEN_OBJECT und OPEN_ARRAY Aufrufe korrekt zu schachteln und mit CLOSE_OBJECT und CLOSE_ARRAY-Aufrufen zu schließen. APEX_JSON stellt das nicht sicher, wenn also ein CLOSE_OBJECT-Aufruf fehlt, bekommt Ihr eine ungültige JSON-Struktur. APEX_JSON bietet euch die Prozedur CLOSE_ALL an, die man am besten als letztes aufruft; diese schließt alle noch offenen Objekte und Arrays - und euer JSON ist syntaktisch korrekt.
Das bedeutet aber, dass die JSON-Hierarchie immer in einer PL/SQL-Prozedur oder einem anonymen Block "programmiert" werden muss. Die Frage ist, ob man da nicht etwas automatisieren kann - und in der Tat gibt es eine Variante von APEX_JSON.WRITE, die einen Cursor entgegennimmt. Die probieren wir gleich mal aus: Wir bauen eine SQL-Funktion, die einen beliebigen Cursor entgegennimmt und daraus JSON generiert.
create or replace function make_json(
  p_cursor in sys_refcursor,
  p_root   in varchar2 default 'query',
  p_indent in number default 2
) return clob 
is
  l_json   clob;
  l_cursor sys_refcursor := p_cursor;
begin
  apex_json.initialize_clob_output(DBMS_LOB.CALL, true, p_indent);
  apex_json.open_object();
  apex_json.write(p_root, l_cursor);
  apex_json.close_object;
  return apex_json.get_clob_output;
end make_json;
/
sho err
Verwendet wird die Funktion wie folgt ...
SQL select make_json(cursor(select * from emp)) as json_clob from dual;

JSON_CLOB
--------------------------------------------------------------------------------
{
  "query":  [
    {
      "EMPNO":7369
     ,"ENAME":"SMITH"
     ,"JOB":"CLERK"
     ,"MGR":7902
     ,"HIREDATE":"1980-12-17T00:00:00Z"
     ,"SAL":800
     ,"DEPTNO":20
    }
   ,{

:

     ,"SAL":1300
     ,"DEPTNO":10
    }
  ]
}
In diese Funktion könnt Ihr nun jede beliebige Query hineingeben; es wird immer ein JSON mit korrekten Attributnamen generiert. Allerdings ist die Struktur des generierten JSON - ebenso wie das Ergebnis einer SQL-Query - immer flach. Manchmal wird aber JSON mit Hierarchie gebraucht - muss man nun wieder programmieren ...?
Interessanterweise gibt es eine Version von APEX_JSON.WRITE, die einen XMLTYPE entgegennimmt. Ginge es nur darum, das XML so, wie es ist, ins JSON einzubauen, könnte man auch mit der APEX_JSON.WRITE-Variante arbeiten, die einen CLOB entgegennimmt. Wenn es aber eine eigene Variante für XMLTYPE gibt, muss mehr dahinterstecken. Probieren wir das mal aus, indem wir eine kleine Objektstruktur als XMLTYPE modellieren und daraus JSON erzeugen ...
declare
  l_json clob;
begin
  apex_json.initialize_clob_output(DBMS_LOB.CALL, true, 2);
  apex_json.open_object('xml');
  apex_json.write(xmltype(
'<r>
  <c1>Test</c1>
  <c2>Test2</c2>
  <c3>
    <object>
      <a1>A1</a1>
      <a2>A2</a2>
    </object>
  </c3>
</r>'));
  apex_json.close_object;
  dbms_output.put_line(apex_json.get_clob_output);
end;
/

"xml":{
  {"c1":"Test","c2":"Test2","c3":{"object":{"a1":"A1","a2":"A2"}}}
}
APEX_JSON interpretiert den XMLTYPE ganz offensichtlich - und versucht, die XML-Struktur in JSON nachzubilden. Das kann man sich für unsere obige Funktion MAKE_JSON zunutze machen. Angenommen, wir erstellen eine XML-View auf die Tabelle EMP (dazu kann man die seit langem verfügbaren SQL/XML Funktionen hernehmen) ...
create or replace view empxml as 
select 
  empno, 
  xmlelement(
    "EMP",
    xmlelement("ENAME", ename), 
    xmlelement("SAL", ename), 
    xmlelement("MGR_TO", (
      select 
        xmlagg(
          xmlelement("EMP", 
            xmlelement("ENAME", d.ename)
          )
        )
        from emp d 
        where d.mgr = e.empno
      )
    ) 
  ) as xml from emp e
/
Die View lässt sich ganz normal selektieren ...
SQL> select empno, xmlserialize(document xml indent size=2)  xml from empxml e;

     EMPNO XML
---------- ----------------------------------------
      7369 <EMP>
             <ENAME>SMITH</ENAME>
             <SAL>SMITH</SAL>
             <MGR_TO/>
           </EMP>

      7566 <EMP>
             <ENAME>JONES</ENAME>
             <SAL>JONES</SAL>
             <MGR_TO>
               <EMP>
                 <ENAME>SCOTT</ENAME>
               </EMP>
               <EMP>
                 <ENAME>FORD</ENAME>
               </EMP>
             </MGR_TO>
           </EMP>
Gibt man das SELECT auf diese View nun als Cursor in die MAKE_JSON-Funktion, so bekommen wir JSON mit Hierarchie - und das ohne die Hierarchie in PL/SQL explizit zu programmieren ...
SQL> select make_json(cursor(select empno, xml emp from empxml)) from dual;

MAKE_JSON(CURSOR(SELECTEMPNO,XMLEMPFROMEMPXML))
--------------------------------------------------------------------------------
{
  "query":  [
    {
      "EMPNO":7369
     ,"EMP":{"ENAME":"SMITH","SAL":"SMITH","MGR_TO":null}
    }
   ,{
      "EMPNO":7566
     ,"EMP":{"ENAME":"JONES","SAL":"JONES","MGR_TO":[{"ENAME":"SCOTT"},{"ENAME": "FORD"}]}
    }
:

}
Was die XML-Struktur angeht, ist APEX_JSON sehr empfindlich: Angenommen, es soll ein Array mit Objekten, die je mehrere Attribute haben, generiert werden. Dann sollte in der generierten XML-Struktur jedes Objekt nochmals von einem XML-Tag umgeben sein - ist das nicht der Fall, so generiert APEX_JSON unter Umständen eine andere Struktur. Hier muss man einfach ein wenig experimentieren.
Das mit APEX 5.0 eingeführte PL/SQL-Paket APEX_JSON eignet sich auch außerhalb von APEX sehr gut, um JSON aus SQL und PL/SQL heraus zu generieren. Die zu erzeugende JSON-Struktur kann mit PL/SQL-Aufrufen "programmiert" werden. Darüber hinaus bietet APEX_JSON die Möglichkeit an, JSON anhand eines Cursors zu erzeugen; alle Attributnamen werden dann aus den Tabellenspalten abgeleitet. Selektiert dieser Cursor XML-Dokumente als XMLTYPE, so leitet APEX_JSON die JSON-Strukturen aus dem XML ab - komplexe, hierarchische JSON-Strukturen lassen sich so auch ohne PL/SQL-Programmierung erzeugen: Es braucht nur eine generische SQL-Funktion, welche einen Cursor entgegennimmt und ein CLOB zurückliefert.
This Blog Posting is about a PL/SQL package, which has recently been introduced with APEX 5.0, and which is very interesting also for non-APEX Developers: APEX_JSON. This package is based upon the very popular PL/JSON package and provides PL/SQL support for parsing and generating JSON. By building own SQL functions, APEX_JSON functionality can be brought to the SQL layer as well (if you are interested in this, read on). SQL and PL/SQL developers can find two very nice use-cases for APEX_JSON - outside of APEX:
  • JSON can now be parsed also in pre-12.1.0.2 databases. In 12.1.0.2 and higher, I'd recommand to use the new native SQL/JSON functions. JSON_VALUE, JSON_QUERY or JSON_TABLE have been implemented at SQL level, within the database kernel, and should therefore be more efficient than a PL/SQL solution. But those who need to implement JSON parsing on 11.2 or 12.1.0.1, can now use APEX_JSON very nicely for their requirements.
  • Generating JSON from SQL query results or table data is still not possible with SQL alone - even with 12.1.0.2. Until this feature arrives, we can do this kind of work with APEX_JSON.
So, in this blog posting, I'll show how JSON can be generated from table data using APEX_JSON - but at the SQL and PL/SQL layer - without APEX. The next posting will then be about parsing JSON with APEX_JSON. Let's start easy (as always): We want to generate JSON from the well known table EMP and for this first example, the ENAME column is sufficient.
declare
  l_json clob;
begin
  apex_json.initialize_clob_output(DBMS_LOB.CALL, true, 2);
  apex_json.open_object();
  apex_json.open_array('emp');
  for i in (select ename from emp) loop
    apex_json.open_object();
    apex_json.write('ename', i.ename);
    apex_json.close_object;
  end loop;
  apex_json.close_array;
  apex_json.close_object;
  dbms_output.put_line(apex_json.get_clob_output);
end;
The result of this PL/SQL block is ...
{
  "emp":[
    { "ename":"SMITH" }
   ,{ "ename":"ALLEN" }
   ,{ "ename":"WARD" }
   ,{ "ename":"JONES" }

:

   ,{ "ename":"FORD" }
   ,{ "ename":"MILLER" }
  ]
}
At the beginning, we initialize the JSON generator with INITIALIZE_CLOB_OUTPUT. So, the JSON output will be written to a (temporary) CLOB. Within APEX, there is also the alternative to write the output to the HTP buffer. INITIALIZE_CLOB_OUTPUT takes three parameters. The first two are being used to construct the internal temporary LOB, usage is the same as in DBMS_LOB.CREATETEMPORARY. The third parameter determines the indentation for the JSON hierarchy.
Of course, APEX_JSON also allows to create more complex JSON hierarchies. It's all about the nesting of OPEN_OBJECT and OPEN_ARRAY calls. So, the following example generates "nested JSON" for the tables DEPT and EMP.
declare
  l_json clob;
begin
  apex_json.initialize_clob_output(DBMS_LOB.CALL, true, 2);
  apex_json.open_object();
  apex_json.open_array('dept');
  for i in (select * from dept) loop
    apex_json.open_object();
    apex_json.write('deptno', i.deptno);
    apex_json.write('dname',  i.dname);
    apex_json.open_array('emp');
    for e in (select * from emp where deptno = i.deptno) loop
      apex_json.open_object();
      apex_json.write('empno', e.empno);
      apex_json.write('ename', e.ename);
      apex_json.write('sal',   e.sal);
      apex_json.close_object;
    end loop;
    apex_json.close_array;
    apex_json.close_object;
  end loop;
  apex_json.close_array;
  apex_json.close_object;
  dbms_output.put_line(apex_json.get_clob_output);
end;
/

{
  "dept":[
    {
      "deptno":10
     ,"dname":"ACCOUNTING"
     ,"emp":[
        {
          "empno":7782
         ,"ename":"CLARK"
         ,"sal":2450
        }
       ,{
          "empno":7839
         ,"ename":"KING"

:

   ,{
      "deptno":40
     ,"dname":"OPERATIONS"
     ,"emp":[
      ]
    }
  ]
}
Note, that you as the developer are responsible for the correct nesting of your OPEN_OBJECT and OPEN_ARRAY calls and that you have to call CLOSE_OBJECT and CLOSE_ARRAY repectively. Opening an object or an array without closing it leads to a wrong or invalid JSON structure. APEX_JSON provides the CLOSE_ALL procedure, which is best being called at the end of your procedure; it will prevent generating JSON with objects unclosed.
It seems that the JSON hierarchy always has to be "coded" with PL/SQL calls in a procedure or an anonymous block. In practice, we often need something more "automagically": We want to provide a query - and the generator does the rest. For this purpose, one overloading of APEX_JSON.WRITE takes a cursor as parameter. Let's try this out: We build a generic SQL function, taking a Cursor as its parameter and generating JSON from that cursor with APEX_JSON.
create or replace function make_json(
  p_cursor in sys_refcursor,
  p_root   in varchar2 default 'query',
  p_indent in number default 2
) return clob 
is
  l_json   clob;
  l_cursor sys_refcursor := p_cursor;
begin
  apex_json.initialize_clob_output(DBMS_LOB.CALL, true, p_indent);
  apex_json.open_object();
  apex_json.write(p_root, l_cursor);
  apex_json.close_object;
  return apex_json.get_clob_output;
end make_json;
/
sho err
This function can be used as follows ...
SQL select make_json(cursor(select * from emp)) as json_clob from dual;

JSON_CLOB
--------------------------------------------------------------------------------
{
  "query":  [
    {
      "EMPNO":7369
     ,"ENAME":"SMITH"
     ,"JOB":"CLERK"
     ,"MGR":7902
     ,"HIREDATE":"1980-12-17T00:00:00Z"
     ,"SAL":800
     ,"DEPTNO":20
    }
   ,{

:

     ,"SAL":1300
     ,"DEPTNO":10
    }
  ]
}
Now we have a function which can take any SQL query to generate JSON from its results. And we can use this query in the SQL layer - no PL/SQL needed from here on. With this function you should be able to take out a large amount of PL/SQL "JSON generator" code. This function does it all.
The JSON structure coming out of this function will always be as flat as a SQL query result is. In some cases, more complex JSON with a hierarchy is needed - does that mean we have to write PL/SQL code again ...?
Interestingly, there is another overloading of APEX_JSON.WRITE. This one takes XMLTYPE as its parameter, and its purpose is not to simply embed XML to the JSON output (the CLOB variant does this). This function can do more - and to explore how it works, again, a simple example is needed: Let's model a simple object structure as XMLTYPE and pass this to APEX_JSON.WRITE.
declare
  l_json clob;
begin
  apex_json.initialize_clob_output(DBMS_LOB.CALL, true, 2);
  apex_json.open_object('xml');
  apex_json.write(xmltype(
'<r>
  <c1>Test</c1>
  <c2>Test2</c2>
  <c3>
    <object>
      <a1>A1</a1>
      <a2>A2</a2>
    </object>
  </c3>
</r>'));
  apex_json.close_object;
  dbms_output.put_line(apex_json.get_clob_output);
end;
/

"xml":{
  {"c1":"Test","c2":"Test2","c3":{"object":{"a1":"A1","a2":"A2"}}}
}
APEX_JSON examines the XMLTYPE and tries to rebuild the XML structure in the JSON output. We can use this for the above MAKE_JSON function - the cursor needs to select an XMLTYPE column, then. Thus, we now create a more complex XML structure for the data within the EMP table. We use the SQL/XML functions to generate XML and encapsulate the SQL query in a view.
create or replace view empxml as 
select 
  empno, 
  xmlelement(
    "EMP",
    xmlelement("ENAME", ename), 
    xmlelement("SAL", ename), 
    xmlelement("MGR_TO", (
      select 
        xmlagg(
          xmlelement("EMP", 
            xmlelement("ENAME", d.ename)
          )
        )
        from emp d 
        where d.mgr = e.empno
      )
    ) 
  ) as xml from emp e
/
The view can be selected ...
SQL> select empno, xmlserialize(document xml indent size=2)  xml from empxml e;

     EMPNO XML
---------- ----------------------------------------
      7369 <EMP>
             <ENAME>SMITH</ENAME>
             <SAL>SMITH</SAL>
             <MGR_TO/>
           </EMP>

      7566 <EMP>
             <ENAME>JONES</ENAME>
             <SAL>JONES</SAL>
             <MGR_TO>
               <EMP>
                 <ENAME>SCOTT</ENAME>
               </EMP>
               <EMP>
                 <ENAME>FORD</ENAME>
               </EMP>
             </MGR_TO>
           </EMP>
Passing this query to the MAKE_JSON function leads to a JSON result with a hierarchy according to the structure of the XMLTYPE column XML. We generated JSON with a hierarchy, but we did not hard-code it with PL/SQL calls.
SQL> select make_json(cursor(select empno, xml emp from empxml)) from dual;

MAKE_JSON(CURSOR(SELECTEMPNO,XMLEMPFROMEMPXML))
--------------------------------------------------------------------------------
{
  "query":  [
    {
      "EMPNO":7369
     ,"EMP":{"ENAME":"SMITH","SAL":"SMITH","MGR_TO":null}
    }
   ,{
      "EMPNO":7566
     ,"EMP":{"ENAME":"JONES","SAL":"JONES","MGR_TO":[{"ENAME":"SCOTT"},{"ENAME": "FORD"}]}
    }
:

}
APEX_JSON seems to be a but picky regarding the XML structure; for instance, objects with multiple attributes should be encapsulated with an XML tag; if this is missing, you'll not get the right JSON structure. You might need to experiment a bit ...
Summarized, I think that the new APEX_JSON package introduced with APEX 5.0, is very usable outside of APEX as well. The SQL and PL/SQL programmer finds a nice solution for their "Generating JSON" needs. The option to generate JSON from a cursor allows very generic solutions - one PL/SQL procedure is able to generate JSON from multiple tables - always with correct attributes. Bringing XMLTYPE into the game even allows generic solutions with complex hierarchys. A simple SQL function taking a cursor and returning a CLOB brings all this functionality to the SQL layer.

Beliebte Postings