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.

23. April 2014

Tricks mit externen Tabellen: Unix "ps" per SELECT ausführen

External table tricks: Execute Unix "ps" command with SELECT
Heute geht es nochmals um die Möglichkeiten, die sich durch die Verwendung von externen Tabellen in der Datenbank ergeben. Mit Version 11.2 wurde ja die Möglichkeit geschaffen, ein "Präprozessor"-Kommando zu hinterlegen, welches ausgeführt wird, bevor die externe Datei tatsächlich als Tabelle ausgelesen wird. Die Standardanwendung hierfür wäre das Unix Utility gunzip - damit können externe Tabellen direkt auf .gz-Dateien erstellt werden; der Präprozessor packt sie unmittelbar vor dem Auslesen aus. So weit, so gut.
Ende 2012 hatte ich ein Blog-Posting veröffentlicht, welches zeigt, wie man mit dem Präprozessor auch eine Datei von einem Remote-System (per SSH) als externe Tabelle in die Datenbank einbinden kann; die Datei muss also gar nicht eigens auf den Datenbankserver kopiert werden.
Heute zeige ich, wie man diese Architektur nutzen kann, um Informationen als externe Tabelle aufzubereiten, die gar nicht als Datei vorliegen. Als Beispiel soll die Unix-Prozessliste dienen ...
$ ps -ef
UID        PID  PPID  C STIME TTY          TIME CMD
root         1     0  0 Apr08 ?        00:01:58 init [3]
root         2     0  0 Apr08 ?        00:00:00 [kthreadd]
root         3     2  0 Apr08 ?        00:00:06 [migration/0]
root         4     2  0 Apr08 ?        00:00:31 [ksoftirqd/0]
root         5     2  0 Apr08 ?        00:00:00 [watchdog/0]
root         6     2  0 Apr08 ?        00:00:05 [migration/1]
:            :     :  : :     :               : :
Es ist nun schon recht naheliegend, dass dieses Kommando der "Präprozessor" sein soll - eine Datei, die vom Präprozessor umgewandelt werden soll, ist hier gar nicht nötig. Nun muss nur noch das Ausgabeformat geparst werden - und das sollte ja einfach sein ... ist es aber nicht ganz - denn das "ps"-Kommando nutzt Leerzeichen, um diese tabellenartige Formatierung zu generieren; das sieht zwar gut aus, ist aber als externe Tabelle nicht einfach zu interpretieren (die Anzahl der Leerzeichen zwischen zwei Feldern ist immer anders - und auch die "Feldpositionen" sind nicht zwingend fix). Besser wäre es, wenn die Felder durch Kommas oder Tab-Zeichen (\t) getrennt wären. Das folgende Kommando macht genau das.
$ ps -ef | awk 'OFS="\t" {$1=$1; print}'
Auf der Unix-Shell sieht die Ausgabe genauso aus. Schaut man sich den Output aber genau an, so erkennt man, dass die Felder nun durch Tabulatoren getrennt werden; und genau das brauchen wir für die externe Tabelle. Legt nun also zwei Verzeichnisse auf dem Datenbankserver an: oradir_exe und oradir_file.
$ mkdir /home/oracle/oradir_exe
$ mkdir /home/oracle/oradir_file
In das Verzeichnis oradir_exe kommt nun eine Datei namens getps mit unten stehendem Inhalt (cat getps). Im wesentlichen ist es das obige Kommando ps - aber nochmals leicht verändert, damit einige Details mehr angezeigt werden. Außerdem werden die Executables ps und awk mit ihrem kompletten Pfad angegeben. Zum Abschluß nicht vergessen, die Datei ausführbar zu machen.
$ cd /home/oracle/oradir_exe
$ cat getps
#!/bin/sh
/bin/ps -eo euser,pid,ppid,c,size,tty,time,cmd | /bin/awk 'OFS="\t" {$1=$1; print}'

$ chmod +x getps
$ ls -l
total 4
-rwxr-xr-x 1 oracle oinstall 92 Apr 23 14:47 getps
Ins Verzeichnis oradir_file kommt eine Datei namens file.txt - diese könnt Ihr mit touch erzeugen; die Inhalte (in dem Falle keine) sind völlig egal.
$ cd /home/oracle/oradir_file
$ touch file.txt
$ ls -l
total 0
-rw-r--r-- 1 oracle oinstall 0 Apr 23 14:34 file.txt
Damit sind die Vorbereitungen außerhalb der Datenbank abgeschlossen. Nun geht es in die Datenbank, um die externe Tabelle zu erzeugen. Der Einfachheit halber mache ich hier alles als SYS - eine Unix-Prozessliste ist sicherlich ohnehin nur für den DBA interessant. Zuerst also die Directory-Objekte erzeugen ...
drop directory oradir_exe
/

drop directory oradir_file
/

create directory oradir_exe as '/home/oracle/oradir_exe'
/

create directory oradir_file as '/home/oracle/oradir_file'
/
Dann die externe Tabelle. Man beachte die Zeile mit der PREPROCESSOR-Klausel. Hier wird eigens das Directory-Objekt ORADIR_EXE verwendet. Die Datei file.txt wird als LOCATION, also als Quelle der externen Tabelle angegeben; eine externe Tabelle wird eben so definiert. De-Facto ist diese Datei aber völlig uninteressant, denn das Executable kann seinen Output auch ohne erzeugen. Man kann auch deutlich den Vorteil erkennen, der sich dadurch ergibt, dass die Felder der Ausgabe nun durch Tabs getrennt werden: die externe Tabelle enthält nun nur noch die Klausel FIELDS TERMINATED BY '\t' .
drop table unix_ps
/

create table unix_ps(
  userid     varchar2(30),
  pid        number,
  ppid       number, 
  cpu_util   number,
  "SIZE"     number,
  tty        varchar2(10),
  "TIME"     varchar2(20),
  cmd        varchar2(500)
)
ORGANIZATION EXTERNAL (
  TYPE oracle_loader
  DEFAULT DIRECTORY oradir_file
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE 
    PREPROCESSOR ORADIR_EXE:'getps'
    nobadfile nologfile
    fields terminated by '\t'(
      userid   char,
      pid      char,
      ppid     char,
      cpu_util char,
      "SIZE"   char,
      tty      char,
      "TIME"   char,
      cmd      char
    )
   )
   LOCATION ('file.txt') 
)
REJECT LIMIT UNLIMITED PARALLEL
/
Und das war's auch schon. Ihr könnt die externe Tabelle nun selektieren ...
select "CMD", "PID", "SIZE", "CPU_UTIL" 
from "UNIX_PS"
where "USERID" = 'oracle' and "CMD" like 'ora_%' 
order by "SIZE" desc;

CMD                         PID       SIZE   CPU_UTIL
-------------------- ---------- ---------- ----------
ora_dbw0_orcl              1803      10072          0
oracleorcl                 2477       5412          0
ora_pmon_orcl              1785       5116          0
ora_mmnl_orcl              1815       5056          0
oracleorcl                29308       4244          0
:                             :          :          :
Nach gleichem Schema können auch andere Informationen für SQL-Abfragen in der Datenbank verfügbar gemacht werden. Viel Spaß beim Ausprobieren.
In this blog posting, I will (again) talk about external tables and how these can be used within the database. Oracle 11.2 introduced the preprocessing capability for external tables - the external table definition is being extended with a preprocessor clause: The executable specified here will be executed, just before the external file is being read. The standard use case for this is the Unix gunzip command - using this as the executable, we can create external tables directly on compressed .gz files without unpacking them first.
In 2012, I published a blog posting which desribes how the preprocessor can be used in order to create an external table for a file residing on a remote server. In this example, the preprocessor grabs the file from the remote site using SSH. Thus, the files for external tables do not need to be placed on the database server itself.
Today I'll show, how the proprocessor feature for external tables can be used to create external tables based on the output of a Unix executable - with no static file at all. As example, I'll use the Unix ps command.
$ ps -ef
UID        PID  PPID  C STIME TTY          TIME CMD
root         1     0  0 Apr08 ?        00:01:58 init [3]
root         2     0  0 Apr08 ?        00:00:00 [kthreadd]
root         3     2  0 Apr08 ?        00:00:06 [migration/0]
root         4     2  0 Apr08 ?        00:00:31 [ksoftirqd/0]
root         5     2  0 Apr08 ?        00:00:00 [watchdog/0]
root         6     2  0 Apr08 ?        00:00:05 [migration/1]
:            :     :  : :     :               : :
It's obvious, that the "ps" command will be the "preprocessor" in this case. We'll just have no file to be preprocessed; the executable will generate all the output itself. The output of the "ps" command looks nicely like a table ... but the formatting is done only with blanks - and this makes it really hard to create an external table definition for. The amount of blanks varies with the output itself and the field positions are variable as well. It would be much easier to have commas or Tab ( \t) characters as field separators - the following Unix sequence does the job for us.
$ ps -ef | awk 'OFS="\t" {$1=$1; print}'
Executed on the Unix shell, we hardly see a difference - but since the fields are not being separated by "tab", our external table definition will be much more easy. Now, let's create the required structures on the operating system side. First, we need to directories: oradir_exe and oradir_file.
$ mkdir /home/oracle/oradir_exe
$ mkdir /home/oracle/oradir_file
In oradir_exe we'll place a file named getps with the content shown below (cat getps). Basically it is the above mentioned ps command, but modified a bit in order to see more information. The used executables ps and awk are furthermore extended to their full path (which is required for the external table preprocessor). Finally, don't forget to make the file executable (chmod +x).
$ cd /home/oracle/oradir_exe
$ cat getps
#!/bin/sh
/bin/ps -eo euser,pid,ppid,c,size,tty,time,cmd | /bin/awk 'OFS="\t" {$1=$1; print}'

$ chmod +x getps
$ ls -l
total 4
-rwxr-xr-x 1 oracle oinstall 92 Apr 23 14:47 getps
In oradir_file we place a file named file.txt - you can create it with the touch command. This file must just exist - its contents are totally irrelevant.
$ cd /home/oracle/oradir_file
$ touch file.txt
$ ls -l
total 0
-rw-r--r-- 1 oracle oinstall 0 Apr 23 14:34 file.txt
This concludes the preparations on the operating system side. The next steps are being executed within the database. For simplicily I have done all steps as SYS. First, we'll create the Oracle directory objects corresponding to the Unix folders.
drop directory oradir_exe
/

drop directory oradir_file
/

create directory oradir_exe as '/home/oracle/oradir_exe'
/

create directory oradir_file as '/home/oracle/oradir_file'
/
Then the external table itself is being created. Note the PREPROCESSOR clause. It references the getps script we created before and the directory object it resides in. The other directory object and the file file.txt are referenced in the LOCATION clause, since an external table requires a file to read from. But in our case, the preprocessor does all the work. We can also see the benefit we have gained, by changing the field separators to tabs: we can just declare the FIELDS to be TERMINATED BY '\t' . The plain ps -ef output would be much more difficult to parse.
drop table unix_ps
/

create table unix_ps(
  userid     varchar2(30),
  pid        number,
  ppid       number, 
  cpu_util   number,
  "SIZE"     number,
  tty        varchar2(10),
  "TIME"     varchar2(20),
  cmd        varchar2(500)
)
ORGANIZATION EXTERNAL (
  TYPE oracle_loader
  DEFAULT DIRECTORY oradir_file
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE 
    PREPROCESSOR ORADIR_EXE:'getps'
    nobadfile nologfile
    fields terminated by '\t'(
      userid   char,
      pid      char,
      ppid     char,
      cpu_util char,
      "SIZE"   char,
      tty      char,
      "TIME"   char,
      cmd      char
    )
   )
   LOCATION ('file.txt') 
)
REJECT LIMIT UNLIMITED PARALLEL
/
And that's it - the external table can be used (as always) by selecting it ...
select "CMD", "PID", "SIZE", "CPU_UTIL" 
from "UNIX_PS"
where "USERID" = 'oracle' and "CMD" like 'ora_%' 
order by "SIZE" desc;

CMD                         PID       SIZE   CPU_UTIL
-------------------- ---------- ---------- ----------
ora_dbw0_orcl              1803      10072          0
oracleorcl                 2477       5412          0
ora_pmon_orcl              1785       5116          0
ora_mmnl_orcl              1815       5056          0
oracleorcl                29308       4244          0
:                             :          :          :
And with the same approach, much more operating system information can be made available to SQL queries. Have fun trying it out.

2. April 2014

Veranstaltung "Unstrukturierte Daten in Oracle" im Mai 2014

Im Mai 2014 führen meine Kollegin Ulrike Schwinn und ich eine Veranstaltung (nicht nur) für SQL und PL/SQL Entwickler statt. Diesmal geht es um unstrukturierte Daten in der Oracle-Datenbank - das bedeutet, es werden Large Objects (BLOB, CLOB), XML und Oracle TEXT betrachtet - alles inkl. Oracle12c Update. 

Vielleicht sieht man sich ...? Wir freuen uns drauf.


Mai 2014: SQL and beyond

Unstrukturierte Daten in Oracle12c


Unstrukturierte Daten wie Bilder, Dokumente und Dateien sind neben den relationalen Tabellendaten normaler Bestandteil nahezu jeder Anwendung. Meist werden diese der Einfachheit halber ins Dateisystem gelegt, obwohl die Speicherung in der Datenbank viele Vorteile mit sich bringt.

Erfahren Sie auf diesem Oracle Database Day, welche Möglichkeiten Ihnen die neue Datenbankversion 12c zur Speicherung von und zum Umgang mit unstrukturierten Daten bietet. Dabei wird auf grundsätzliche Dinge wie das Large-Object-Management ebenso eingegangen wie auf die konkrete Nutzung der Daten durch XML-Funktionen, Volltextindizierung und mehr ...

Die Teilnahme an der Veranstaltung ist kostenlos. Melden Sie sich am besten gleich an.


Termine

  • 06.05.2014:
    Oracle Hauptverwaltung München
    Riesstr. 25
    D-80992 München (Anfahrt)

    [Anmeldung]
  • 07.05.2014:
    Oracle Niederlassung Frankfurt
    Robert-Bosch-Str. 5
    D-63303 Dreieich (Anfahrt)

    [Anmeldung]
  • 08.05.2014:
    Oracle Niederlassung in Hamburg
    Kühnehöfe 5
    D-22761 Hamburg (Anfahrt)

    [Anmeldung]

Agenda


11:30Registrierung & Kaffee
12:00Beginn der Veranstaltung
Unstrukturierte Daten in Oracle12c speichern
LOB-Management, Kompression, Performanceaspekte, ...
Mittagspause
Umgang mit XML in der Oracle-Datenbank
XMLTYPE in Oracle12c, Performance, XML-Standards (XQuery Fulltext), ...
Oracle TEXT: Neues in Oracle12c
Near Realtime Index, Automatische Spracherkennung, neue Query Operatoren, ...
16:00Fragen/Diskussion & Ende der Veranstaltung
This blog posting is about an event in Germany in german language ... and therefore in german only.

Beliebte Postings