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

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.

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.

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.

... 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.

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.

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.

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.

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).

The views expressed on this blog are my own and do not necessarily reflect the views of Oracle Corporation or Oracle Germany. The views and opinions expressed by visitors to this blog are theirs and do not necessarily reflect mine.