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.

15. Mai 2014

XML DB Repository anders genutzt: Dateien per FTP in eine (eigene) Tabelle laden

Upload files into your table with FTP - just with the database
In diesem Blog Posting widme ich mich mal wieder der XML DB und hier speziell dem XML DB Repository. Wie der eine oder andere weiß, bietet dieses einen eingebauten HTTP-, WebDAV- und FTP-Zugang zur Datenbank an. Heute zeige ich, wie man Dateien mit Hilfe des FTP-Protokollservers direkt in eigene Tabellen laden kann. Dazu ist zwar ein wenig Vorarbeit möglich - die Bordmittel der Datenbank werden jedoch völlig ausreichend sein.

Schritt 1: FTP-Zugang aktivieren

Der FTP-Protokollserver der XML DB ist normalerweise abgeschaltet - zu allererst sollte dieser also eingeschaltet werden - dazu setzt Ihr als DBA (oder als anderer Datenbankuser mit der Rolle XDBADMIN) folgendes Kommando ab.
begin
  dbms_xdb.setftpport(2100);
end;
Anstelle der 2100 könnt Ihr natürlich auch einen anderen Port hernehmen - auf Unix- oder Linux-Systemen sollte man der Einfachheit halber nur über 1024 gehen, denn die Ports unter 1024 würden erfordern, dass der Oracle Listener mit Root-Privilegien läuft. Danach den Listener kontrollieren mit lsnrctl status
[oracle@sccloud033 ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 15-MAY-2014 14:06:02

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
:
Listener Log File         /opt/oracle/diag/tnslsnr/sccloud033/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sccloud033.de.oracle.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sccloud033.de.oracle.com)(PORT=8080))(Presentation=HTTP)(Session=RAW))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sccloud033.de.oracle.com)(PORT=2100))(Presentation=FTP)(Session=RAW))
Services Summary...
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Sollte die rote Zeile nicht vorhanden sein, so sollte folgendes geprüft werden:
  • Der Datenbankparameter DISPATCHERS sollte mindestens diesen Inhalt haben: (PROTOCOL=TCP) (SERVICE=orclXDB)
  • Wenn der Listener nicht auf dem Standardport 1521 läuft, sollte die Listener-Adresse im Datenbankparameter LOCAL_LISTENER konfiguriert werden.
Nach etwaigen Korrekturen sollte ein ALTER SYSTEM REGISTER abgesetzt und der Listener danach nochmals geprüft werden.

Schritt 2: erste Gehversuche mit FTP

Wenn der Protokollserver läuft, lässt sich ein erster Versuch mit FTP starten. Verbindet euch mit einem FTP-Client auf die Datenbank (im folgenden ist ein Kommandozeilen-Client dargestellt), meldet euch mit einem Datenbankuser (bspw. SCOTT/tiger) an und ladet eine Datei in das Verzeichnis /public hoch. Beachtet bei der Auswahl eures FTP-Clients bitte, dass die Oracle XML DB kein passives FTP unterstützt.
D:\>ftp -n
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 scott tiger
331 pass required for SCOTT
230 SCOTT logged in
ftp> cd /public
250 CWD Command successful
ftp> put einedatei.txt
200 PORT Command successful
150 ASCII Data Connection
226 ASCII Transfer Complete
ftp: 125 bytes sent in 0,07Seconds 1,92Kbytes/sec.
ftp> ls
200 PORT Command successful
150 ASCII Data Connection
-rw-r--r--   1 SCOTT    oracle       122 MAY 15 12:11 einedatei.txt
ftp>
Es stellt sich sofort die Frage, wo diese Datei konkret gelandet ist - eine eigene Tabelle haben wir noch gar nicht angelegt. Und tatsächlich speichert die Datenbank alle Verzeichnisse und Dateien in einer eigenen Repository-Tabelle. Diese liegt im Schema XDB und heißt XDB$RESOURCE. Für SQL-Zugriffe gibt es die Views PATH_VIEW und RESOURCE_VIEW. Man könnte nun hergehen und die Datei mit der SQL-Funktion XDBURITYPE wie folgt auslesen ...
SQL> select xdburitype('/public/einedatei.txt').getclob() from dual;
... was aber nicht das ist, was wir wollen. Denn das Ziel ist es, dass eine hochgeladene Datei direkt in eine eigene Tabelle eingefügt wird. Möglich wird dies mit Hilfe der XML DB Repository Events (Dokumentation), die man als Art "Trigger" auf das XML DB Repository verstehen kann.

Schritt 3: Tabelle erstellen und PL/SQL-Code für den Repository Event-Handler schreiben

Das Erstellen der Tabelle, in welche die Dateien geladen werden sollen, ist nichts Besonderes. Baut eine ganz normale Tabelle mit einer Spalte vom Typ BLOB.
create table files_tab(
  id          number(10) primary key,
  file_name   varchar2(200) not null,
  mime_type   varchar2(200),
  datetime    date          not null,
  owner       varchar2(30)  not null,
  content     blob
)
/

create sequence files_seq
/
Nun wird es interessanter: Wir erstellen ein PL/SQL-Paket, welches Handler-Funktionen implementiert. Die Event-Handler sind als Schnittstelle zu verstehen, die ausprogrammiert werden muss: für die verschiedenen möglichen Aktionen auf dem Repository wie Erstellen, Verändern oder Löschen einer Datei (Ressource) sind Handler vorgesehen, die mit eigenem Code versehen werden können. In unserem Beispiel wird der pre-create Handler implementiert. Mit dem PL/SQL-Code wird also festlegt,, was, unmittelbar vor dem Speichern der Ressource (= der hochgeladenen Datei) im Repository, passieren soll. Und wie bei jeder Schnittstelle muss man sich beim Implementieren genau an die Vorgaben halten ...
CREATE OR REPLACE PACKAGE xml_eventhandler AS
  PROCEDURE handlePreCreate (eventObject DBMS_XEVENT.XDBRepositoryEvent);
END xml_eventhandler;
/
sho err

CREATE OR REPLACE PACKAGE BODY xml_eventhandler AS
  PROCEDURE handlePreCreate (eventObject DBMS_XEVENT.XDBRepositoryEvent) AS
    XDBResourceObj DBMS_XDBRESOURCE.XDBResource;
    ResDisplayName VARCHAR2(100);
    ResOwner       VARCHAR2(1000);
    ResMimeType    VARCHAR2(100);

    ContentBLOB    blob;
    ContentBlobCS  number;
    IdCreated      files_tab.id%type;
  BEGIN
    -- get details on uploaded resource: Filename, Mimetype, Content
    XDBResourceObj := DBMS_XEVENT.getResource(eventObject);
    ResDisplayName := DBMS_XDBRESOURCE.getDisplayName(XDBResourceObj);
    ResMimeType    := DBMS_XDBRESOURCE.getContentType(XDBResourceObj);
    ResOwner       := DBMS_XDBRESOURCE.getOwner(XDBResourceObj);
    ContentBLOB    := dbms_xdbresource.getcontentblob(XDBResourceObj, ContentBlobCS);

    -- insert a new ROW into the table
    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;

    -- Set the new table rows' primary Key value as new content
    dbms_xdbresource.setcontent(XDBResourceObj, IdCreated);
    dbms_xdbresource.setcontenttype(XDBResourceObj,'text/plain');
  END handlePreCreate;
end xml_eventhandler;
/
sho err
Der Event-Handler muss als PL/SQL-Package implementiert werden. Wenn der Pre-Create Handler ausprogrammiert werden soll, muss die entsprechende Funktion HANDLEPRECREATE heißen. Auch die Signatur ist fest vorgegeben. Der Inhalt ist jedoch (wie immer bei einem Interface) frei wählbar - dieses Beispiel:
  • ... liest einige Details der hochgeladenen Datei, wie Dateinamen und Mimetype, in Variablen ein
  • ... fügt in die eigene Tabelle eine neue Zeile mit den Details und dem Inhalt der Datei ein
  • ... und damit die Inhalte nicht doppelt gespeichert werden, wird der Dateiinhalt danach durch den (per Sequence) generierten ID-Wert der neuen Tabellenzeile ersetzt. Im XML DB Repository findet sich nach dem Upload also immer noch eine Datei - allerdings steht darin nur noch die ID, unter der die Datei in der eigenen Tabelle gefunden werden kann.

Schritt 4: Einrichten des XML Repository für den Event-Handler

Nun wird das "virtuelle Dateisystem" des XML DB Repository für den Upload in die eigene Tabelle vorbereitet. Zunächst braucht es einige neue Verzeichnisse. /public/uploader/files wird das Verzeichnis sein, für das der Event-Handler eingerichtet wird - alles, was in dieses Verzeichnis hochgeladen wird, soll also in die eigene Tabelle FILES_TAB übernommen werden. /public/uploader/resconfig wird dagegen nur eine XML-Konfigurationsdatei enthalten - die aber nicht gelöscht werden sollte. Das folgende Skript legt die Ordner an.
declare
  b boolean := false;
begin
  b := DBMS_XDB.createFolder('/public/uploader');
  b := DBMS_XDB.createFolder('/public/uploader/files');
  b := DBMS_XDB.createFolder('/public/uploader/resconfig');
end;
/
Nun folgt das Ablegen der XML-Konfigurationsdatei - diese enthält Informationen über den Folder, für die Event-Handler registriert werden sollen, die konkreten Handler selbst und den Names des Packages, welches den Code enthält. Das folgende Skript legt die Datei im genannten Order /public/uploader/resconfig an.
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>FTP Uploader</description>
           <!-- Schema and Name of the PL/SQL Event Handler package -->
           <schema>'||sys_context('userenv','CURRENT_SCHEMA')||'</schema>
           <source>XML_EVENTHANDLER</source>
           <language>PL/SQL</language>
           <!-- List of implemented events -->
           <events>
             <Pre-Create/>
           </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;
/

commit
/
Bis hierhin haben wir eine Tabelle, ein PL/SQL-Paket und einige Ordner und Dateien im XML DB Repository erzeugt. Der Event-Handler ist jedoch noch nicht aktiv - die Aktivierung erfolgt jetzt: Die gerade angelegte Event-Konfigurationsdatei wird mit dem Verzeichnis /public/uploader/files verknüpft. Diesen Schritt müsst Ihr mit einem User machen, der die Rolle XDBADMIN hat - oder als SYS.
BEGIN
  DBMS_RESCONFIG.appendResConfig(
    '/public/uploader/files', 
    '/public/uploader/resconfig/eventhandler.xml',
    DBMS_RESCONFIG.APPEND_RECURSIVE
  );
END;
/

commit
/
Damit sind alle Schritte abgeschlossen. Ladet nun eine Datei per FTP ins Verzeichnis /public/uploader/files hoch.
D:\>ftp -n
ftp> open sccloud033 2100
Connected to sccloud033.de.oracle.com.
:
ftp> put einedatei.txt
200 PORT Command successful
150 ASCII Data Connection
226 ASCII Transfer Complete
ftp: 125 bytes sent in 0,06Seconds 1,95Kbytes/sec.
ftp> dir
200 PORT Command successful
150 ASCII Data Connection
-rw-r--r--   1 TESTIT   oracle         1 MAY 15 12:46 einedatei.txt
226 ASCII Transfer Complete
ftp: 61 bytes received in 0,00Seconds 30,50Kbytes/sec.
ftp>
Obwohl 125 Bytes hochgeladen wurden, enthält die Datei im XML DB Repository nur noch ein Byte - der Inhalt ist nur noch eine "1". Schaut man jedoch in die Tabelle FILES_TAB hinein, so findet sich die Datei hier wieder ...
SQL> select * from files_tab

  ID FILE_NAME  MIME_ DATETIME                  OWNER
---- ---------- ----- ------------------------- ------------------------------
CONTENT
--------------------------------------------------------------------------------
   1 q.sql            15.05.2014 14:46:13       TESTIT
73656C656374202A2066726F6D207478740A776865726520636F6E7461696E7328646F632C202728
4A412057495448494E2028414E5A454947452920414E4420284D4158204D55535445524D414E4E29
Auf diese Weise könnt Ihr nun Datei für Datei in die Datenbank hochladen - und alle Dateien landen in eurer Tabelle FILES_TAB. Die Dateieinträge im XML DB Repository werden nur noch die jeweilige ID der FILES_TAB-Tabellenzeile enthalten. Nun stellt sich allerdings die Frage, was passiert, wenn man (per FTP-Kommando delete) eine Datei löscht oder wenn man per FTP-Upload eine bereits vorhandene Datei ersetzt ...
... nun, datenbankseitig entspricht das einer DELETE- bzw. UPDATE-Aktion auf das XML DB Repository; und folgerichtig gibt es auch Delete- und Update-Handler. Diese haben wir bislang noch nicht ausprogrammiert, also passiert dann auch - nichts. Wenn man eine Datei per FTP-Kommando löscht, verschwindet der Eintrag aus dem XML DB Repository, die Zeile in FILES_TAB bliebt erhalten. Lädt man eine Datei hoch und eine Datei gleichen Namens existiert bereits, so wird der neue Inhalt ins XML DB Repository geschrieben, FILES_TAB bleibt aber unberührt.
Der nächste Schritt wäre also, die Update- und Delete-Handler auszuprogrammieren - das hebe ich mir aber für das nächste Blog-Posting auf. Bis dahin - viel Spaß beim Ausprobieren.
Übrigens: Wenn Ihr "aufräumen" wollt, empfiehlt es sich, zuerst die Event-Konfiguration im XML DB Repository zu löschen (wiederum als User mit XDBADMIN-Rolle oder als DBA).
BEGIN
  DBMS_RESCONFIG.deleteResConfig(
    '/public/uploader/files', 
    '/public/uploader/resconfig/eventhandler.xml',
    DBMS_RESCONFIG.APPEND_RECURSIVE
  );
END;
/
sho err

begin
  dbms_xdb.deleteresource('/public/uploader', dbms_xdb.delete_recursive_force);
end;
/
sho err

commit
/
Danach könnt Ihr Tabellen und PL/SQL-Objekte löschen.
I haven't blogged on Oracle XML DB functionality for some time now - so this posting will be about a special function of the XML DB repository: Repository Events. Some readers know, that Oracle XML DB contains the XML DB repository which provides a "virtual filesystem" and which can be accessed using HTTP, WebDAV or FTP protocols. Using XML DB Repository and Repository events I will show how files can be uploaded to the database - and directly stored into your own table. Only database functionality is needed for this - nothing else.

Step 1: Activate FTP protocol server

The FTP protocol server is disabled by default - so we need to activate it first. Log into the database as DBA or as another user having granted the XDBADMIN role and execute the following PL/SQL call.
begin
  dbms_xdb.setftpport(2100);
end;
Of course, you can also take another TCP/IP port number. On Unix or Linux systems you should take a number greater than 1024, because you would need to run the Oracle listener with root privileges otherwise. Having done this, check your listener with lsnrctl status
[oracle@sccloud033 ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 15-MAY-2014 14:06:02

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
:
Listener Log File         /opt/oracle/diag/tnslsnr/sccloud033/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sccloud033.de.oracle.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sccloud033.de.oracle.com)(PORT=8080))(Presentation=HTTP)(Session=RAW))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sccloud033.de.oracle.com)(PORT=2100))(Presentation=FTP)(Session=RAW))
Services Summary...
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The red line indicates that the FTP protocol server has been started on port 2100. If this line is not present, you might check the following ...
  • Make sure that the DISPATCHERS parameter contains at least (PROTOCOL=TCP) (SERVICE={Oracle SID}XDB)
  • If your Listener does not run on the default port 1521, configure its address in the LOCAL_LISTENER parameter. Check the Oracle documentation (Reference Guide) for details.
After making changes, synchronize the database with the listener using ALTER SYSTEM REGISTER and check the the output of lsnrctl status again.

Step 2: Upload something to the database with FTP

Having the FTP protocol server running, you can do your first experiments. Start an FTP client and connect to the database - the following examples were done with the "standard commandline FTP client" on Windows. Note that Oracle XML DB does not support passive FTP. Log in using a database account (SCOTT/tiger) and upload a file to the /public folder.
D:\>ftp -n
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 scott tiger
331 pass required for SCOTT
230 SCOTT logged in
ftp> cd /public
250 CWD Command successful
ftp> put onefile.txt
200 PORT Command successful
150 ASCII Data Connection
226 ASCII Transfer Complete
ftp: 125 bytes sent in 0,07Seconds 1,92Kbytes/sec.
ftp> ls
200 PORT Command successful
150 ASCII Data Connection
-rw-r--r--   1 SCOTT    oracle       122 MAY 15 12:11 onefile.txt
ftp>
But where has this file been stored? It cannot be a table in the SCOTT schema - we did not create one so far. The answer is, that the "virtual file system" on which the FTP client operates, is completely stored in a database table. This "XML DB repository table" resides in the XDB schema and is named XDB$RESOURCE. For SQL access, Oracle provides two views: RESOURCE_VIEW and PATH_VIEW. File and folder operations can be performed over FTP oder WebDAV protocols or the DBMS_XDB package. We could therefore access the uploaded file from the SQL layer with the following query:
SQL> select xdburitype('/public/einedatei.txt').getclob() from dual;
But this is not what we want. We want to have the file stored in our own table automatically upon upload. XML DB Repository Events (Documentation) allow us to do that. A repository event is like a trigger on the "virtual filesystem" - an action is being fired upon upload, replacement or deletion of a file or folder.

Step 3: Create the file table and the PL/SQL Event Handler package

Creating our own table, which will contain the uploaded files, is nothing special. It is just an ordinary table with a BLOB column. A sequence for the primary key values is also useful.
create table files_tab(
  id          number(10) primary key,
  file_name   varchar2(200) not null,
  mime_type   varchar2(200),
  datetime    date          not null,
  owner       varchar2(30)  not null,
  content     blob
)
/

create sequence files_seq
/
The next step is way more interesting: This package contains the PL/SQL code which will be executed as soon the an XML DB Repository event has been fired. We want to have some action when a file has been uploaded: XML DB Repository provides two events for this: pre-create and post-create. We'll use the pre-create event. Therefore the following PL/SQL package contains a function named HANDLEPRECREATE as the event handler. The function signature is determined by XML DB - the implementation is up to us.
CREATE OR REPLACE PACKAGE xml_eventhandler AS
  PROCEDURE handlePreCreate (eventObject DBMS_XEVENT.XDBRepositoryEvent);
END xml_eventhandler;
/
sho err

CREATE OR REPLACE PACKAGE BODY xml_eventhandler AS
  PROCEDURE handlePreCreate (eventObject DBMS_XEVENT.XDBRepositoryEvent) AS
    XDBResourceObj DBMS_XDBRESOURCE.XDBResource;
    ResDisplayName VARCHAR2(100);
    ResOwner       VARCHAR2(1000);
    ResMimeType    VARCHAR2(100);

    ContentBLOB    blob;
    ContentBlobCS  number;
    IdCreated      files_tab.id%type;
  BEGIN
    -- get details on uploaded resource: Filename, Mimetype, Content
    XDBResourceObj := DBMS_XEVENT.getResource(eventObject);
    ResDisplayName := DBMS_XDBRESOURCE.getDisplayName(XDBResourceObj);
    ResMimeType    := DBMS_XDBRESOURCE.getContentType(XDBResourceObj);
    ResOwner       := DBMS_XDBRESOURCE.getOwner(XDBResourceObj);
    ContentBLOB    := dbms_xdbresource.getcontentblob(XDBResourceObj, ContentBlobCS);

    -- insert a new ROW into the table
    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;

    -- Set the new table rows' primary Key value as new content
    dbms_xdbresource.setcontent(XDBResourceObj, IdCreated);
    dbms_xdbresource.setcontenttype(XDBResourceObj,'text/plain');
  END handlePreCreate;
end xml_eventhandler;
/
sho err
The function implementation ...
  • ... reads information about the uploaded file and its contents into some PL/SQL variables using the DBMS_XDBRESOURCE and DBMS_XEVENT packages.
  • ... inserts a new row with the file contents and metadata in our own FILES_TAB table
  • ... and to prevent duplicate storage of the uploaded file, the file content to be stored in the XML DB repository is being replaced with just the primary key value from the new FILES_TAB row. So after uploading we should have one new row in the FILES_TAB table and (still) a file in the XML DB repository. But while FILES_TAB contains the uploaded contents, the file in the XML DB repository will just contain the numeric primary key value pointing to a row in FILES_TAB.

Step 4: Configuring XML DB Repository

In the last step, we'll configure the virtual filesystem. First, we need a few directories: /public/uploader/files will be the upload directory. Everything uploaded into this folder, is to be placed in FILES_TAB. /public/uploader/resconfig will just contain the XML configuration file for the event handler registration. The following PL/SQL block creates the folders.
declare
  b boolean := false;
begin
  b := DBMS_XDB.createFolder('/public/uploader');
  b := DBMS_XDB.createFolder('/public/uploader/files');
  b := DBMS_XDB.createFolder('/public/uploader/resconfig');
end;
/
Next, we'll create the XML configuration file. This contains the "event definition", that means, the name of the PL/SQL package and the database schema it resides in, as well as the list of actually implemented event handlers. The following PL/SQL block creates this file and places it in the /public/uploader/resconfig folder.
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>FTP Uploader</description>
           <!-- Schema and Name of the PL/SQL Event Handler package -->
           <schema>'||sys_context('userenv','CURRENT_SCHEMA')||'</schema>
           <source>XML_EVENTHANDLER</source>
           <language>PL/SQL</language>
           <!-- List of implemented events -->
           <events>
             <Pre-Create/>
           </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;
/

commit
/
So far, we have created a table to hold the uploaded files, a PL/SQL package with the event handler implementation, two XML DB repository folders and an XML configuration file which allows us to actually register the event handler with the XML DB engine. The following PL/SQL block does the final step: The configuration file (and therefore the event handler implementation) is being registered and linked to to /public/uploader/files folder. Execute this either as DBA or as a user having the XDBADMIN role.
BEGIN
  DBMS_RESCONFIG.appendResConfig(
    '/public/uploader/files', 
    '/public/uploader/resconfig/eventhandler.xml',
    DBMS_RESCONFIG.APPEND_RECURSIVE
  );
END;
/

commit
/
That's it. Now, connect again to the database, with an FTP client, and upload a file to /public/uploader/files.
D:\>ftp -n
ftp> open sccloud033 2100
Connected to sccloud033.de.oracle.com.
:
ftp> put onefile.txt
200 PORT Command successful
150 ASCII Data Connection
226 ASCII Transfer Complete
ftp: 125 bytes sent in 0,06Seconds 1,95Kbytes/sec.
ftp> dir
200 PORT Command successful
150 ASCII Data Connection
-rw-r--r--   1 TESTIT   oracle         1 MAY 15 12:46 onefile.txt
226 ASCII Transfer Complete
ftp: 61 bytes received in 0,00Seconds 30,50Kbytes/sec.
ftp>
125 bytes have been uploaded, but the file within the XML DB repository has only a site of 1 byte. Examine the file contents: you should find just "1" - but the corresponding FILES_TAB row contains exactly what we have uploaded.
SQL> select * from files_tab where id = 1;

  ID FILE_NAME  MIME_ DATETIME                  OWNER
---- ---------- ----- ------------------------- ------------------------------
CONTENT
--------------------------------------------------------------------------------
   1 q.sql            15.05.2014 14:46:13       TESTIT
73656C656374202A2066726F6D207478740A776865726520636F6E7461696E7328646F632C202728
4A412057495448494E2028414E5A454947452920414E4420284D4158204D55535445524D414E4E29
You can now upload as many files as you want - all uploaded files will be placed as new rows into the FILES_TAB table. But this leads directly to the next question: What happens, when an uploaded file replaces an existing file with the same name? What happens when a file is being deleted (with the FTP delete command)?
All contents of the XML DB repository are, as stated above, being stored in a database table. So deleting a file corresponds to a DELETE operation on the repository table and replacing a file corresponds to an UPDATE operation. Consequently, we have XML DB repository events for Delete and Update. And we could extend the PL/SQL package to also contain handlers for Update and Delete Events. At the moment we don't have such an implementation, so XML DB will behave normally. Upon delete, the file will be removed from the XML DB repository, but the row in FILES_TAB will not be touched. The same applies to file replacement: New file contents will be stored in XML DB repository, but the FILES_TAB row won't be affected, since the existing Pre-Create Handler won't execute in this case.
So, the next step would be to implement additional event handlers for update and delete operations. After that, we could think about an implementation for the Render handler which fires when the file is being retrieved from the XML DB repository. But this is topic for another blog posting - for the moment: Have fun trying this out.
BTW: For cleaning up all this, you should first delete the event handler configuration within the XML DB repository. Execute the following (as User with XDBADMIN role or as DBA).
BEGIN
  DBMS_RESCONFIG.deleteResConfig(
    '/public/uploader/files', 
    '/public/uploader/resconfig/eventhandler.xml',
    DBMS_RESCONFIG.APPEND_RECURSIVE
  );
END;
/
sho err

begin
  dbms_xdb.deleteresource('/public/uploader', dbms_xdb.delete_recursive_force);
end;
/
sho err

commit
/
After that, PL/SQL objects, tables and sequences can be dropped safely.

Beliebte Postings