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.

11. Juli 2014

Monatliches Datenbank-Update für Entwickler

... das ist Oracle Developers' Monthly.

Oracle Developers' Monthly ist ein monatlich stattfindendes Webseminar für Anwendungsentwickler, die mit der Oracle-Datenbank arbeiten. Es beginnt immer um 09:00 Uhr und dauert etwa 30 Minuten. In diesem Webseminar, erhalten die Teilnehmer aktuelle Informationen rund um die Oracle-Datenbank: Das umfasst unter anderem jeweils aktuelle Releases, wichtige Patchsets, anstehende Termine, interessante Neuigkeiten aus der Blogosphere und dem Web 2.0 und vieles mehr.
  • What's new? What's hot?
    • Aktuelle Releases, Patchsets, Software-Downloads
    • OTN Spotlight: Diese Seite sollten Sie sich ansehen!
    • Was gibt's Neues in den deutschsprachigen Communities?
    • Web 2.0: Blogs, Twitter, Facebook & Co.
    • Termine der nächsten 6 Wochen
  • Der Ready To Use Tipp
    • New Features oder ...
    • Hidden Secrets aus älteren Versionen oder ...
    • Best Practices
Die Teilnahme ist kostenlos. Der nächste Termin ist am Montag um 09:00 Uhr. Auf der Webseite findet Ihr weitere Informationen und Einwahldaten. Vielleicht hört man sich am Montag ...?
This posting is about an event in german language and therefore in german only.

4. Juni 2014

Dateien per FTP in eine (eigene) Tabelle laden - Teil II

Upload files into your table with FTP - just with the database - Part II
In diesem Blog Posting setze ich das beim letzten Mal begonnene Thema FTP-Uploads in eigene Tabellen fort. Im ersten Teil wurden die Protokollserver und die XML DB Repository Events prinzipiell vorgestellt und es wurde gezeigt, wie man, mit Hilfe des Events Pre-Create einen FTP-Upload "abfängt" und in eine eigene Tabelle umleitet. Anschließend befindet sich die hochgeladene Datei in der eigenen Tabelle, während die Datei im XML DB Repository nur noch deren Primärschlüssel enthält. Heute wird dieses Modell erweitert: Wir wollen noch weitere Events implementieren, so dass eine wirklich vollständige FTP-Schnittstelle für unsere Tabelle FILES_TAB entsteht.
  • Pre-Create wurde im ersten Teil behandelt. Dieses Ereignis wird ausgelöst, wenn eine Datei neu ins XML DB Repository hochgeladen wird.
  • Pre-Update wird ausgelöst, wenn eine hochgeladene Datei eine bereits vorhandene gleichen Namens überschreibt. Logischerweise braucht es in diesem Event-Handler Zugriff sowohl auf die "alte" als auch auf die "neue" Datei.
  • Pre-Delete wird ausgelöst, wenn eine Datei im XML DB Repository gelöscht wird.
  • Render übernimmt, sofern es implementiert ist, die Bereitstellung der Inhalte beim Abrufen der Datei. Hiermit lässt sich also auch etwas völlig anderes, als das im XML DB Repository gespeicherte, bereitstellen.
Zuerst solltet Ihr also die bestehende Konfiguration der Event-Handler löschen. Event-Handler "hängen" an den Ordnern und Dateien des XML DB Repository. Das Neu-Konfigurieren der Event-Handler ist de-facto also ein Update auf besagten Ordnern und Dateien. Damit dieses Update nicht wiederum ein Event auslöst, empfiehlt es sich, die XML DB Events für die Session, mit der man am Event-Handler arbeitet, während dieser Arbeit abzuschalten.
alter session set XML_DB_EVENTS = DISABLE;
Dann löscht Ihr den im ersten Teil angelegten Event-Handler.
BEGIN
  DBMS_RESCONFIG.deleteResConfig(
    '/public/uploader/files', 
    '/public/uploader/resconfig/eventhandler.xml',
    DBMS_RESCONFIG.DELETE_RECURSIVE
  );
  dbms_xdb.deleteresource(
    '/public/uploader/resconfig/eventhandler.xml', 
    dbms_xdb.delete_recursive_force
  );
END;
/
sho err
Dann wird die Package Specification des PL/SQL-Paketes neu eingespielt. Nun enthält das Package vier Prozeduren: handlePreCreate, handlePreUpdate, handlePreDelete und handleRender.
CREATE OR REPLACE PACKAGE xml_eventhandler AS
  PROCEDURE handlePreCreate (eventObject DBMS_XEVENT.XDBRepositoryEvent);
  PROCEDURE handlePreDelete (eventObject DBMS_XEVENT.XDBRepositoryEvent);
  PROCEDURE handlePreUpdate (eventObject DBMS_XEVENT.XDBRepositoryEvent);
  PROCEDURE handleRender    (eventObject DBMS_XEVENT.XDBRepositoryEvent);
END xml_eventhandler;
/
sho err
Und nun der Package Body. Der Code der zusätzlichen Prozeduren ist recht einfach verständlich: Alle drei holen zunächst den Inhalt der angesprochenen Datei im XML DB Repository, also den Primary Key der entsprechenden Zeile in FILES_TAB. handlePreUpdate macht damit dann ein SQL UPDATE, handlePreDelete macht ein SQL DELETE und handleRender liest damit die Spalte CONTENT aus und gibt diesen an den Anwender zurück (SETRENDERSTREAM).
CREATE OR REPLACE PACKAGE BODY xml_eventhandler AS
  /*
   * CREATE (INSERT) Event
   */
  PROCEDURE handlePreCreate (eventObject DBMS_XEVENT.XDBRepositoryEvent) AS
    XDBResourceObj DBMS_XDBRESOURCE.XDBResource;
    ResDisplayName VARCHAR2(100);
    ResMimeType    VARCHAR2(100);

    ContentBLOB    blob;
    ContentBlobCS  number;
    IdCreated      files_tab.id%type;
  BEGIN
    XDBResourceObj := DBMS_XEVENT.getResource(eventObject);
    ResDisplayName := DBMS_XDBRESOURCE.getDisplayName(XDBResourceObj);
    ResMimeType    := DBMS_XDBRESOURCE.getContentType(XDBResourceObj);

    ContentBLOB := dbms_xdbresource.getcontentblob(XDBResourceObj, ContentBlobCS);
    insert into files_tab (
      id, file_name, mime_type, datetime, owner, content
    ) values (
      files_seq.nextval, 
      ResDisplayName, 
      ResMimeType,
      sysdate,
      sys_context('userenv','CURRENT_USER'),
      ContentBLOB
    )
    returning id into IdCreated;

    DBMS_XDBRESOURCE.setContent(XDBResourceObj, IdCreated);
    DBMS_XDBRESOURCE.setContentType(XDBResourceObj,'text/plain');
  END handlePreCreate;

  /*
   * UPDATE Event
   */
  PROCEDURE handlePreUpdate (eventObject DBMS_XEVENT.XDBRepositoryEvent) AS
    OldXDBResourceObj DBMS_XDBRESOURCE.XDBResource;
    NewXDBResourceObj DBMS_XDBRESOURCE.XDBResource;
    ResMimeType       VARCHAR2(100);

    ContentBLOB    blob;
    ContentBlobCS  number;
    IdCreated      files_tab.id%type;
  BEGIN
    NewXDBResourceObj := DBMS_XEVENT.getResource(eventObject);
    ResMimeType       := DBMS_XDBRESOURCE.getContentType(NewXDBResourceObj);
    ContentBLOB       := DBMS_XDBRESOURCE.getContentBLOB(NewXDBResourceObj, ContentBlobCS);

    OldXDBResourceObj := DBMS_XEVENT.getOldResource(eventObject);
    IdCreated := to_number(DBMS_XDBRESOURCE.getContentVARCHAR2(OldXDBResourceObj));

    update files_tab set
      content   = ContentBLOB,
      datetime  = sysdate,
      mime_type = ResMimeType
    where id = IdCreated;

    DBMS_XDBRESOURCE.setContent(NewXDBResourceObj, IdCreated);
    DBMS_XDBRESOURCE.setContentType(NewXDBResourceObj,'text/plain');
  END handlePreUpdate;

  /*
   * DELETE Event
   */
  PROCEDURE handlePreDelete (eventObject DBMS_XEVENT.XDBRepositoryEvent) AS
    XDBResourceObj    DBMS_XDBRESOURCE.XDBResource;
    IdCreated         files_tab.id%type;
  BEGIN
    XDBResourceObj := DBMS_XEVENT.getResource(eventObject);
    IdCreated := to_number(DBMS_XDBRESOURCE.getContentVARCHAR2(XDBResourceObj));

    delete from files_tab where id = IdCreated;
  END handlePreDelete;

  /*
   * RENDER (SELECT) Event
   */
  PROCEDURE handleRender (eventObject DBMS_XEVENT.XDBRepositoryEvent) AS
    XDBResourceObj    DBMS_XDBRESOURCE.XDBResource;

    IdCreated         files_tab.id%type;
    ContentBLOB       blob;
  BEGIN
    XDBResourceObj := DBMS_XEVENT.getResource(eventObject);
    IdCreated := to_number(DBMS_XDBRESOURCE.getContentVARCHAR2(XDBResourceObj));

    select content into ContentBLOB
    from files_tab
    where id = IdCreated;

    DBMS_XEVENT.setRenderStream(eventObject, ContentBLOB);
  END handleRender;
end xml_eventhandler;
/
sho err
Nun könnt die die XML DB Repository Event-Handler neu registrieren ...
DECLARE
  b BOOLEAN := FALSE;
BEGIN
  b := DBMS_XDB.createResource(
    '/public/uploader/resconfig/eventhandler.xml',
    '<ResConfig xmlns="http://xmlns.oracle.com/xdb/XDBResConfig.xsd"
                xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                xsi:schemaLocation="http://xmlns.oracle.com/xdb/XDBResConfig.xsd
                                    http://xmlns.oracle.com/xdb/XDBResConfig.xsd">
       <event-listeners>
         <listener>
           <description>Category application</description>
           <schema>' || sys_context('userenv','CURRENT_SCHEMA') || '</schema>
           <source>XML_EVENTHANDLER</source>
           <language>PL/SQL</language>
           <events>
             <Pre-Create/>
             <Pre-Update/>
             <Pre-Delete/>
             <Render/>
           </events>
         </listener>
       </event-listeners>
       <defaultChildConfig>
         <configuration>
           <path>/public/uploader/resconfig/eventhandler.xml</path>
         </configuration>
       </defaultChildConfig>
     </ResConfig>',
    'http://xmlns.oracle.com/xdb/XDBResConfig.xsd',
    'ResConfig'
  );
END;
/

BEGIN
  DBMS_RESCONFIG.appendResConfig(
    '/public/uploader/files', 
    '/public/uploader/resconfig/eventhandler.xml',
    DBMS_RESCONFIG.APPEND_RECURSIVE
  );
END;
/
sho err

commit
/
Zum Abschluss müssen die XML DB Events wieder aktiviert werden.
alter session set XML_DB_EVENTS = ENABLE;
Und das war's auch schon. Wenn Ihr nun per FTP Dateien hochladet, aktualisiert oder löscht, werdet Ihr feststellen, dass sich die Tabelle FILES_TAB komplett analog dazu verhält. Und wenn Ihr die Dateien wieder herunterladet, wird tatsächlich der Inhalt aus der Tabelle FILES_TAB bereitgestellt. Wir haben eine FTP-Schnittstelle für eine Tabelle implementiert - mit nichts als der Datenbank. Zuerst also einige Dateien (per FTP) hochladen ...
ftp> open sccloud033 2100
Connected to sccloud033.de.oracle.com.
220- sccloud033.de.oracle.com
Unauthorised use of this FTP server is prohibited and may be subject to civil and criminal prosecution.
220 sccloud033.de.oracle.com FTP Server (Oracle XML DB/Oracle Database) ready.
ftp> user testit oracle
331 pass required for TESTIT
230 TESTIT logged in
ftp> cd /public/uploader/files
250 CWD Command successful
ftp> bin
200  Type set to I.
ftp> prom
Interactive mode Off .
ftp> mput *
200 PORT Command successful
150 BIN Data Connection
226 BIN Transfer Complete
ftp: 1935722 bytes sent in 0,22Seconds 8680,37Kbytes/sec.
:
ftp> dir
200 PORT Command successful
150 ASCII Data Connection
-rw-r--r--   1 TESTIT   oracle         1 MAY 26 11:44 Bild01.jpg
-rw-r--r--   1 TESTIT   oracle         1 MAY 26 11:44 Bild02.jpg
-rw-r--r--   1 TESTIT   oracle         1 MAY 26 11:44 Bild03.jpg
-rw-r--r--   1 TESTIT   oracle         1 MAY 26 11:44 Bild04.jpg
-rw-r--r--   1 TESTIT   oracle         1 MAY 26 11:44 Bild05.jpg
-rw-r--r--   1 TESTIT   oracle         1 MAY 26 11:44 Bild06.jpg
... in der Tabelle sieht das dann so aus.
SQL> select file_name, mime_type, dbms_lob.getlength(content) fsize from files_tab;

FILE_NAME       MIME_TYPE                 FSIZE
--------------- -------------------- ----------
Bild01.jpg      image/jpeg              1935722
Bild02.jpg      image/jpeg              3483872
Bild03.jpg      image/jpeg              2300657
Bild04.jpg      image/jpeg              3003526
Bild05.jpg      image/jpeg              2245385
Bild06.jpg      image/jpeg              2350616
Dann eine Datei löschen ...
ftp> del Bild01.jpg
250 DELE Command successful
ftp> dir
200 PORT Command successful
150 ASCII Data Connection
-rw-r--r--   1 TESTIT   oracle         1 MAY 26 11:44 Bild02.jpg
-rw-r--r--   1 TESTIT   oracle         1 MAY 26 11:44 Bild03.jpg
-rw-r--r--   1 TESTIT   oracle         1 MAY 26 11:44 Bild04.jpg
-rw-r--r--   1 TESTIT   oracle         1 MAY 26 11:44 Bild05.jpg
-rw-r--r--   1 TESTIT   oracle         1 MAY 26 11:44 Bild06.jpg
... was sich in der Tabelle ebenfalls sofort wiederfindet.
SQL> select file_name, mime_type, dbms_lob.getlength(content) fsize from files_tab;

FILE_NAME       MIME_TYPE                 FSIZE
--------------- -------------------- ----------
Bild02.jpg      image/jpeg              3483872
Bild03.jpg      image/jpeg              2300657
Bild04.jpg      image/jpeg              3003526
Bild05.jpg      image/jpeg              2245385
Bild06.jpg      image/jpeg              2350616
Viel Spaß damit. Natürlich lässt sich das ganze nun nochmals viel weiter treiben. So wäre es eine Möglichkeit, die Zeile der Tabelle FILES_TAB im DELETE-Handler nicht einfach zu löschen, sondern diese in eine andere (Archiv-)Tabelle zu überführen. Ein Loggings ließe sich sehr einfach programmieren - es gibt kaum Grenzen.
Zum Abschluß möchte ich euch die Dokumentation zu den XML DB Repository Events nicht vorenthalten: Ihr findet Sie in Kapitel 30 des XML DB Developers' Guide.
Here is the second part of the blog posting about FTP'ing files into your own tables with the Oracle Database and the XML DB repository. In the first part is introduced Oracle XML DB protocol servers, the repository and Repository Events. We also implemented a simple example, which redirects uploaded files into a particular table, FILES_TAB. After this, the file within the XML DB repository just contains a primary key value for the FILES_TAB table - the file content is stored in the very row this PK value is pointing to. Today we're going to make this example complete: We want to handle also Change, Delete and Download events - all these should work on the FILES_TAB table instead of the XML DB repository.
  • Pre-Create has been implemented in the first part. This event fires, when a new file is being uploaded to the XML DB repository..
  • Pre-Update fires, when an uploaded file overwrites an existing one with the same name. Consequently, within this event, we need access to both the "old" and "new" file contents.
  • Pre-Delete fires, when a file in the XML DB repository is being deleted.
  • Render comes to action, when file contents are being downloaded from the XML DB repository. The code of this event handler determines the actual file contents transmitted to the client.
Before proceeding, we should delete the existing event handler configuration from the first blog posting. But wait: All event handlers are part of the metadata of a XML DB resource. So changing the event handler configuration changes the resource itself - so it fires -again- an update event. To prevent the firing on unwanted events (and therefore unwanted actions from PL/SQL event handlers), it's advisable to disable XML DB events for the database session you are working with.
alter session set XML_DB_EVENTS = DISABLE;
Now, delete the existing event handlers.
BEGIN
  DBMS_RESCONFIG.deleteResConfig(
    '/public/uploader/files', 
    '/public/uploader/resconfig/eventhandler.xml',
    DBMS_RESCONFIG.DELETE_RECURSIVE
  );
  dbms_xdb.deleteresource(
    '/public/uploader/resconfig/eventhandler.xml', 
    dbms_xdb.delete_recursive_force
  );
END;
/
sho err
Then, replace the existing Package Specification for your event handler package with the following one. This one contains not one, but four handler procedures: handlePreCreate, handlePreUpdate, handlePreDelete und handleRender.
CREATE OR REPLACE PACKAGE xml_eventhandler AS
  PROCEDURE handlePreCreate (eventObject DBMS_XEVENT.XDBRepositoryEvent);
  PROCEDURE handlePreDelete (eventObject DBMS_XEVENT.XDBRepositoryEvent);
  PROCEDURE handlePreUpdate (eventObject DBMS_XEVENT.XDBRepositoryEvent);
  PROCEDURE handleRender    (eventObject DBMS_XEVENT.XDBRepositoryEvent);
END xml_eventhandler;
/
sho err
After that, install the Package Body. The code of the new procedures is rather simple: All of them first look into the contents of the existing repository file (you remember: it contains the primary key value for the row in FILES_TAB). handlePreUpdate then performs a SQL UPDATE action, handlePreDelete does SQL DELETE and handleRender reads the CONTENT column into a BLOB variable and passes this to SETRENDERSTREAM - this leads to the BLOB content being passed to the client.
CREATE OR REPLACE PACKAGE BODY xml_eventhandler AS
  /*
   * CREATE (INSERT) Event
   */
  PROCEDURE handlePreCreate (eventObject DBMS_XEVENT.XDBRepositoryEvent) AS
    XDBResourceObj DBMS_XDBRESOURCE.XDBResource;
    ResDisplayName VARCHAR2(100);
    ResMimeType    VARCHAR2(100);

    ContentBLOB    blob;
    ContentBlobCS  number;
    IdCreated      files_tab.id%type;
  BEGIN
    XDBResourceObj := DBMS_XEVENT.getResource(eventObject);
    ResDisplayName := DBMS_XDBRESOURCE.getDisplayName(XDBResourceObj);
    ResMimeType    := DBMS_XDBRESOURCE.getContentType(XDBResourceObj);

    ContentBLOB := dbms_xdbresource.getcontentblob(XDBResourceObj, ContentBlobCS);
    insert into files_tab (
      id, file_name, mime_type, datetime, owner, content
    ) values (
      files_seq.nextval, 
      ResDisplayName, 
      ResMimeType,
      sysdate,
      sys_context('userenv','CURRENT_USER'),
      ContentBLOB
    )
    returning id into IdCreated;

    DBMS_XDBRESOURCE.setContent(XDBResourceObj, IdCreated);
    DBMS_XDBRESOURCE.setContentType(XDBResourceObj,'text/plain');
  END handlePreCreate;

  /*
   * UPDATE Event
   */
  PROCEDURE handlePreUpdate (eventObject DBMS_XEVENT.XDBRepositoryEvent) AS
    OldXDBResourceObj DBMS_XDBRESOURCE.XDBResource;
    NewXDBResourceObj DBMS_XDBRESOURCE.XDBResource;
    ResMimeType       VARCHAR2(100);

    ContentBLOB    blob;
    ContentBlobCS  number;
    IdCreated      files_tab.id%type;
  BEGIN
    NewXDBResourceObj := DBMS_XEVENT.getResource(eventObject);
    ResMimeType       := DBMS_XDBRESOURCE.getContentType(NewXDBResourceObj);
    ContentBLOB       := DBMS_XDBRESOURCE.getContentBLOB(NewXDBResourceObj, ContentBlobCS);

    OldXDBResourceObj := DBMS_XEVENT.getOldResource(eventObject);
    IdCreated := to_number(DBMS_XDBRESOURCE.getContentVARCHAR2(OldXDBResourceObj));

    update files_tab set
      content   = ContentBLOB,
      datetime  = sysdate,
      mime_type = ResMimeType
    where id = IdCreated;

    DBMS_XDBRESOURCE.setContent(NewXDBResourceObj, IdCreated);
    DBMS_XDBRESOURCE.setContentType(NewXDBResourceObj,'text/plain');
  END handlePreUpdate;

  /*
   * DELETE Event
   */
  PROCEDURE handlePreDelete (eventObject DBMS_XEVENT.XDBRepositoryEvent) AS
    XDBResourceObj    DBMS_XDBRESOURCE.XDBResource;
    IdCreated         files_tab.id%type;
  BEGIN
    XDBResourceObj := DBMS_XEVENT.getResource(eventObject);
    IdCreated := to_number(DBMS_XDBRESOURCE.getContentVARCHAR2(XDBResourceObj));

    delete from files_tab where id = IdCreated;
  END handlePreDelete;

  /*
   * RENDER (SELECT) Event
   */
  PROCEDURE handleRender (eventObject DBMS_XEVENT.XDBRepositoryEvent) AS
    XDBResourceObj    DBMS_XDBRESOURCE.XDBResource;

    IdCreated         files_tab.id%type;
    ContentBLOB       blob;
  BEGIN
    XDBResourceObj := DBMS_XEVENT.getResource(eventObject);
    IdCreated := to_number(DBMS_XDBRESOURCE.getContentVARCHAR2(XDBResourceObj));

    select content into ContentBLOB
    from files_tab
    where id = IdCreated;

    DBMS_XEVENT.setRenderStream(eventObject, ContentBLOB);
  END handleRender;
end xml_eventhandler;
/
sho err
Now re-register the event handlers. Note that the XML document describing these must also be adjusted.
DECLARE
  b BOOLEAN := FALSE;
BEGIN
  b := DBMS_XDB.createResource(
    '/public/uploader/resconfig/eventhandler.xml',
    '<ResConfig xmlns="http://xmlns.oracle.com/xdb/XDBResConfig.xsd"
                xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                xsi:schemaLocation="http://xmlns.oracle.com/xdb/XDBResConfig.xsd
                                    http://xmlns.oracle.com/xdb/XDBResConfig.xsd">
       <event-listeners>
         <listener>
           <description>Category application</description>
           <schema>' || sys_context('userenv','CURRENT_SCHEMA') || '</schema>
           <source>XML_EVENTHANDLER</source>
           <language>PL/SQL</language>
           <events>
             <Pre-Create/>
             <Pre-Update/>
             <Pre-Delete/>
             <Render/>
           </events>
         </listener>
       </event-listeners>
       <defaultChildConfig>
         <configuration>
           <path>/public/uploader/resconfig/eventhandler.xml</path>
         </configuration>
       </defaultChildConfig>
     </ResConfig>',
    'http://xmlns.oracle.com/xdb/XDBResConfig.xsd',
    'ResConfig'
  );
END;
/

BEGIN
  DBMS_RESCONFIG.appendResConfig(
    '/public/uploader/files', 
    '/public/uploader/resconfig/eventhandler.xml',
    DBMS_RESCONFIG.APPEND_RECURSIVE
  );
END;
/
sho err

commit
/
Finally re-enable XML DB events within your database session.
alter session set XML_DB_EVENTS = ENABLE;
And then you are finished. Upon uploading, replacing or deleting files with FTP, wou'll notice that your database performs corresponding actions on your FILES_TAB table. Downloading a file using FTP leads to the contents of FILES_TAB being actually passed to the client. You have a complete FTP interface for your table. Try it out. First, upload some files via FTP.
ftp> open sccloud033 2100
Connected to sccloud033.de.oracle.com.
220- sccloud033.de.oracle.com
Unauthorised use of this FTP server is prohibited and may be subject to civil and criminal prosecution.
220 sccloud033.de.oracle.com FTP Server (Oracle XML DB/Oracle Database) ready.
ftp> user testit oracle
331 pass required for TESTIT
230 TESTIT logged in
ftp> cd /public/uploader/files
250 CWD Command successful
ftp> bin
200  Type set to I.
ftp> prom
Interactive mode Off .
ftp> mput *
200 PORT Command successful
150 BIN Data Connection
226 BIN Transfer Complete
ftp: 1935722 bytes sent in 0,22Seconds 8680,37Kbytes/sec.
:
ftp> dir
200 PORT Command successful
150 ASCII Data Connection
-rw-r--r--   1 TESTIT   oracle         1 MAY 26 11:44 Bild01.jpg
-rw-r--r--   1 TESTIT   oracle         1 MAY 26 11:44 Bild02.jpg
-rw-r--r--   1 TESTIT   oracle         1 MAY 26 11:44 Bild03.jpg
-rw-r--r--   1 TESTIT   oracle         1 MAY 26 11:44 Bild04.jpg
-rw-r--r--   1 TESTIT   oracle         1 MAY 26 11:44 Bild05.jpg
-rw-r--r--   1 TESTIT   oracle         1 MAY 26 11:44 Bild06.jpg
... your table will then look similar to this ...
SQL> select file_name, mime_type, dbms_lob.getlength(content) fsize from files_tab;

FILE_NAME       MIME_TYPE                 FSIZE
--------------- -------------------- ----------
Bild01.jpg      image/jpeg              1935722
Bild02.jpg      image/jpeg              3483872
Bild03.jpg      image/jpeg              2300657
Bild04.jpg      image/jpeg              3003526
Bild05.jpg      image/jpeg              2245385
Bild06.jpg      image/jpeg              2350616
Deleting a file ...
ftp> del Bild01.jpg
250 DELE Command successful
ftp> dir
200 PORT Command successful
150 ASCII Data Connection
-rw-r--r--   1 TESTIT   oracle         1 MAY 26 11:44 Bild02.jpg
-rw-r--r--   1 TESTIT   oracle         1 MAY 26 11:44 Bild03.jpg
-rw-r--r--   1 TESTIT   oracle         1 MAY 26 11:44 Bild04.jpg
-rw-r--r--   1 TESTIT   oracle         1 MAY 26 11:44 Bild05.jpg
-rw-r--r--   1 TESTIT   oracle         1 MAY 26 11:44 Bild06.jpg
... leads to a deletion within the table.
SQL> select file_name, mime_type, dbms_lob.getlength(content) fsize from files_tab;

FILE_NAME       MIME_TYPE                 FSIZE
--------------- -------------------- ----------
Bild02.jpg      image/jpeg              3483872
Bild03.jpg      image/jpeg              2300657
Bild04.jpg      image/jpeg              3003526
Bild05.jpg      image/jpeg              2245385
Bild06.jpg      image/jpeg              2350616
And of course, this simple scenario could be extended. You can easily have a logging facility, you could handle a DELETE event differently; that means: not deleting the FILES_TAB row, but actually copying it to an archive table. And many more ...
And the last word: The documentation on XML DB repository events is contained in chapter 30 of XML DB Developers' Guide.

Beliebte Postings