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.

Keine Kommentare:

Beliebte Postings