Creating a RSS feed for your APEX application

apexninjas.com - RSS feedCreating a RSS feed for you APEX application or website is actually quite simple, using the owa_util and wpg_docload Oracle packages. A RSS feed (Real Simple Syndication) is a method of exporting your website data into XML format containing a summarized text and metadata (author and date of creation). The RSS feed is usually public and can be accessed by other sites or web services through a URI, like http://localhost:8081/apex/rss.

The method described below can also be used to create a sitemap for your site.

Step 1. Create a procedure that generates the RSS feed

The name of the procedure or whether it is stored inside a package or not, is not important. Usually, you would want the RSS feed to contain the title of your website, a short description and the posts titles with hyperlinks to the actual location of posts. The procedure should look like this (inspired from Rick Murnanes' post):

CREATE OR REPLACE PROCEDURE rss
AS
  v_xml blob;
  v_user        VARCHAR2(100);
BEGIN

  WITH a AS(
    SELECT POST_URL, POST_TITLE
    from  MY_ARTICLES
    order by POST_PUBLISH_DATE desc
  )
  SELECT
    XMLElement("rss",
     XMLAttributes('2.0' as "version"),
     XMLElement("channel",
      XMLElement("title",
              'Mysite.com'),
      XMLElement("link",
               'http://www.oracle.com/global/de/community'),
      XMLElement("description",
               'Description: this is my site!'),
      XMLElement("language", 'en-us'),
      XMLElement("copywrite", 'Copyright '||
           to_char(sysdate, 'YYYY')||' Mysite.com'),
      (
       XMLAgg(
        XMLElement("item",
        XMLElement("pubDate",
            to_char(sysdate – rownum,
                 'DAY, DD MON YYYY HH24:MI:SS')),
        XMLElement(
          "title",
          a.POST_TITLE
          ),
        XMLElement(
          "link",
          a.POST_URL
         )
       )
      )
     )
    )
  ).getblobval(nls_charset_id('AL32UTF8')) into v_xml
  from a;
  owa_util.mime_header('text/xml');
  wpg_docload.download_file(v_xml);
  dbms_lob.freetemporary(v_xml);
END rss;
 

 

Step 2. Grant public web access to the procedure

For this procedure to be accessed publicly from the web, you must:

1. connect to your schema

grant execute on rss to public;

2. connect as sys

create public synonym rss for #your_schema#.#your_package#.rss;

Modify the function:  apex_040000.wwv_flow_epg_include_mod_local, as follows:

create or replace function apex_040000.wwv_flow_epg_include_mod_local(
    procedure_name in varchar2)
return boolean
is
begin
    –return false; — remove this statement when you modify this function
    —
    — Administrator note: the procedure_name input parameter may be in the format:
    —
    —    procedure
    —    schema.procedure
    —    package.procedure
    —    schema.package.procedure
    —
    — If the expected input parameter is a procedure name only, the IN list code shown below
    — can be modified to itemize the expected procedure names. Otherwise you must parse the
    — procedure_name parameter and replace the simple code below with code that will evaluate
    — all of the cases listed above.
    —
    if upper(procedure_name) in (
          'RSS'
          ) then
        return TRUE;
    else
        return FALSE;
    end if;

end wwv_flow_epg_include_mod_local;

This function found in the APEX_040000 schema will enable public web access to the previously created RSS synonym that points to the procedure that generates the RSS feed.

Now,  you can access your RSS feed from http://#your dad base#/rss, like this http://localhost:8081/apex/rss:

apexninjas rss feed

Leave a Reply