I started to play around with DBFS (Database Filesystem), a new interesting feature in Oracle 11.2.

There are some excellent guides on how to set it up:
http://www.oracle-base.com/articles/11g/DBFS_11gR2.php
http://blog.ronnyegner-consulting.de/2009/10/08/the-oracle-database-file-system-dbfs/

But both of them had one “not very clean” part in them – compiling FUSE kernel driver from source. Actually this is not necessary and there are precompiled FUSE rpm packages available:
http://dag.wieers.com/rpm/packages/fuse/

After RPM package is installed, you need to add the OS user who will mount the filesystem (for example oracle) to OS group fuse.

usermod -a -G fuse oracle

A small SQL code sample this time, how to read RSS file in SQL. I’m using it to display and aggregate some RSS feeds in APEX application.
All columns are directly read from XML (plus some additinal code to get the time zone correct), except pubdate_local that is pubdate converted to date datatype in local time zone. The first example is reading from a local file localnews.rss under directory DIR1.

SELECT title,
       link,
       description,
       author,
       pubdate,
       CAST (pubdate AT TIME ZONE SESSIONTIMEZONE AS DATE) pubdate_local
  FROM (    SELECT title,
                   link,
                   description,
                   author,
                   TO_TIMESTAMP_TZ (
                      REPLACE(pubdate, 'PDT', 'PST PDT'),
                      CASE
                         WHEN REGEXP_LIKE (pubdate, '[[:digit:]]{4}$')
                         THEN
                            'Dy, dd fmMonth YYYY HH24:MI:SS TZHTZM'
                         ELSE
                            'Dy, dd fmMonth YYYY HH24:MI:SS TZR TZD'
                      END,
                      'NLS_DATE_LANGUAGE = American')
                      pubdate
              FROM XMLTABLE (
                      '/rss/channel/item'
                      PASSING xmltype (BFILENAME ('DIR1', 'localnews.rss'),
                                       NLS_CHARSET_ID ('AL32UTF8'))
                      COLUMNS title VARCHAR2 (250 CHAR) PATH 'title',
                              link VARCHAR2 (250 CHAR) PATH 'link',
                              author VARCHAR2 (250 CHAR) PATH 'author',
                              description CLOB PATH 'description',
                              pubdate VARCHAR2 (50) PATH 'pubDate'));

And the same SQL when reading RSS directly from internet over HTTP (BFile is replaced with HttpUriType).

SELECT title,
       link,
       description,
       author,
       pubdate,
       CAST (pubdate AT TIME ZONE SESSIONTIMEZONE AS DATE) pubdate_local
  FROM (    SELECT title,
                   link,
                   description,
                   author,
                   TO_TIMESTAMP_TZ (
                      REPLACE(pubdate, 'PDT', 'PST PDT'),
                      CASE
                         WHEN REGEXP_LIKE (pubdate, '[[:digit:]]{4}$')
                         THEN
                            'Dy, dd fmMonth YYYY HH24:MI:SS TZHTZM'
                         ELSE
                            'Dy, dd fmMonth YYYY HH24:MI:SS TZR TZD'
                      END,
                      'NLS_DATE_LANGUAGE = American')
                      pubdate
              FROM XMLTABLE (
                      '/rss/channel/item'
                      PASSING HttpUriType('http://feeds.feedburner.com/DougsOracleBlog').getXML()
                      COLUMNS title VARCHAR2 (250 CHAR) PATH 'title',
                              link VARCHAR2 (250 CHAR) PATH 'link',
                              author VARCHAR2 (250 CHAR) PATH 'author',
                              description CLOB PATH 'description',
                              pubdate VARCHAR2 (50) PATH 'pubDate'));

Thanks to Daniel Morgans session today at EMEA Harmony, I now have a personal goal to get the Oracle ACE title 🙂 Need to get working towards it and become more active in the community.