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.

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.

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.

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.

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

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.