10. Oktober 2014

PL/SQL in Oracle 12.1: Index By Tables, SQL Abfragen und TABLE()

PL/SQL in 12.1: INDEX BY tables, SQL Queries and the TABLE() function
Eins der neuen PL/SQL Features in der Oracle Version 12.1 ist, dass - im Gegensatz zu 11 - noch mehr reine PL/SQL Typen in SQL-Queries oder DML-Anweidungen genutzt werden können. Reine PL/SQL-Typen sind RECORD-, aber auch BOOLEAN- oder "PL/SQL Index-By-Table"-Datentypen. Letztere werden auch als assoziative Arrays bezeichnet. Ab der Version 12.1 ist es nun möglich, eine solche PL/SQL Index-By-Table in SQL-Queries mit der Funktion TABLE() zu verwenden. zumindest die Dokumentation. Wie das genau funktioniert, beschreibt dieses Blog-Posting.
Zunächst der Setup: Ich brauche eine PL/SQL-Funktion, die eine Index-By Table erstellt und zurückgibt - außerdem muss der Type, der als Index-By-Table definiert ist, in einem Package deklariert werden (sonst kann man ihn gar nicht als Funktionsparameter verwenden). Die Dokumentation beschreibt bereits die eine oder andere Einschränkung. So muss u.a. das assoziative Array als INDEX BY PLS_INTEGER definiert sein.
create or replace package mypkg is
  type t_assoc_array is table of varchar2(20) index by pls_integer;
  function testit (p_amount in number) return t_assoc_array;
end mypkg;
/
sho err

create or replace package body mypkg is
  function testit (p_amount in number) return t_assoc_array is
    l_array t_assoc_array;
  begin
    for i in 1..p_amount loop
      l_array(i) := 'VALUE_'||i;
    end loop;
    return l_array;
  end testit;
end mypkg;
/
sho err
Nun könnte man versuchen, die Funktion aus einer SQL-Query zu verwenden ...
select * from table(mypkg.testit(20))
/

FEHLER in Zeile 1:
ORA-00902: Ungültiger Datentyp
Das funktioniert nicht. Und es gibt auch eine passende Einschränkung in der Dokumentation dazu: A PL/SQL function cannot return a value of a PL/SQL-only type to SQL. Doch wie kann man dieses Feature dann überhaupt nutzen?
declare 
  l_array mypkg.t_assoc_array;
begin
  l_array := mypkg.testit(20);
  for i in (
    -- Nutzung des PL/SQL Arrays in einer SQL Query - nun aber im PL/SQL Kontext
    select * from table(l_array)
  ) loop
    dbms_output.put_line(i.column_value);
  end loop;
end;
/

VALUE_1
VALUE_2
VALUE_3
VALUE_4
VALUE_5
VALUE_6
:

PL/SQL-Prozedur erfolgreich abgeschlossen.
Wenn man also eine SQL-Query innerhalb einer PL/SQL Prozedur, -Funktion oder eines anonymen Blocks ausführt, so kann man darin etwaige PL/SQL Index By Tables verwenden (TABLE()-Operator nutzen). Das kann sehr nützlich sein, wenn man die Index-By-Table mit einer Datenbanktabelle joinen möchte.
declare 
  l_array mypkg.t_assoc_array;
begin
  l_array(1) := 7839;
  l_array(2) := 7844;
  for i in (
    select ename, sal from emp e join table(l_array) a on e.empno = a.column_value
  ) loop
    dbms_output.put_line('ENAME: '||i.ename||' - SAL: '||i.sal);
  end loop;
end;
/

ENAME: KING - SAL: 5000
ENAME: TURNER - SAL: 1500

PL/SQL-Prozedur erfolgreich abgeschlossen.
Zusammenfassend kann man sagen, dass PL/SQL und SQL in 12.1 nochmal ein wenig besser zusammenarbeiten. PL/SQL Index-by-Tables können in SQL Queries verwendet werden, solange man sich im PL/SQL Kontext befindet. Für eine "top-level" SQL-Query sind sie aber weiterhin nicht erreichbar. So bleibt auch in 12.1. eine gewisse Grenze zwischen SQL und PL/SQL - aber die Richtung stimmt.
One of the new things in Oracle12c is, that more (compared to Oracle11g) pure PL/SQL types are accessible from SQL queries or DML statements. Pure PL/SQL types are record-, boolean or "INDEX BY" types. The latter are also called associative arrays. So, beginning with 12.1, we can access PL/SQL INDEX BY tables with a SQL query - according to the documentation. This blog posting describes how it works (and what does not work).
First, the setup. We need (of course) a PL/SQL function returing a PL/SQL INDEX BY table. And in order to create such a function, we need to have a package specification with the INDEX BY table definition. To make it easy, we put the function into the package as well. Reading the documentation carefully, we observe the first restriction for this feature: The INDEX BY table must be declared as INDEX BY PLS_INTEGER.
create or replace package mypkg is
  type t_assoc_array is table of varchar2(20) index by pls_integer;
  function testit (p_amount in number) return t_assoc_array;
end mypkg;
/
sho err

create or replace package body mypkg is
  function testit (p_amount in number) return t_assoc_array is
    l_array t_assoc_array;
  begin
    for i in 1..p_amount loop
      l_array(i) := 'VALUE_'||i;
    end loop;
    return l_array;
  end testit;
end mypkg;
/
sho err
Now we do the first test: Use the INDEX BY table within a SQL query ...
select * from table(mypkg.testit(20))
/

ERROR at line 1:
ORA-00902: invalid datatype
Does not work. Bummer. OK, read the documentation again. Aah, there is the other restriction: A PL/SQL function cannot return a value of a PL/SQL-only type to SQL. But what's the value of that new feature then ...? And how can it be used?
declare 
  l_array mypkg.t_assoc_array;
begin
  l_array := mypkg.testit(20);
  for i in (
    -- Nutzung des PL/SQL Arrays in einer SQL Query - nun aber im PL/SQL Kontext
    select * from table(l_array)
  ) loop
    dbms_output.put_line(i.column_value);
  end loop;
end;
/

VALUE_1
VALUE_2
VALUE_3
VALUE_4
VALUE_5
VALUE_6
:

PL/SQL procedure successfully completed.
So, PL/SQL INDEX BY tables are accessible for a SQL statement within a PL/SQL procedure, function or anonymous block (use the TABLE() operator). This can be very useful: think about joining your PL/SQL INDEX BY table to a database table, as follows ...
declare 
  l_array mypkg.t_assoc_array;
begin
  l_array(1) := 7839;
  l_array(2) := 7844;
  for i in (
    select ename, sal from emp e join table(l_array) a on e.empno = a.column_value
  ) loop
    dbms_output.put_line('ENAME: '||i.ename||' - SAL: '||i.sal);
  end loop;
end;
/

ENAME: KING - SAL: 5000
ENAME: TURNER - SAL: 1500

PL/SQL procedure successfully completed.
In Oracle 12.1, PL/SQL and SQL work more closely together. We can use INDEX BY tables within SQL queries now, as long as this query is being executed within a PL/SQL context. So INDEX BY tables are still PL/SQL, not SQL data types. We still have some barriers for PL/SQL data types in SQL - but the development direction is good.

26. September 2014

XML Namespaces, XMLTABLE und die Oracle Datenbank

Dealing with XML namespaces in the Oracle database
Heute geht es um einen Aspekt beim Umgang mit XML in der Oracle-Datenbank, der recht häufig zu Fragen und scheinbar unverständlichem Verhalten der Datenbank führt: die XML Namespaces. Dazu (wie immer) ein einführendes Beispiel.
create table xml_tab (
  id  number,
  xml xmltype
)
/

insert into xml_tab values (
  1, 
  '<document xmlns="a">
     <blog>SQL und PL/SQL</blog>
     <thema>Oracle Datenbank</thema>
   </document>'
);

commit
/
Aus diesem XML-Dokument sollen nun, mit der SQL-Funktion XMLTABLE, die Inhalte extrahiert werden - wie das geht, findet man recht schnell heraus:
select 
  blog, 
  thema
from xml_tab t, xmltable(
  '/document'
  passing xml
  columns 
    blog  varchar2(30) path 'blog',
    thema varchar2(30) path 'thema'
) x
/

No rows selected.
Wie man sieht, funktioniert das nicht - man überprüft die Angaben der XML-Tags noch einmal und stellt fest, dass alles richtig ist. Der Grund für das Verhalten liegt an der Namespace Deklaration im XML-Dokument - und zwar hier.
<document xmlns="a">...</document>
XML kennt das Konzept der Namespaces: Ein XML-Tag wird nicht nur durch den Namen selbst bestimmt, in diesem Fall also document, sondern auch durch den Namespace. Damit wird es möglich, in ein- und demselben XML-Dokument mehrere Tags des scheinbar gleichen Namens zu haben - durch unterschiedliche Namespaces ist es aber nicht mehr dasselbe Tag. Namespaces werden in der Praxis meist mit mit URLs (bspw. http://www.meinefirma.de/xmlnamespace1) benannt - damit sie global möglichst eindeutig sind (die Webadressen müssen natürlich nicht tatsächlich funktionieren - es sind nur Namen). Man kann aber auch, wie oben, jede beliebige Zeichenkette hernehmen. Gerade wenn der Namespace mit einer längeren URL benannt ist, wäre es aber recht umständlich, die gesamte URL zu jedem XML-Tag dazuzuschreiben - die ohnehin schon sehr großen XML-Dokumente würden noch größer und schwieriger zu verarbeiten. Daher gibt es zusätzlich ...
  • Namespace-Präfixe
  • Default Namespace
Beide werden mit dem Attribut xmlns definiert. Im Beispiel oben wird der Namespace "a" als Default-Namespace deklariert. Alle XML-Tags ohne ein Namespace-Präfix werden also dem Namespace "a" zugeordnet. Alternativ könnte man auch einen Namespace-Präfix deklarieren - dann sieht das XML-Dokument so aus.
insert into xml_tab values (
  2, 
  '<pr:document xmlns:pr="a">
     <pr:blog>SQL und PL/SQL</pr:blog>
     <pr:thema>Oracle Datenbank</pr:thema>
   </pr:document>'
);
Inhaltlich sind beide XML-Dokumente exakt identisch - ein XML-Parser macht keinen Unterschied. Die XML-Tags gehören in beiden Fällen zum Namespace "a", nur ist das einmal der Default-Namspace, im zweiten Fall werden explizite Präfixe verwendet.
Und weil das alles noch nicht genug ist, gibt es auch XML-Dokumente, in denen das Attribut xmlns fehlt oder in denen es XML-Tags ohne Präfix, aber keinen Default-Namespace gibt. Diese Tags haben dann keinen Namespace, manche sprechen auch vom Null-Namespace. Ein XML-Tag ohne Namespace ist nach dem XML-Standard ein anderes Tag als eines mit Namespace. Und das alles kann in ein- und demselben XML-Dokument auch gemischt werden.
Diese Dinge müssen beim Formulieren der SQL-Abfrage mit XMLTABLE berücksichtigt werden; im Eingangsbeispiel gehören alle XML-Tags zum Namespace "a", die XMLTABLE-Abfrage enthält aber keinerlei Namespace-Definition. Also hat sie nach XML-Tags ohne Namespace gesucht, davon (natürlich) keine gefunden, daher das No rows selected.. Die Abhilfe kann so aussehen ...
select
  blog,
  thema
from xml_tab t, xmltable(
  xmlnamespaces('a' as "p"),
  '/p:document'
  passing xml
  columns
    blog  varchar2(30) path 'p:blog',
    thema varchar2(30) path 'p:thema'
) x
/

BLOG                           THEMA
------------------------------ ------------------------------
SQL und PL/SQL                 Oracle Datenbank
SQL und PL/SQL                 Oracle Datenbank

2 rows selected.
... oder so:
select
  blog,
  thema
from xml_tab t, xmltable(
  xmlnamespaces(default 'a'),
  '/document'
  passing xml
  columns
    blog  varchar2(30) path 'blog',
    thema varchar2(30) path 'thema'
) x
/

BLOG                           THEMA
------------------------------ ------------------------------
SQL und PL/SQL                 Oracle Datenbank
SQL und PL/SQL                 Oracle Datenbank

2 rows selected.
Man sieht, dass die XMLNAMESPACES-Klausel für die XMLTABLE-Funktion die gleiche Bedeutung hat, wie das Attribut xmlns im XML-Dokument. In beiden Fällen wird der Namespace auf ein Präfix abgebildet oder als Default festgelegt. In den folgenden XQuery oder XPath Ausdrücken muss dann der deklarierte Präfix verwendet werden. Es muss aber keinesfalls der gleiche Präfix wie im Dokument verwendet werden - der Präfix oder die Tatsache, dass ein Namespace als Default deklariert ist, ist völlig bedeutungslos. Wichtig ist allein der Name des Namespace, also das "a". Und natürlich kann man sich nun auch XML-Dokument mit mehreren Namespaces vorstellen ...
insert into xml_tab values (
  3, 
  '<ns1:document xmlns:ns1="a" xmlns:ns2="b" xmlns:ns3="c">
     <ns2:blog>SQL und PL/SQL</ns2:blog>
     <ns3:thema>Oracle Datenbank</ns3:thema>
   </ns1:document>'
);
Nun gehört jedes XML-Tag tatsächlich zu einem anderen Namespace. Dieses Dokument ist auch für einen XML-Parser inhaltlich ein anderes Dokument als die ersten beiden. Es werden drei Namespaces deklariert: a, b und c. Während a als Default-Namespace deklariert wird (also kein Präfix für das XML-Tag), erhalten b und c die Präfixe ns1 und ns2. In der XMLTABLE-Abfrage muss das berücksichtigen.
select 
  blog, 
  thema
from xml_tab t, xmltable(
  xmlnamespaces(default 'a', 'b' as "p1", 'c' as "p2"),
  '/document'
  passing xml
  columns 
    blog  varchar2(30) path 'p1:blog',
    thema varchar2(30) path 'p2:thema'
) x
/
Natürlich muss man in der XMLTABLE-Abfrage nicht mit einem Default-Namespace arbeiten, man kann auch alle drei auf Präfixe abbilden. Wichtig ist nur, dass die eigentlichen Namen der Namespaces, nämlich a, b und c, korrekt angesprochen werden. Achtet man darauf, so sind auch XML-Dokumente mit Namespaces kein Problem mehr.
This blog posting is about XML namespaces, and how to deal with them when processing XML with SQL functions. This often leads to confusion and questions - the following sections will try to shed some light into this. We'll start with a simple example ...
create table xml_tab (
  id  number,
  xml xmltype
)
/

insert into xml_tab values (
  1, 
  '<document xmlns="a">
     <blog>SQL und PL/SQL</blog>
     <thema>Oracle Datenbank</thema>
   </document>'
);

commit
/
From this XML document, we want to extract data using the XMLTABLE function. After reading the documentation, the SQL query is authored rather quickly.
select 
  blog, 
  thema
from xml_tab t, xmltable(
  '/document'
  passing xml
  columns 
    blog  varchar2(30) path 'blog',
    thema varchar2(30) path 'thema'
) x
/

No rows selected.
But, although all tag names are correct, it does not work. The reason is the XML namespace declaration at the beginning of the document.
<document xmlns="a">...</document>
XML has the concept of namespaces. An XML tag is being identified not only by its name (here: document), but also by its namespace. Using this, XML allows tags having the same name, but different semantics. By considering both name and namespace, an XML engine is able to differentiate between the tags. In practice, namespaces are being named with URLs like http://mycompany.com/myproject/mynamespace (these URLs don't have to exist physically - it's just a name). Instead of a URL, we can also use any character string for a namespace, like in the example above: "a" (which is not likely to be globally unqiue, of course). If a URL, or a long string is being used for a namespace, we seem to have a problem: We need to add this string to each and every XML Tag, don't we ...?
  • Namespace prefixes map long namespaces to short prefixes
  • The default namespace is being used for tags without a prefix
Both are declared with the xmlns attribute: xmlns="a" declares "a" as the default namespace wheres xmlns:pr="a" maps "a" to the namespace prefix "pr". In our example, a default namespace is being used. As an alternative, we can declare a namespace prefix. Then we need to add it to the XML tags - like in the following XML document.
insert into xml_tab values (
  2, 
  '<pr:document xmlns:pr="a">
     <pr:blog>SQL und PL/SQL</pr:blog>
     <pr:thema>Oracle Datenbank</pr:thema>
   </pr:document>'
);
Both XML documents have exactly the same semantics - for an XML parser there is no difference. And that's not all: XML tags can also have no namespace at all. Some refer to this as the null or empty namespace. This is the case, when the xmlns attribute is either not present or when the XML document has no default namespace, but contains XML tags without a namespace prefix. Therefore we can have XML tags with, and without a namespace and - of course - mutliple different namespaces - all within the same XML document.
We need to take care about this when authoring XMLTABLE queries. In the above example, all XML tags were part of the "a" namespace. But the XMLTABLE query did not contain a namespace declaration - so it looked for tags without a namespace. Since the XML document does not contain these, the query result is correct. To get the query working - we need to add the XMLNAMESPACES clause. A working solution can look like this ...
select
  blog,
  thema
from xml_tab t, xmltable(
  xmlnamespaces('a' as "p"),
  '/p:document'
  passing xml
  columns
    blog  varchar2(30) path 'p:blog',
    thema varchar2(30) path 'p:thema'
) x
/

BLOG                           THEMA
------------------------------ ------------------------------
SQL und PL/SQL                 Oracle Datenbank
SQL und PL/SQL                 Oracle Datenbank

2 rows selected.
... or like this ...
select
  blog,
  thema
from xml_tab t, xmltable(
  xmlnamespaces(default 'a'),
  '/document'
  passing xml
  columns
    blog  varchar2(30) path 'blog',
    thema varchar2(30) path 'thema'
) x
/

BLOG                           THEMA
------------------------------ ------------------------------
SQL und PL/SQL                 Oracle Datenbank
SQL und PL/SQL                 Oracle Datenbank

2 rows selected.
The XMLNAMESPACES clause within an XMLTABLE query has the same meaning as the xmlns attribute within an XML document: a namespace is mapped to a prefix or declared as default. The prefixes and defaults within a query are independent from the prefixes and defaults in the document - only the namespaces themselves (here "a") are important. Having this in mind, we can now also imagine documents with tags from multiple namespaces.
insert into xml_tab values (
  3, 
  '<ns1:document xmlns:ns1="a" xmlns:ns2="b" xmlns:ns3="c">
     <ns2:blog>SQL und PL/SQL</ns2:blog>
     <ns3:thema>Oracle Datenbank</ns3:thema>
   </ns1:document>'
);
This XML document is semantically different to the previous ones, since each XML tag belongs to another namespace: a, b and c. a is being declared as the default (XML tag has no prefix); b and c are mapped to prefixes ns1 and ns2. A working XMLTABLE query can look as follows.
select 
  blog, 
  thema
from xml_tab t, xmltable(
  xmlnamespaces(default 'a', 'b' as "p1", 'c' as "p2"),
  '/document'
  passing xml
  columns 
    blog  varchar2(30) path 'p1:blog',
    thema varchar2(30) path 'p2:thema'
) x
/
As already said, the prefixes used in the XMLTABLE query can be different from the prefixes in the document: the namespace itself is important - not the prefix. We do also not need to use a default namespace - another possibile solution would be to have three different namespace prefixes. Taking care about this, XML documents with namespaces are no problem at all.

4. September 2014

node.js und die Oracle-Datenbank: Erste Versuche ...

node.js and the Oracle database: First experiments
In diesem Blog-Posting möchte ich ein wenig von meinen ersten Gehversuchen mit der Oracle-Datenbank und node.js berichten. node.js selbst möchte ich jedoch nicht im Detail vorstellen, dazu gibt es ausreichend Information im Internet. Auch zum Thema Installation und Setup einer node.js-Umgebung sowie zur Installation zusätzlicher Pakete mit "npm" sind zahlreiche Tutorials verfügbar. Hier geht es vor allem um node.js und die Oracle-Datenbank.
Bekanntlich kann man node.js recht gut mit "JavaScript auf dem Server" bezeichnen. Basis ist die JavaScript-Engine des Chrome-Browsers, die dann JavaScript-Programme per Kommandozeile startet und ausführt. Wie andere Programmiersprachen bietet auch node.js mittlerweile eine recht umfangreiche und erweiterbare Funktionsbibliothek mit, die von der sehr aktiven Community ständig weiterentwickelt wird. Einige Wichtige Eigenschaften von node.js seien aber vorab genannt: Node.js ist Asynchron, event-getrieben und nicht-blockierend. Was das genau bedeutet, sehen wir noch.
Möchte man mit node.js auf eine Oracle-Datenbank zugreifen, stellt sich (wie immer) die Frage nach einem Treiber. Von Oracle gibt es derzeit (noch) keinen node.js Treiber, so dass wir auf das Angebot der Community zurückgreifen müssen - und siehe da: Es existiert ein Oracle-Treiber namens node-oracle von Joe Ferner, den man von GitHub herunterladen kann.
Dem Download liegt ein README bei, welches die Installation in eine bestehende node.js Umgebung beschreibt. Auf Unix/Linux-Systemen gelang mir das auf Anhieb und ohne weitere Probleme. Danach kann man sein erstes node.js-Programm schreiben. Ich greife natürlich auf die Oracle-Datenbank zu, selektiere erst mal etwas und gebe es aus. Das sieht dann so aus.
var oracle = require('oracle');

var connectData = {
  hostname: "sccloud034.de.oracle.com",
  port:     1521,
  database: "pdb01.de.oracle.com", 
  user:     "scott",
  password: "tiger"
}

oracle.connect(connectData, function(err, connection) {
  if (err) {
   console.log("Error connecting to db:", err); return;
  }

  connection.execute("select ename, job from emp ", [],  function(err, results) {
    connection.close();
    console.log(results);
  });
});

console.log("done");
Der Code sieht etwas anders aus, als man es von prozeduralen Programmiersprachen gewohnt ist: node.js arbeitet bei Funktionen, die "I/O bound" sind (Netzwerk, Dateisystem) asynchron. Die Funktion oracle.connect ist I/O bound und bekommt neben den Verbindungsdaten zur Oracle-Datenbank auch eine Callback-Funktion übergeben - diese wird aufgerufen, wenn die Verbindung hergestellt wurde. node.js ist nicht-blockierend: oracle.connect wird asynchron ausgeführt, das Skript arbeitet währenddessen weiter. Demnach muss der Code, der von oracle.connect abhängt, in eine Callback-Funktion gepackt werden.
Startet man das Programm, so bekommt man folgendes Ergebnis.
$ node ora.js
done
[ { ENAME: 'SMITH', JOB: 'CLERK' },
  { ENAME: 'ALLEN', JOB: 'SALESMAN' },
  { ENAME: 'WARD', JOB: 'SALESMAN' },
  { ENAME: 'JONES', JOB: 'MANAGER' },
  { ENAME: 'MARTIN', JOB: 'SALESMAN' },
  { ENAME: 'BLAKE', JOB: 'MANAGER' },
  { ENAME: 'CLARK', JOB: 'MANAGER' },
  { ENAME: 'SCOTT', JOB: 'ANALYST' },
  { ENAME: 'KING', JOB: 'PRESIDENT' },
  { ENAME: 'TURNER', JOB: 'SALESMAN' },
  { ENAME: 'ADAMS', JOB: 'CLERK' },
  { ENAME: 'JAMES', JOB: 'CLERK' },
  { ENAME: 'FORD', JOB: 'ANALYST' },
  { ENAME: 'MILLER', JOB: 'CLERK' } ]
$
Interessant ist, dass das "done", welches im Skript ja erst am Ende ausgegeben wird, dennoch zuerst ausgegeben wurde. Das hängt eben mit der bereits erwähnten asynchronen Natur eines node.js-Programms zusammen. Wenn man sich den Code genau ansieht, so sieht man, dass auf der obersten Ebene nur 2 Statements vorhanden sind: oracle.connect und console.log. Während oracle.connect asynchron abgearbeitet wird, arbeitet node.js mit dem zweiten Statement weiter. Als erstes wird demnach "done" ausgegeben. Erst danach ist der Verbindungsaufbau zu Oracle fertig, node.js ruft die Callback-Funktion auf und führt das eigentliche SELECT durch.
Als nächstes soll ein node.js Skript verwendet werden, um Dateien in die Datenbank hochzuladen. Mit dem Aufruf soll ein Parameter übergeben werden, der auf ein Verzeichnis zeigt - das node.js Skript soll dann alle Textdateien in die Tabelle DOCUMENT_TABLE in der Datenbank speichern. Zuerst also die Tabelle anlegen ...
create table document_table (filename varchar2(200), document clob);
Dann kommt das node.js Skript - es arbeitet eigentlich wie vorhin. Allerdings wird nun, sobald die Verbindung zur Datenbank hergestellt wurde, das angegebene Directory durchgearbeitet und alle gefundenen Dateien werden mit oracle.execute in die Datenbank geladen. Die Arbeit mit den Callback-Funktionen kann, wie man sehen kann, zu stark geschachteltem Code führen. Node.js bietet hier durchaus Ansätze, das soll aber nicht Thema dieses Postings sein.
var fs = require("fs");
var ora = require("oracle");

var oradb = {
  hostname: "sccloud034.de.oracle.com",
  port:     1521,
  database: "orcl",
  user:     "scott",
  password: "tiger"
}

var dirname = process.argv[2];

ora.connect(oradb, function(err, connection) {
  if (err) {throw err;}
  connection.setAutoCommit(false);
  var fileCount = 0;
  fs.readdir(dirname, function (err, files) {
    if (err) {throw err;}
    files.forEach(function (file) {
      fileCount ++;
      fs.readFile(dirname+"/"+file, 'utf-8', function (err, data) {
        connection.execute(
          "insert into document_table values (:1, :2)",
          [file, data],
          function (err, results) {
            if (err) {throw err;} else {
              console.log('"'+file+'" saved.');
              fileCount--;
              if (fileCount == 0) {
                connection.close();
                console.log("done");
              }
            }
        });
      });
    });
  });
});
Nun wurden alle Dateien über genau eine einzige Verbindung an die Oracle-Datenbank gespeichert. Die Dateien werden also sequenziell verarbeitet. Wenn nun auf Datenbankseite mehr Ressourcen bereitstehen, dann würde man auch gerne mehr Datenbankverbindungen nutzen - schreiben wir das Skript also ein wenig um - so dass für jede Datei nun eine neue Verbindung geöffnet wird. Der folgende Code arbeitet sich also zuerst durch die Dateien des angegebenen Verzeichnisses und versucht dann, für jede Datei eine Datenbankverbindung zu öffnen und die Datei an die Datenbank zu senden.
var fs = require("fs");
var ora = require("oracle");

var oradb = {
  hostname: "sccloud034.de.oracle.com",
  port:     1521,
  database: "pdb01.de.oracle.com",
  user:     "scott",
  password: "tiger"
}

var dirname = process.argv[2];

fs.readdir(dirname, function (err, files) {
  if (err) {throw err;}
  files.forEach(function (file) {
    fs.readFile(dirname+"/"+file, 'utf-8', function (err, data) {
      ora.connect(oradb, function(err, connection) {
        if (err) {throw err;}
        connection.execute(
          "insert into document_table values (:1, :2)",
          [file, data],
          function (err, results) {
            if (err) {throw err;}
            console.log('"'+file+'" saved.');
            connection.close();
        });
      });
    });
  });
});
Wenn man dieses Skript startet, passiert erstmal gar nichts ... und nach einer Weile bricht es typischerweise mit einem Fehler ab. Wird mit einer Datenbank gearbeitet, die große Ressourcen bereitstellt, so kann das Skript tatsächlich auch erfolgreich laufen - effizient arbeitet es aber in keinem Fall.
[oracle@sccloud033 node]$ node loadFiles.js po

/home/oracle/node/loadFiles.js:19
          if (err) {throw err;}
                          ^
Error: ORA-12516: TNS:listener could not find available handler with matching protocol stack
Noch klarer wird das ganze, wenn man (während das Skript läuft), die Oracle Data Dictionary View V$SESSION beobachtet.
SQL> select username, count(*) from v$session group by username

USERNAME                         COUNT(*)
------------------------------ ----------
                                       28
SCOTT                                 154
SYS                                     1

-- nach einer Weile ...

SQL> select username, count(*) from v$session group by username

USERNAME                         COUNT(*)
------------------------------ ----------
                                       26
SCOTT                                 366
SYS                                     1

Um dieses Verhalten zu verstehen, muss man wieder an die Tatsache denken, dass node.js asynchron arbeitet. Wenn im Verzeichnis 500 Dateien liegen, sind arbeitet sich das Skript schnell durch diese 500 Dateien durch und versucht, für jede Datei asynchron eine Datenbankverbindung zu öffnen (wie gelernt: Während das läuft arbeitet das Skript weiter). Das aber dauert (im Vergleich zum Lesen des Verzeichnisses) recht lange, so das das Verzeichnis durchgearbeitet ist, noch bevor die erste Datenbankverbindung steht - als Folge entsteht der Bedarf nach 500 Datenbankverbindungen - Oracle kommt gar nicht mehr hinterher.
Es braucht einen Connection-Pool, der die verwendeten Datenbankverbindungen reguliert. Node.js bietet keine Connection-Pools für Datenbankverbindungen an, aber zum Glück hat die Oracle-Datenbank die Lösung. Denn seit der Oracle-Version 11.1 gibt es den Database Resident Connection Pool, der für solche Umgebungen, die keine eigenen Connection-Pools bereitstellen können, geschaffen wurde. Man dachte damals vor allem an PHP - für node.js eignet sich der Pool genauso gut. Aber erst muss der Pool auf Datenbankseite gestartet werden. Das geschieht mit dem PL/SQL-Paket DBMS_CONNECTION_POOL, mit dem auch Einstellungen am Pool vorgenommen werden können. Hier muss allerdings mit DBA-Privilegien gearbeitet werden. Verwendet man Oracle12c, so muss der Connection-Pool in der Container-Datenbank, nicht in der Pluggable Database gestartet und eingerichtet werden.
SQL> exec dbms_connection_pool.start_pool;

PL/SQL-Prozedur erfolgreich abgeschlossen.
Dieser Connection Pool ist nicht mit der Shared Server Architektur vergleichbar. Die Datenbank verwaltet nun selbstständig einen Pool von Dedicated Server-Verbindungen. Wie bei jedem Connection Pool kann man die minimale und maximale Anzahl der gehaltenen Verbindungen einstellen. Wenn ein Client eine Verbindung braucht, wird der Dedicated Server Prozess nicht neu erzeugt, er wird aus dem Pool genommen - und beim Schließen der Verbindung wird er an den Pool zurückgegeben. Wenn gerade keine Verbindung frei ist, wartet der Client solange bis eine frei ist. Das ist genau das, was wir für node.js brauchen. Zuerst stellen wir noch die minimale und maximale Anzahl Verbindungen ein.
begin
  DBMS_CONNECTION_POOL.ALTER_PARAM ('','MINSIZE', '10');
  DBMS_CONNECTION_POOL.ALTER_PARAM ('','MAXSIZE', '20');
end;
/
Einstellungen überprüfen ...
SQL> SELECT * FROM DBA_CPOOL_INFO;

CONNECTION_POOL
--------------------------------------------------------------------------------
STATUS              MINSIZE    MAXSIZE   INCRSIZE SESSION_CACHED_CURSORS
---------------- ---------- ---------- ---------- ----------------------
INACTIVITY_TIMEOUT MAX_THINK_TIME MAX_USE_SESSION MAX_LIFETIME_SESSION
------------------ -------------- --------------- --------------------
 NUM_CBROK MAXCONN_CBROK
---------- -------------
SYS_DEFAULT_CONNECTION_POOL
ACTIVE                   10         20          2                     20
               300            120          500000                86400
         1         40000

Um den Pool nun mit unserem node.js Programm zu nutzen, müssen nur die Verbindungsdaten angepasst werden. Übrigens: Das :POOLED kann man an jeden Oracle-Connection-String anhängen - der Database Resident Connection Pool lässt sich also auch mit Java, .NET oder SQL*Plus nutzen.
var fs = require("fs");
var ora = require("oracle");

var oradb = {
  hostname: "sccloud034.de.oracle.com",
  port:     1521,
  database: "pdb01.de.oracle.com:POOLED",
  user:     "scott",
  password: "tiger"
}

var dirname = process.argv[2];

fs.readdir(dirname, function (err, files) {
  if (err) {throw err;}
  files.forEach(function (file) {
    fs.readFile(dirname+"/"+file, 'utf-8', function (err, data) {
      ora.connect(oradb, function(err, connection) {
        if (err) {throw err;}
        connection.execute(
          "insert into document_table values (:1, :2)",
          [file, data],
          function (err, results) {
            if (err) {throw err;}
            console.log('"'+file+'" saved.');
            connection.close();
        });
      });
    });
  });
});
Nun wird das Programm durchlaufen - als DBA kann man die View V$CPOOL_STATS und so die Auslastung der Datenbank beobachten ...
SQL> SELECT num_open_servers, num_busy_servers, num_auth_servers, num_requests
  2  FROM V$CPOOL_STATS

NUM_OPEN_SERVERS NUM_BUSY_SERVERS NUM_AUTH_SERVERS NUM_REQUESTS
---------------- ---------------- ---------------- ------------
              15                9                1          799

Der Schlüssel zu skalierbaren node.js Skripten mit der Oracle-Datenbank liegt also tatsächlich in diesem etwas unscheinbaren Connection Pool. Ein node.js Skript sollte ihn auf jeden Fall verwenden - genau wie PHP.
Vorerst soll das genug sein - ich finde node.js nicht uninteressant; man kann es dank der Funktionsbibliothek sehr schön als Skripting Sprache einsetzen: In einem anderen Beispiel habe ich XML-Dateien nach JSON konvertiert und in die Datenbank geladen; im Vergleich zu diesen Skripten waren das nur wenige Codezeilen mehr ...
In this blog posting, I'd like write something about my first experiences with the Oracle database and node.js. This will not be a tutorial on how to install and configure node.js, and how to install new packages - there are plenty tutorials of this kind available in the internet. This posting will focus on how to deal with the Oracle database in node.js programs.
I like to describe node.js as "javascript on the server". It is based on Chrome's JavaScript Engine: a node.js script is being placed in a normal file and then executed by node.js - typically launched from the command line. The programming language is Javascript, node.js is extensible, provides a package manager (npm) and has a vibrant community which drives and develops the function library. Compared to other programming languages, there is a huge difference: Node.js is asynchronous, event driven and non-blocking for I/O bound functions; I'll go into more details during the blog posting.
To connect to the Oracle database, a program uses a driver - in the Java World this is JDBC, in the .NET World this is ODP.NET and so on. For node.js Oracle does not (yet) provide a driver - so we have to look what the community provides: And there is the module node-oracle by Joe Ferner, which is available from GitHub.
The download contains a README which pretty well describes the installation of the node-oracle module into an existing node.js environment. On my linux system this went smooth and without problems. After that we can author the first node.js program which does something with the Oracle database. So, let's select the EMP table and print out the results.
var oracle = require('oracle');

var connectData = {
  hostname: "sccloud034.de.oracle.com",
  port:     1521,
  database: "pdb01.de.oracle.com", 
  user:     "scott",
  password: "tiger"
}

oracle.connect(connectData, function(err, connection) {
  if (err) {
   console.log("Error connecting to db:", err); return;
  }

  connection.execute("select ename, job from emp ", [],  function(err, results) {
    connection.close();
    console.log(results);
  });
});

console.log("done");
As said earlier, node.js is event driven. The oracle.connect function, which opens the connection to the database, is I/O bound (network communication to the Oracle database). So node.js executes it asynchonously. During its execution, node.js does not block and continues working with the next statement. Upon finish, the oracle.connect will execute the callback function which has been provided as a parameter (in the example this is an anonymous function). So, node.js code looks different from Java, C or PHP code. Due to the asynchronous and non-blocking architecture, developers work with callback functions.
Upon execution, the script generates the following output
$ node ora.js
done
[ { ENAME: 'SMITH', JOB: 'CLERK' },
  { ENAME: 'ALLEN', JOB: 'SALESMAN' },
  { ENAME: 'WARD', JOB: 'SALESMAN' },
  { ENAME: 'JONES', JOB: 'MANAGER' },
  { ENAME: 'MARTIN', JOB: 'SALESMAN' },
  { ENAME: 'BLAKE', JOB: 'MANAGER' },
  { ENAME: 'CLARK', JOB: 'MANAGER' },
  { ENAME: 'SCOTT', JOB: 'ANALYST' },
  { ENAME: 'KING', JOB: 'PRESIDENT' },
  { ENAME: 'TURNER', JOB: 'SALESMAN' },
  { ENAME: 'ADAMS', JOB: 'CLERK' },
  { ENAME: 'JAMES', JOB: 'CLERK' },
  { ENAME: 'FORD', JOB: 'ANALYST' },
  { ENAME: 'MILLER', JOB: 'CLERK' } ]
$
console.log("done") is the very last statement within the script - but in the output it seems that it has been executed first. That is due to the non-blocking characteristics of node.js. If you examine the script carefully, you'll notice that at the top-level there are only 2 statements. The first is oracle.connect, the second is console.log. The other lines are callback functions. So the script starts by invoking oracle.connect, the database connection is being established - and while this is being done, node.js continues executing the console.log statement. So we first see "done" and then the actual data from the database.
The next node.js script is supposed to load files into the database. I want to provide a directory - the node.js script loads all text files within that directory into a given database table, DOCUMENT_TABLE.
create table document_table (filename varchar2(200), document clob);
The node.js script is based on the previous one: The nature of the task is a bit more complex - and so is the script. The filesystem calls for reading a directory and reading a file are I/O bound as well - and so they have the same characteristics as the Oracle calls. We need to provide a callback in all the cases. For that reason we get this highly nested code. There are approaches in the node.js community to keep the code more simple, but this is another topic.
var fs = require("fs");
var ora = require("oracle");

var oradb = {
  hostname: "sccloud034.de.oracle.com",
  port:     1521,
  database: "orcl",
  user:     "scott",
  password: "tiger"
}

var dirname = process.argv[2];

ora.connect(oradb, function(err, connection) {
  if (err) {throw err;}
  connection.setAutoCommit(false);
  var fileCount = 0;
  fs.readdir(dirname, function (err, files) {
    if (err) {throw err;}
    files.forEach(function (file) {
      fileCount ++;
      fs.readFile(dirname+"/"+file, 'utf-8', function (err, data) {
        connection.execute(
          "insert into document_table values (:1, :2)",
          [file, data],
          function (err, results) {
            if (err) {throw err;} else {
              console.log('"'+file+'" saved.');
              fileCount--;
              if (fileCount == 0) {
                connection.close();
                console.log("done");
              }
            }
        });
      });
    });
  });
});
The script opens the connection to the Oracle database, walks through the directory, reads each file and performs a SQL INSERT on the Oracle database. So far, so good. Here all the work is being done within one database connection. So on the database side, we utilize one CPU. Some database systems have more available resources, so what to do if we want to have this more scalable ...?
We need more database connections - so we change the order within the script. We first walk though the directory, read each file, then open a database connection in order to perform the INSERT operation. The code now looks like this.
var fs = require("fs");
var ora = require("oracle");

var oradb = {
  hostname: "sccloud034.de.oracle.com",
  port:     1521,
  database: "pdb01.de.oracle.com",
  user:     "scott",
  password: "tiger"
}

var dirname = process.argv[2];

fs.readdir(dirname, function (err, files) {
  if (err) {throw err;}
  files.forEach(function (file) {
    fs.readFile(dirname+"/"+file, 'utf-8', function (err, data) {
      ora.connect(oradb, function(err, connection) {
        if (err) {throw err;}
        connection.execute(
          "insert into document_table values (:1, :2)",
          [file, data],
          function (err, results) {
            if (err) {throw err;}
            console.log('"'+file+'" saved.');
            connection.close();
        });
      });
    });
  });
});
In the "traditional" world, this would work without any problems - perhaps it would be slower, because all these database connections are being opened and closed - but it would work. But the node.js script shows up nothing ... and after a while it stops with an error message (note that the script might run successful on a database with huge resources). What happens here ...?
[oracle@sccloud033 node]$ node loadFiles.js po

/home/oracle/node/loadFiles.js:19
          if (err) {throw err;}
                          ^
Error: ORA-12516: TNS:listener could not find available handler with matching protocol stack
Things become clear as we start monitoring the V$SESSION view ...
SQL> select username, count(*) from v$session group by username

USERNAME                         COUNT(*)
------------------------------ ----------
                                       28
SCOTT                                 154
SYS                                     1

-- after a while ...

SQL> select username, count(*) from v$session group by username

USERNAME                         COUNT(*)
------------------------------ ----------
                                       26
SCOTT                                 366
SYS                                     1

Again, node.js is event-driven. Creating an Oracle connection is a very expensive task - while the first one is running, the script continues in walking through the directory, reading files and spawning new Oracle connections. Before the first file has been stored into the database, node.js has walked completely though the directory and requested a database connection for each file. And as soon as the database limits have been reached, the script stops.
We need a connection pool. But node.js does not provide a connection pool for database connections. Luckily, the database does. Oracle 11.1 introduced the Database Resident Connection Pool which has been developed for exactly these cases. This connection pool (which is not the Shared Server Architecture) is a pool of Dedicated Server Connections. When a client establishes a database connection, no new background process will be created, it will be taken from the pool. And when the client closes the connection, the server process is not being destroyed, it is being placed back into the pool. And as all connection pools, one can configure settings like the minimum or maximum amount of connections or other settings.
The database-resident connection pool is being administered with the PL/SQL package DBMS_CONNECTION_POOL. You need DBA privileges in order to work with it and if you are using Oracle12c and the Multitenant Architecture, you need to work in the Container Database. Now, start the Connection Pool with default settings.
SQL> exec dbms_connection_pool.start_pool;

PL/SQL procedure successfully completed.
We can adjust pool settings ...
begin
  DBMS_CONNECTION_POOL.ALTER_PARAM ('','MINSIZE', '10');
  DBMS_CONNECTION_POOL.ALTER_PARAM ('','MAXSIZE', '20');
end;
/
... and check the existing configuration.
SQL> SELECT * FROM DBA_CPOOL_INFO;

CONNECTION_POOL
--------------------------------------------------------------------------------
STATUS              MINSIZE    MAXSIZE   INCRSIZE SESSION_CACHED_CURSORS
---------------- ---------- ---------- ---------- ----------------------
INACTIVITY_TIMEOUT MAX_THINK_TIME MAX_USE_SESSION MAX_LIFETIME_SESSION
------------------ -------------- --------------- --------------------
 NUM_CBROK MAXCONN_CBROK
---------- -------------
SYS_DEFAULT_CONNECTION_POOL
ACTIVE                   10         20          2                     20
               300            120          500000                86400
         1         40000

To actually use the connection pool in our node.js script, we just need to make a tiny change within the database connection parameters. Note that this works in SQL*Plus, Java or any other Oracle client as well.
var fs = require("fs");
var ora = require("oracle");

var oradb = {
  hostname: "sccloud034.de.oracle.com",
  port:     1521,
  database: "pdb01.de.oracle.com:POOLED",
  user:     "scott",
  password: "tiger"
}

var dirname = process.argv[2];

fs.readdir(dirname, function (err, files) {
  if (err) {throw err;}
  files.forEach(function (file) {
    fs.readFile(dirname+"/"+file, 'utf-8', function (err, data) {
      ora.connect(oradb, function(err, connection) {
        if (err) {throw err;}
        connection.execute(
          "insert into document_table values (:1, :2)",
          [file, data],
          function (err, results) {
            if (err) {throw err;}
            console.log('"'+file+'" saved.');
            connection.close();
        });
      });
    });
  });
});
While the node.js script is running, you might monitor the Oracle View V$CPOOL_STATS ... there you can see, how many server processes are being actually used.
SQL> SELECT num_open_servers, num_busy_servers, num_auth_servers, num_requests
  2  FROM V$CPOOL_STATS

NUM_OPEN_SERVERS NUM_BUSY_SERVERS NUM_AUTH_SERVERS NUM_REQUESTS
---------------- ---------------- ---------------- ------------
              15                9                1          799

The key to scalable node.js scripts working with the Oracle database is a mainly unkown database feature: Database Resident Connection Pool. Every node.js program working on the Oracle database should use it.
I did some further experiments with node.js - but for today this should be enough information. I think, node.js can be nicely used as a scripting language. For instance, converting a folder of XML documents to JSON and uploading this to the database is just a minor change within the above script and will lead to only a few more lines of code ...

1. August 2014

JSON und Oracle12c: 'Development meets Customers' in Frankfurt

This blog posting is about an event in German language and therefore in german only.
Jüngst hatte ich das erste Blog-Posting zur neuen JSON-Unterstützung in 12.1.0.2 veröffentlicht - nun findet die erste Veranstaltung dazu statt. Und was für eine: Am 17. September findet in Frankfurt die halbtägige Veranstaltung Oracle Development meets Customers "JSON und die Oracle Database 12c" statt.

Mit der aktuellsten Version 12.1.0.2 wird die Oracle-Datenbank erstmalig mit nativer JSON-Funktionalität ausgestattet. So ist es nun möglich, JSON-Daten in Oracle-Tabellen zu speichern, und auf diesem Weg mit sich ändernden bzw. flexiblen Schemas - in der Oracle-Datenbank - umzugehen.
Darüber hinaus stehen SQL-Standard-Funktionen (SQL/JSON) bereit, mit denen performante Abfragen auf die gespeicherten JSON-Daten möglich werden. In dieser Veranstaltung haben Sie die einmalige Chance, aktuellste Informationen dazu direkt aus dem Entwicklungsteam zu erhalten. Beda Hammerschmidt, einer der führenden Entwickler der JSON-Datenbank, wird die JSON-Funktionen vorstellen und Tipps & Tricks zu deren Einsatz verraten. Lassen Sie sich diese Gelegenheit nicht entgehen - melden Sie sich gleich an.
  • Eine Einführung: Was ist JSON und wie funktioniert es?
  • JSON in Oracle12c: Funktionsweise
  • SQL/JSON-Standard Funktionen: JSON_QUERY, JSON_TABLE, etc ...
  • JSON-Indizierung und Performance
  • Document/Collection APIs und Query-By-Example für flexible Anwendungsentwicklung
In dieser Veranstaltung haben Sie die einmalige Chance, aktuellste Informationen zur neuen JSON-Unterstützung in Oracle12c - direkt aus dem Entwicklungsteam - zu erhalten. Beda Hammerschmidt, einer der führenden Entwickler der JSON-Datenbank, wird die JSON-Funktionen vorstellen und Tipps & Tricks zu deren Einsatz verraten. Lassen Sie sich diese Gelegenheit nicht entgehen - melden Sie sich gleich an.

23. Juli 2014

Oracle 12.1.0.2 ist da: Ein Einblick in die JSON-Unterstützung

Oracle 12.1.0.2 is here: First look into SQL JSON support
Seit kurzem ist das Patchset 12.1.0.2 der Oracle-Datenbank verfügbar. Und wie schon in der Vergangenheit ist dies mehr als nur ein Patchset: Es führt einige neue Funktionen ein.
  • Oracle In-Memory Database
  • Native Unterstützung für JSON
  • Approximage COUNT DISTINCT
  • :
In diesem Blog-Posting möchte ich euch einen kleinen Einblick in die JSON-Unterstützung geben - denn nun bietet die Oracle-Datenbank eine JSON-Unterstützung direkt auf SQL-Ebene an. Im ersten Release werden SQL-Abfragen auf JSON-Daten unterstützt, das Erzeugen von JSON oder die Unterstützung von JSON in PL/SQL stehen noch aus. Was JSON genau ist, werde ich hier jedoch nicht erläutern, dieser Wikipedia-Artikel sollte einen hinreichenden Einblick geben.
Am Anfang steht wie immer eine Tabelle mit einer Spalte, welche die JSON-Daten aufnehmen soll. Im Gegensatz zur XML-Unterstützung wurde für JSON jedoch kein eigener Datentyp eingeführt: JSON kann als BLOB, CLOB, VARCHAR2 oder NVARCHAR2 abgelegt werden - die im folgenden besprochenen SQL-Funktionen arbeiten mit allen diesen Datentypen. Das Erstellen der Tabelle ist also ganz leicht ...
create table tab_json (
  id           number        generated always as identity,
  name         varchar2(200),
  json_data   clob
)
/ 
Und in diese Tabelle können nun Daten eingefügt werden. Wir wollen mal eine Zeile mit "validem" JSON erzeugen und eine mit "Schrott" ...
insert into tab_json (name, json_data) values (
  '1. JSON', 
  '{person: {firstname: "Carsten", lastname: "Czarski", year: 2014}}'
)
/

insert into tab_json (name, json_data) values (
  '2. kein JSON, sondern XML', 
  '<person><firstname>Carsten</firstname><lastname>Czarski</lastname><year>2014</year></person>'
)
/

insert into tab_json (name, json_data) values (
  '3. wieder JSON',
  '{person: [{firstname: "Carsten", lastname: "Czarski", year: 2014}, {firstname: "John", lastname: "Doe", year: 2015}]}'
)
/

commit
/
Nun wollen wir mit den Daten arbeiten. Bevor man irgendwelche JSON-Funktionen anwendet, muss man natürlich wissen, welche Zeile JSON ist und welche nicht. Dazu dienen die SQL-Erweiterungen IS JSON bzw. IS NOT JSON.
select name from tab_json where json_data is json
/

NAME
---------------------------------------------
1. JSON
3. wieder JSON

2 Zeilen ausgewählt.

select name from tab_json where json_data is not json
/

NAME
--------------------------------------------
2. kein JSON, sondern XML

1 Zeile wurde ausgewählt.
Natürlich lässt sich mit IS JSON auch ein Check-Constraint einrichten - damit lässt sich sicherstellen, dass die Tabellenspalte tatsächlich nur JSON enthalten wird. Das setzen wir im folgenden gleich einmal um. Zuerst löschen wir die Zeile, die kein JSON ist und dann erzeugen wir den Check-Constraint.
SQL> delete from tab_json where name = '2. kein JSON, sondern XML';

1 Zeile wurde gelöscht.

SQL> alter table tab_json add constraint ck_is_json check (json_data is json);

Tabelle wurde geändert.
Von nun an nimmt die Tabelle nur noch JSON entgegen - andere Daten werden mit einer Fehlermeldung abgewiesen.

SQL-Funktionen: JSON_VALUE

Jetzt geht es daran, mit SQL Abfragen auf die JSON-Inhalte durchzuführen. Die erste Funktion ist JSON_VALUE - sie dient dazu, einzelne (skalare) Werte (und nur diese) aus dem JSON zu extrahieren. Kurz gesagt: JSON_VALUE kann verwendet werden, wenn genau ein Wert als Rückgabe erwartet wird.
select json_value(json_data, '$.person.firstname' ) 
from tab_json
where name = '1. JSON'
/

JSON_VALUE(JSON_DATA,'$.PERSON.FIRSTNAME')
--------------------------------------------
Carsten

1 Zeile wurde ausgewählt.
JSON_VALUE nimmt ein JSON-Dokument (oder eine Tabellenspalte mit JSON-Dokumenten) und einen JSON Pfadausdruck (JSON Path Expression) entgegen. Im Pfadausdruck repräsentiert das Dollarzeichen ($) das JSON-Dokument als ganzes. Per Punktnotation kann man nun durch die Hierarchie navigieren. $.person.firstname navigiert also zunächst ins Objekt person und dort zum Attribut firstname. Selektiert man dagegen nur den Knoten person, welcher ja ein (aus mehreren Attributen bestehendes) Objekt ist ...
select json_value(json_data, '$.person' ) 
from tab_json
where name = '1. JSON'
/

JSON_VALUE(JSON_DATA,'$.PERSON')
--------------------------------------------------------------------------------
- SQL NULL -

1 Zeile wurde ausgewählt.
... so erhält man gar nichts, obwohl der Knoten person sehr wohl vorhanden ist. Tatsächlich ist hier ein Fehler aufgetreten, allerdings hat die Datenbank keine Fehlermeldung geliefert. Die JSON-Funktionen können sich im Fehlerfall variabel verhalten - JSON_VALUE liefert im Fehlerfall standardmäßig SQL NULL zurück. Allerdings kann man dieses Verhalten mit der ON ERROR-Klausel ändern.
select json_value(json_data, '$.person' error on error) 
from tab_json
where name = '1. JSON'
/

FEHLER in Zeile 2:
ORA-40456: JSON_VALUE wurde als nicht-skalarer Wert ausgewertet
Mit ERROR ON ERROR wird festgelegt, dass die Datenbank im Fehlerfall auch wirklich eine Fehlermeldung auslösen soll. Die JSON-Funktionen arbeiten standardmäßig mit EMPTY ON ERROR, lösen also keine Fehlermeldung aus, so dass die Anwendung bei JSON-Dokumenten, die nicht zur Query passen, trotzdem weitermachen kann.

SQL-Funktionen: JSON_QUERY

JSON_QUERY wird ganz ähnlich wie JSON_VALUE verwendet, im Gegensatz dazu muss der Pfadausdruck eine gültige JSON-Struktur selektieren, also auf ein JSON-Objekt oder -Array zeigen. Ein skalarer Wert (ein Attributwert) ist hier nicht erlaubt. Den Unterschied erkennt man am besten, wenn man die beiden obigen Abfragen mit JSON_QUERY anstelle von JSON_VALUE ausführt. Damit wir Fehler besser erkennen können, geben wir auch gleich die Klausel ERROR ON ERROR an. Zuerst wird $.person selektiert.
select json_query(json_data, '$.person' PRETTY ERROR ON ERROR ) 
from tab_json
/

JSON_QUERY(JSON_DATA,'$.PERSON'PRETTYERRORONERROR)
----------------------------------------------------

  {
    "firstname" : "Carsten",
    "lastname" : "Czarski",
    "year" : 2014
  }


  [
    {
      "firstname" : "Carsten",
      "lastname" : "Czarski",
      "year" : 2014
    },
    {
      "firstname" : "John",
      "lastname" : "Doe",
      "year" : 2015
    }
  ]

2 Zeilen ausgewählt.
Wie man sieht, extrahiert JSON_QUERY das Objekt person problemlos und gibt es zurück. Im ersten JSON Dokument ist dies ein einzelnes Objekt, im zweiten ein Array mit zwei Elementen. Diese Flexibilität ist bei JSON durchaus normal. Das Schlüsselwort PRETTY sorgt dafür, dass das JSON lesbar formatiert zurückgegeben wird; in produktiven Anwendungen, in denen das JSON von anderen Programmen konsumiert wird, ist das normalerweise nicht nötig - im Entwicklungsprozess kann es aber durchaus hilfreich sein. Die zweite Abfrage, die direkt auf ein skalares Objektattribut abzielt, liefert mit JSON_QUERY ein anderes Ergebnis ...
select json_query(json_data, '$.person.firstname' PRETTY ERROR ON ERROR ) 
from tab_json
/

FEHLER in Zeile 2:
ORA-40480: Ergebnis kann nicht ohne Array-Wrapper zurückgegeben werden
Wie schon gesagt, muss der JSON-Pfadausdruck bei JSON_QUERY auf ein Objekt oder Array zeigen, nicht auf einen skalaren Wert. Man kann dem begegnen, indem die Klausel WITH WRAPPER mitgegeben wird. Das sähe dann so aus:
select json_query(json_data, '$.person.firstname' PRETTY WITH WRAPPER ERROR ON ERROR ) 
from tab_json
/

JSON_QUERY(JSON_DATA,'$.PERSON.FIRSTNAME'PRETTYWITHWRAPPERERRORONERROR)
-------------------------------------------------------------------------------
[
  "Carsten"
]

[
  "Carsten",
  "John"
]

2 Zeilen wurde ausgewählt.
Nun hat die Datenbank die Ergebnisse in eckige Klammern eingefasst - in JSON haben diese eine Bedeutung, denn sie legen fest, dass nun ein JSON-Array vorliegt. Wozu das gut ist? Dieses Ergebnis kann einem JSON-Parser übergeben und von diesem problemlos verarbeitet werden - das wäre mit dem einfachen String nicht machbar gewesen. Die Klausel WITH WRAPPER macht also aus dem Ergebnis von JSON_QUERY ein Array - das gilt auch, wenn JSON_QUERY ohnehin schon ein ganzes Objekt selektiert.
select json_query(json_data, '$.person' PRETTY WITH WRAPPER ERROR ON ERROR ) 
from tab_json
/

JSON_QUERY(JSON_DATA,'$.PERSON'PRETTYWIT
----------------------------------------
[
  {
    "firstname" : "Carsten",
    "lastname" : "Czarski",
    "year" : 2014
  }
]
:

2 Zeilen wurden ausgewählt.

SQL-Funktionen: JSON_TABLE

JSON_TABLE ist schließlich die Power-Funktion für JSON in der Datenbank, denn sie kann ein komplexes JSON-Objekt in eine flache Tabelle übersetzen - ganz ähnlich wie ihr Pendant XMLTABLE das für XML-Dokumente vermag.
select t.name json_name, j.fname, j.lname, j.year
from tab_json t, json_table (
  json_data,
  '$.person'
  columns (
    fname  varchar2(20) path '$.firstname',
    lname  varchar2(20) path '$.lastname',
    year   number       path '$.year'
  )
) j
/ 

JSON_NAME       FNAME                LNAME                      YEAR
--------------- -------------------- -------------------- ----------
1. JSON         Carsten              Czarski                    2014
3. wieder JSON  ?                    ?                    ?

2 Zeilen ausgewählt.
Der Pfadausdruck in JSON_TABLE selektiert ein JSON-Objekt oder -Array; in der COLUMNS-Klausel werden dessen Attribute dann auf Tabellenspalten der Ergebnismenge abgebildet. Obiges Beispiel selektiert den Knoten person, dessen Attribute firstname, lastname und year dann mit COLUMNS als Ergebisspalten deklariert werden. Für das erste JSON, in dem person ein Objekt mit den besagten drei Attributen ist, funktioniert das. Im zweiten JSON-Dokument ist das Objekt person aber ein Array - demnach enthalten die Elemente des Arrays die Attribute, nicht das Array selbst. Also9 bekommen wir für das zweite JSON-Dokument SQL NULL zurück. Die Elemente eines Arrays müssen im JSON Pfadausdruck mit [*] explizit angesprochen werden.
select t.name json_name, j.fname, j.lname, j.year
from tab_json t, json_table (
  json_data,
  '$.person[*]'
  columns (
    fname  varchar2(20) path '$.firstname',
    lname  varchar2(20) path '$.lastname',
    year   number       path '$.year'
  )
) j
/ 

JSON_NAME       FNAME                LNAME                      YEAR
--------------- -------------------- -------------------- ----------
3. wieder JSON  Carsten              Czarski                    2014
3. wieder JSON  John                 Doe                        2015

2 Zeilen ausgewählt.
Schauen wir uns das mit einem anderen JSON-Dokument an. Die Blogger-Plattform, auf der dieser Blog gehostet wird, stellt ebenfalls JSON-Feeds bereit - die letzten 25 Postings dieses Blogs können als JSON abgerufen werden.
Auf diesem JSON wollen wir nun mit JSON_TABLE arbeiten.
/*
 * Achtung: PL/SQL Netzwerk-ACL einrichten für Netzwerkzugriffe
 *          Bei Bedarf Proxy-Server setzen
 */

select 
  datum, 
  titel
from json_table (
  httpuritype('http://sql-plsql-de.blogspot.com/feeds/posts/default?alt=json').getblob() 
  FORMAT JSON,
  '$.feed.entry[*]' 
  columns (
    titel  varchar2(200) path '$."title"."\u0024t"',     -- \u0024 = "$"
    datum  varchar2(50)  path '$."published"."\u0024t"'  -- \u0024 = "$"
  )
)
/

2014-07-11T13:54:54.335+01:00
Monatliches Datenbank-Update für Entwickler

2014-06-04T08:00:12.155+01:00
Dateien per FTP in eine (eigene) Tabelle laden - Teil II

2014-05-15T17:24:04.254+01:00
XML DB Repository anders genutzt: Dateien per FTP in eine (eigene) Tabelle laden

2014-04-23T14:53:09.696+01:00
Tricks mit externen Tabellen: Unix "ps" per SELECT ausführen

2014-04-02T16:00:00.783+01:00
Veranstaltung "Unstrukturierte Daten in Oracle" im Mai 2014

:
Das JSON-Objekt entry ist ein Array - für jedes Posting enthält es einen Eintrag (ein Objekt) mit den Attributen dieses Postings. Also selektiert der JSON-Pathausdruck von JSON_TABLE $.entry. Die Attribute einer entry Instanz, die als Ergebnismenge zurückgeliefert werden sollen, werden in der COLUMNS-Klausel deklariert.
Das JSON muss gar nicht unbedingt in einer Tabelle liegen; es kann auch on-the-fly aus dem Internet geladen oder aus einer externen Tabelle (also dem Dateisystem) gelesen werden - die Möglichkeiten sind vielfältig. Als ersten Einblick in die JSON-Funktionen möchte ich es hierbei belassen - wie man JSON indiziert, soll einem eigenen Blog-Posting vorbehalten bleiben. Viel Spaß auf jeden Fall beim Ausprobieren. Die zur JSON-Unterstützung findet sich übrigens als Kapitel 39 des XML DB Developers' Guide.
The latest database patchset 12.1.0.2 is available. And like other patchsets in the past, this one is not a pure "patchset", it introduces new features as well.
  • Oracle In-Memory Database
  • Native JSON support
  • Approximate COUNT DISTINCT
  • :
  • :
In this blog posting, I'll focus on the native JSON support feature. I will not explain JSON itself, there are plenty of resources about that in the internet (e.g. Wikipedia). We'll start (as always) with a table to store JSON documents. In contradiction to XML, there is no new datatype for JSON. JSON can be stored as VARCHAR2, NVARCHAR2, BLOB or CLOB; the SQL functions will work with all of these. If you already have JSON stored in your database tables, no conversion or migration is required. And really all database clients will support JSON right from the beginning, because there is no new data type.
create table tab_json (
  id           number        generated always as identity,
  name         varchar2(200),
  json_data   clob
)
/ 
We can start inserting rows into that table immediately. The following two commands will insert a JSON instance and another one, which is no valid JSON. As we would habe expected: Both SQL INSERT statements succeed.
insert into tab_json (name, json_data) values (
  '1. JSON', 
  '{person: {firstname: "Carsten", lastname: "Czarski", year: 2014}}'
)
/

insert into tab_json (name, json_data) values (
  '2. no JSON, but XML', 
  '<person><firstname>Carsten</firstname><lastname>Czarski</lastname><year>2014</year></person>'
)
/

insert into tab_json (name, json_data) values (
  '3. JSON again',
  '{person: [{firstname: "Carsten", lastname: "Czarski", year: 2014}, {firstname: "John", lastname: "Doe", year: 2015}]}'
)
/

commit
/
Before we can start working with that data, we need to know, which row contains valid JSON and which does not. For this, Oracle introduces the SQL operators IS JSON and IS NOT JSON.
select name from tab_json where json_data is json
/

NAME
---------------------------------------------
1. JSON
3. JSON again

2 rows selected.

select name from tab_json where json_data is not json
/

NAME
--------------------------------------------
2. no JSON, but XML

1 row selected.
Of course, we can also create a check constraint using the IS JSON operator. This will make sure, that all rows contain valid JSON. First, we'll delete the "non-JSON" row and then we'll create the constraint ...
SQL> delete from tab_json where name = '2. no JSON, but XML';

1 row deleted.

SQL> alter table tab_json add constraint ck_is_json check (json_data is json);

Table altered.
From now on, the table column will accept only valid JSON instances. Otherwise, the check constraing will throw an error message.

SQL functions: JSON_VALUE

Now we'll start executing SQL queries based on the stored JSON data. The first SQL function is JSON_VALUE - it is for querying single (scalar) values. Use JSON_VALUE when you expect a singe scalar value as the result of your query. The following example illustrates this.
select json_value(json_data, '$.person.firstname' ) 
from tab_json
where name = '1. JSON'
/

JSON_VALUE(JSON_DATA,'$.PERSON.FIRSTNAME')
--------------------------------------------
Carsten

1 row selected.
The SQL function takes the JSON column and a JSON Path expression as arguments. Within the Path Expression, the dollar sign represents the JSON document. Using dot-notation, we can walk through the object hierarchy and select attributes. Thus, $.person.firstname navigates to the person object and then to the firstname attribute. If we selected the person node, which is an object and not a scalar value ...
select json_value(json_data, '$.person' ) 
from tab_json
where name = '1. JSON'
/

JSON_VALUE(JSON_DATA,'$.PERSON')
--------------------------------------------------------------------------------
- SQL NULL -

1 row selected.
Although the node $.person exists (it is there, it is an object), we get no result. Remember? JSON_VALUE is for retrieving scalar values only. But why did we not get an error message? Because the JSON functions are variable regarding their error handling. By default, JSON_VALUE returns SQL NULL in case of an error. If we want to have an error message, we need to specify that with the ON ERROR clause.
select json_value(json_data, '$.person' error on error) 
from tab_json
where name = '1. JSON'
/

ERROR at line 2:
ORA-40456: JSON_VALUE evaluated to non-scalar value
ERROR ON ERROR lets the database really throw error messages on case of errors. Alternatives are EMPTY ON ERROR and NULL ON ERROR. Whereas the former returns an empty JSON array, the latter returns SQL NULL. Both are useful when the application needs a valid JSON result in any case (because a JSON parser processes it). When, on the other hand, the state of the application would be unpredictable, you are better off with explicitly throwing an error message using ERROR ON ERROR.

SQL functions: JSON_QUERY

JSON_QUERY is used similar to JSON_VALUE, but in opposition, it is designed to select valid JSON objects only. So the JSON path expression, passed to JSON_QUERY, must target a JSON array or a JSON object. It must not match a scalar value. The difference becomes very clear, when we execute the previous two queries with JSON_QUERY instead of JSON_VALUE. To recognize errors immediately, we'll pass ERROR ON ERROR right from the beginning. First, a query selecting $.person.
select json_query(json_data, '$.person' PRETTY ERROR ON ERROR ) 
from tab_json
/

JSON_QUERY(JSON_DATA,'$.PERSON'PRETTYERRORONERROR)
----------------------------------------------------

  {
    "firstname" : "Carsten",
    "lastname" : "Czarski",
    "year" : 2014
  }


  [
    {
      "firstname" : "Carsten",
      "lastname" : "Czarski",
      "year" : 2014
    },
    {
      "firstname" : "John",
      "lastname" : "Doe",
      "year" : 2015
    }
  ]

2 rows selected.
While JSON_VALUE threw an error, JSON_QUERY executes successfully. The first row is a single JSON object, the second one is a JSON array containing two elements - this variety is usual in JSON environments. The keyword PRETTY lets the database use line breaks and indentation to produce a human-readable output. This is very useful during development, for production, when JSON is only consumed by other programs, it should be turned off. The other query, which selected the yfirstname attribute, returns a different result ...
select json_query(json_data, '$.person.firstname' PRETTY ERROR ON ERROR ) 
from tab_json
/

ERROR at line 1:
ORA-40480: result cannot be returned without array wrapper
As already stated, JSON_QUERY must select a JSON object or array - scalar values are not allowed. But the JSON_QUERY function allows us to circumvent this by using the WITH WRAPPER clause.
select json_query(json_data, '$.person.firstname' PRETTY WITH WRAPPER ERROR ON ERROR ) 
from tab_json
/

JSON_QUERY(JSON_DATA,'$.PERSON.FIRSTNAME'PRETTYWITHWRAPPERERRORONERROR)
-------------------------------------------------------------------------------
[
  "Carsten"
]

[
  "Carsten",
  "John"
]

2 rows selected.
Now, JSON_QUERY wrapped the results into a JSON array. Since an array is a valid JSON structure, no error is thrown. This also applies, when the path expression targets non-scalar values (objects or array) - these are wrapped into an array anyway.
select json_query(json_data, '$.person' PRETTY WITH WRAPPER ERROR ON ERROR ) 
from tab_json
/

JSON_QUERY(JSON_DATA,'$.PERSON'PRETTYWIT
----------------------------------------
[
  {
    "firstname" : "Carsten",
    "lastname" : "Czarski",
    "year" : 2014
  }
]
:

2 rows selected.

SQL functions: JSON_TABLE

JSON_TABLE is the most powerful JSON function. Like its pendant XMLTABLE, it is able to extract multiple values from the JSON, and to project these as the result set of the relational query.
select t.name json_name, j.fname, j.lname, j.year
from tab_json t, json_table (
  json_data,
  '$.person'
  columns (
    fname  varchar2(20) path '$.firstname',
    lname  varchar2(20) path '$.lastname',
    year   number       path '$.year'
  )
) j
/ 

JSON_NAME       FNAME                LNAME                      YEAR
--------------- -------------------- -------------------- ----------
1. JSON         Carsten              Czarski                    2014
3. JSON again   ?                    ?                    ?

2 rows selected.
The path expression passed to JSON_TABLE must select a JSON object or array. Within the COLUMNS clause, the attributes of the selected node are being projected as the SQL result set columns. The above example selects the person node. In the first JSON document, this node contains three attributes: firstname, lastname and year - the COLUMNS clause declares these as the result columns. But in the second JSON document, person is not an object, but an array. So the elements of the person array contain the attributes, not the person array itself. Therefore we get SQL NULL as result for the second JSON document. Array elements must be explicitly selected in the JSON path expression with [*].
select t.name json_name, j.fname, j.lname, j.year
from tab_json t, json_table (
  json_data,
  '$.person[*]'
  columns (
    fname  varchar2(20) path '$.firstname',
    lname  varchar2(20) path '$.lastname',
    year   number       path '$.year'
  )
) j
/ 

JSON_NAME       FNAME                LNAME                      YEAR
--------------- -------------------- -------------------- ----------
3. JSON again   Carsten              Czarski                    2014
3. JSON again   John                 Doe                        2015

2 rows selected.
Lets try JSON_TABLE with another example. The Blogger platform which hosts this blog, also offers a JSON feed. You can retrieve the last 25 postings in JSON format.
/*
 * Achtung: PL/SQL Netzwerk-ACL einrichten für Netzwerkzugriffe
 *          Bei Bedarf Proxy-Server setzen
 */

select 
  datum, 
  titel
from json_table (
  httpuritype('http://sql-plsql-de.blogspot.com/feeds/posts/default?alt=json').getblob() 
  FORMAT JSON,
  '$.feed.entry[*]' 
  columns (
    titel  varchar2(200) path '$."title"."\u0024t"',     -- \u0024 = "$"
    datum  varchar2(50)  path '$."published"."\u0024t"'  -- \u0024 = "$"
  )
)
/

2014-07-11T13:54:54.335+01:00
Monatliches Datenbank-Update für Entwickler

2014-06-04T08:00:12.155+01:00
Dateien per FTP in eine (eigene) Tabelle laden - Teil II

2014-05-15T17:24:04.254+01:00
XML DB Repository anders genutzt: Dateien per FTP in eine (eigene) Tabelle laden

2014-04-23T14:53:09.696+01:00
Tricks mit externen Tabellen: Unix "ps" per SELECT ausführen

2014-04-02T16:00:00.783+01:00
Veranstaltung "Unstrukturierte Daten in Oracle" im Mai 2014

:
The JSON path expression $.entry selects a JSON array - which contains one element for each blog posting. Each element is a JSON objects with attributes containing information about this very blog posting. The COLUMNS clause then projects these out as relational columns. As seen, JSON does not need to be stored within the database - we can also grab JSON instances from the internet or the file system and process these on-the-fly.
This concludes my first blog posting on JSON support in the Oracle database. More are to come. If you like, install your 12.1.0.2 database and try it out. The JSON documentation is part of the XML DB Developers' Guide - Chapter 39.

Beliebte Postings