Tyler Muth’s Blog

Technology with a focus on Oracle, Application Express and Linux

Producing RSS from PL/SQL

Posted by Tyler Muth on January 22, 2008

First things first. The idea and the bulk of the code for this post are not mine, they are Sean Dillon’s. Sean is a good friend of mine and originally wrote this code for AskTom. You can actually still find Sean’s code here. It’s a very cool idea that he came up with and it still works flawlessly today after at least 3 years.

I needed to incorporate RSS into a project I’m working, so I grabbed Sean’s code. The problem is, it’s based on the AskTom table structure which means it won’t run on your database without immediately re-writing the query. Additionally, the nature of this code relies on a pretty lengthy query to generate the XML. I’ll admit, when I first looked at it, I thought “Wow, this is going to be more complex than I thought.” After looking at it for a little while longer, I realized it was actually very simple. Sean also included support for several versions of RSS, improving the functionality, but again, adding to the complexity.

So, I created an example table and simplified the code as much as possible to make it easier for everyone to understand. The table, “PLSQL_PACKAGES”, stores information about some of the built-in PL/SQL packages I use on a regular basis. The links in this table point back to the online Oracle Documentation.

This block of code is just the DDL for the sample table and the insert statements to populate it:

create table plsql_packages(
    id          varchar2(32),
    title       varchar2(255),
    description varchar2(4000),
    link        varchar2(1000),
    updated_by  varchar2(100),
    updated_on  date)
/

create or replace trigger  biu_plsql_packages before insert or update on plsql_packages
for each row
begin
	if inserting then
		:new.id := sys_guid();
	end if;
        :new.updated_by := nvl(v('APP_USER'),user);
        :new.updated_on := sysdate;
end;
/

insert into plsql_packages(title,description,link)
     values ('DBMS_CRYPTO','DBMS_CRYPTO provides an interface to encrypt and decrypt stored data, and can be used in conjunction with PL/SQL programs running network communications. It provides support for several industry-standard encryption and hashing algorithms, including the Advanced Encryption Standard (AES) encryption algorithm. AES has been approved by the National Institute of Standards and Technology (NIST) to replace the Data Encryption Standard (DES).','http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_crypto.htm#i1005082')
/

insert into plsql_packages(title,description,link)
     values ('DBMS_EPG','The DBMS_EPG package implements the embedded PL/SQL gateway that enables a web browser to invoke a PL/SQL stored procedure through an HTTP listener.','http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_epg.htm#sthref3481')
/

insert into plsql_packages(title,description,link)
     values (' OWA_UTIL','The OWA_UTIL package contains utility subprograms for performing operations such as getting the value of CGI environment variables, printing the data that is returned to the client, and printing the results of a query in an HTML table.','http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/w_util.htm#sthref17019')
/

insert into plsql_packages(title,description,link)
     values ('UTL_MAIL','The UTL_MAIL package is a utility for managing email which includes commonly used email features, such as attachments, CC, BCC, and return receipt.','http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/u_mail.htm#i1001258')
/

insert into plsql_packages(title,description,link)
     values ('DBMS_STATS','With the DBMS_STATS package you can view and modify optimizer statistics gathered for database objects.','http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_stats.htm#sthref9773')
/

insert into plsql_packages(title,description,link)
     values ('DBMS_LDAP_UTL','The DBMS_LDAP_UTL package contains the Oracle Extension utility functions.','http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_ldaputl.htm#i997505')
/

insert into plsql_packages(title,description,link)
     values ('DBMS_FLASHBACK','Using DBMS_FLASHBACK, you can flash back to a version of the database at a specified wall-clock time or a specified system change number (SCN).','http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_flashb.htm#i998870')
/

insert into plsql_packages(title,description,link)
     values ('DBMS_FGA','The DBMS_FGA package provides fine-grained security functions.','http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_fga.htm#i1001938')
/

insert into plsql_packages(title,description,link)
     values ('APEX_UTIL','The APEX_UTIL package provides utilities for getting and setting session state, getting files, checking authorizations for users, resetting different states for users, and also getting and setting preferences for users.','http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/a_hdbutl.htm#sthref40')
/

insert into plsql_packages(title,description,link)
     values ('DBMS_RLS','The DBMS_RLS package contains the fine-grained access control administrative interface, which is used to implement Virtual Private Database (VPD). DBMS_RLS is available with the Enterprise Edition only.','http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_rls.htm#i1000830')
/

This is the important block of code as it creates the RSS procedure. Don’t be intimidated by it though as you only need to modify a few lines. The only lines you need to customize to make it work against your own table are 4-7 and 41-43!
create or replace procedure rss
is
— customizable parameters
l_title varchar2(255) := ‘Oracle PL/SQL Packages’;
l_link varchar2(255) := ‘http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/toc.htm’;
l_description varchar2(255) := ‘This is a feed of changes to PL/SQL Package Documentation’;
l_language varchar2(255) := ‘en-us’;
— end customizable parameters
l_version varchar2(10) := ‘2.0’;
l_clob clob;
l_idx pls_integer := 1;
l_len pls_integer := 255;
l_defrows pls_integer := 10;
l_maxrows pls_integer := 30;
l_desclen pls_integer := 250;
begin
for i in (
select xmlelement( “rss”,
— Begin XML Header Block
xmlattributes( l_version as “version”),
xmlelement( “channel”,
xmlforest( l_title as “title”,
l_link as “link”,
l_description as “description”,
l_language as “language”),
— End XML Header Block
— Begin List of Individual Articles or Items
xmlagg(
xmlelement( “item”,
xmlelement(“title”, x.title),
xmlelement(“link”, x.link),
xmlelement(“description”, x.description),
xmlelement(“pubDate”, to_char(x.updated_on,’Dy, DD Mon RRRR hh24:mi:ss’)),
xmlelement(“guid”, XMLATTRIBUTES(‘false’ as “isPermaLink”),x.id||to_char(x.updated_on,’JHH24MISS’))
)
)
— End List of Individual Articles or Items
)
) as result
from ( — Actual Database Query that populates the list of Items
select id,title,link,description,updated_on
from plsql_packages
where rownum < (l_maxrows+1)) x) loop l_clob := xmltype.extract(i.result,'/').getclobval; exit; end loop; --i --- OUTPUT RESULTS owa_util.mime_header('application/xml', false); owa_util.http_header_close; for i in 1..ceil(dbms_lob.getlength(l_clob)/l_len) loop htp.prn(substr(l_clob,l_idx,l_len)); l_idx := l_idx + l_len; end loop; --i end rss; /[/sourcecode] Note on line 34 the "guid" element. This is an optional element (documented here) that an aggregator can use to uniquely identify the item. I’m concatenating the ID column from the table with Julian date concatenated with hours, minutes, and seconds – to_char(sysdate,‘JHH24MISS’). This means that when you update a row, the date will change causing your aggregator to see a new guid and display a new item for the changed row.The easiest way to test this procedure is using the “OWA Output” tab in SQL Developer:SQL Developer OWA Ouput

If you’re running XE or 11g and you want to call this procedure directly through the APEX DAD, you’ll need to edit the FLOWS_XXXXXX.wwv_flow_epg_include_mod_local function and comment-out the first line as well as your procedure to the IN list. Dietmar Aust has a nice post on this.

Once you have your feed displayed in a reader, try updating one of the rows so the updated_on changes. Now refresh the feed in your feed reader and you should see a new item for row you updated. Here’s a link to the code in action on the beta site of apex.oracle.com (this is an RSS feed, so you might want to test it in a reader such as Thunderbird, NetVibes, or Google Reader).

49 Responses to “Producing RSS from PL/SQL”

  1. John Scott said

    Very nice indeed Tyler!

    John.

  2. Paulo Vale said

    And it works😉

    Thanks

  3. Patrick said

    Good one!
    Patrick

  4. Paulo Vale said

    Hi again Tyler,
    A question: do you know how to secure the generated RSS with user/password?

    Thanks

  5. tylermuth said

    Let me start by saying I’ve never done this, but found a few answers via google. It looks like the most popular form of RSS authentication is via HTTP Authentication, either Basic or Digest. If you’re using Apache+mod_plsql, you should be able to protect a DAD with the .htaccess file. The users could either be a list of users in a password file or better yet in an LDAP directory.

    You could also use a separate DAD that does not supply it’s own password, forcing the user to enter a username / password. This might be easier, but it would require a separate database account for each user…not fun to maintain.

    Anyone else have any thoughts?

    Tyler

  6. Hi Tyler,

    really useful tip and a good example where you just have to change a few lines. Saves a loot of work!🙂

    About the integration as Oracle APEX page. I wouldn’t go this route, see the following posting for the reason.

    http://forums.oracle.com/forums/thread.jspa?messageID=2284576&#2284576

    Patrick

  7. Hi Tyler,

    Very nice post. I’ve been testing it and it works nicely for small posts.

    The moment I’ve longer blog posts I get following error: ora-06502: PL/SQL numeric or value error: character string buffer too small.

    That’s at line: htp.prn(SUBSTR(l_clob, l_idx, l_len));

    I’m not quiet sure yet why this happens as you break it down into smaller pieces.

    Thanks,
    Dimitri

  8. Another option for basic authentication (with mod_plsql) is the use of an owa_custom package which expect one function “authorize” returning a boolean.

    Here is an example of how i implemented this using the free mod_owa plugin for apache:

    function authorize
    return boolean
    as
       l_user_id       varchar2(250);
       l_user_pwd      varchar2(250);
       
       l_isAuthorized    boolean := false;
    begin
       -- prompt the user for login and password
       owa_sec.set_protection_realm('Provide-Login');
          
       l_user_id := lower(owa_sec.get_user_id);
       l_user_pwd := lower(owa_sec.get_password);
      
       l_isAuthorized := your_authentication_function_here( l_user_id, l_user_pwd );
       
       return l_isAuthorized;
    
    end authorize;

    Further i noticed that your are using the build-in “length” on the clob variable.
    It may be better to use dbms_lob.getlength(l_clob); for this!

    Here is a procedure is use very often, when flushing clob content to the owa buffer:

    procedure flush_clob
    (         p_clob               in   clob
    )
    is
    
       l_end_position     number;
       l_offset           number;
       l_amount           number default 32000;
       l_length           number;
       
       l_line varchar2(32767);
    
    begin
    
       l_length := dbms_lob.getlength(p_clob);
       l_offset := 1;
       while l_offset < l_length loop
          -- Check on doctype declaration in case of html output! And resolve_entities
          -- for this particular line.
          l_amount := 32000;
          dbms_lob.read(p_clob, l_amount, l_offset, l_line);
          htp.prn(l_line);
          l_offset := l_offset + l_amount;
       end loop;
    end flush_clob;
    

    Hope this helps, to solve Dimitri’s problem!
    Gerton

  9. Jon Gooding said

    Hi,

    When I run the rss procedure I get the following error; in 10.2.0.3.

    SQL> exec rss
    BEGIN rss; END;

    *
    ERROR at line 1:
    ORA-06502: PL/SQL: numeric or value error
    ORA-06512: at “SYS.OWA_UTIL”, line 354
    ORA-06512: at “SYS.HTP”, line 1362
    ORA-06512: at “SYS.HTP”, line 1437
    ORA-06512: at “SYS.OWA_UTIL”, line 411
    ORA-06512: at “RSS.RSS”, line 50
    ORA-06512: at line 1

    Cheers

    Jon

  10. Tyler Muth said

    Patrick, I agree that it’s not the best idea to call it from APEX, so I removed it.

    Gerton, I’ve incorporated your dbms_lob.getlength suggestion. I also edited your comment to use the sourcecode tag to make it easier to read.

    Dimitri, I hope Gerton’s change fixes your ora-06502.

    Jon, this is probably because you’re calling it from sqlplus. You need to call it from a browser or in SQL Developer after enabling “OWA Output” as shown in the original post.

    Thanks,
    Tyler

  11. charlie said

    thanks for the post patrick!

    my client would like to scrape html from their site and publish as an rss feed. my idea is to fetch the html useing the url fetching abilities of the orace database, parse into a table and serve out as an rss feed. all within oracle!

    anybody have any experience with this or see any problems with such a concept?

    thanks in advance.

  12. Angelos Roumeliotis said

    Hi there!

    It sure sound very good!
    Unfortunately it doesn’t work for me.
    When I try to execute it in toad I get:
    ORA-06502: PL/SQL: numeric or value error
    ORA-06512: at “SYS.OWA_UTIL”, line 354
    ORA-06512: at “SYS.OWA_UTIL”, line 413
    ORA-06512: at “RSS.RSS”, line 50

    and in from a browser I get 404 (even though the wwv_flow_epg_include_mod_local function validates it)

    Any ideas??

    Thank you in advanced for your help.

  13. Tyler Muth said

    I’m not sure if Toad handles owa output. With SQL Developer, you simply have to enable OWA Output. As far as the browser goes, can you try creating a simple “hello world” procedure with htp.p just to make sure everything is configured properly?

  14. Angelos Roumeliotis said

    Thank you for your reply.

    I have several other procedure htp.p-ing things on the browser, so I’m pretty sure that works…

    Any other ideas? Anything is appreciated…

  15. Angelos Roumeliotis said

    Don’t worry about it, fixed!

  16. Peter Daniels said

    Practically a total newb to using Oracle’s XML functionality so this was extremely helpful to find, thank you. I get errors when compiling in 9i, however:

    Line # = 17 Column # = 14 Error Text = PL/SQL: SQL Statement ignored
    Line # = 33 Column # = 97 Error Text = PL/SQL: ORA-00904: “X”.”UPDATED_ON”: invalid identifier
    Line # = 45 Column # = 35 Error Text = PLS-00364: loop index variable ‘I’ use is invalid
    Line # = 45 Column # = 9 Error Text = PL/SQL: Statement ignored

    Should this work in 9i (at which we are, unfortunately, stuck) or no? Thanks very much for any assistance.

  17. Tyler Muth said

    Did you copy and paste it line for line or did you change it a bit? I don’t have a 9i database handy so I can’t really test it there. You are aware that 9i is now in error correction support and the first year of free error correction support ends this June or July, correct?

    Tyler

  18. Peter Daniels said

    I initially edited the db fields & such, but I just tried the straight from copy to clipboard option, same deal. I’ll work at it and see if I can figure it out; and lord yes we’re aware that we’re in the final throes of support, it underlies a larger proprietary catalog that we run & the company is a bit behind on delivering a version which works with 10g much less 11g.

    Many thanks for the reply & again, thanks for the tool, it’s already taught me a thing or three.

  19. Felipe said

    Hi, I want to know where in the Apex(oracle) I need to write the code showed above to me create the RSS file. Please tell me the rights steps to create this.

    Thanks very much

    peace!

  20. @Peter Daniels,

    Which version of 9i are you running? All versions below Oracle 9.2.0.4 (?) does not support xmltype!

    Gerton

  21. Peter Daniels said

    Thanks Gerton, 9.2.0.5, and I verified that it can utilize xmltype which, honestly, was sort of a surprise to me.

  22. When obtaining the following error stack (in SQL plus, TOAD etc.)

    When I try to execute it in toad I get:
    ORA-06502: PL/SQL: numeric or value error
    ORA-06512: at “SYS.OWA_UTIL”, line 354
    ORA-06512: at “SYS.OWA_UTIL”, line 413
    ORA-06512: at “RSS.RSS”, line 50

    is because the owa_util package uses several tables of varchar2’s (aka plsql array’s).
    These arrays are not properly initialized (they are empty!) when using tools other then SQL Developer (with OWA output) or Apache/mod_plsql(DAD)/browser.

    You can mimic this by calling the function : owa.initialize (returning a number) before making any calls to owa_util.

    Needless to say that the output for htp.p or htp.prn can only be seen within SQL Developer or in the browser.

    Gerton

  23. Raj Duraisamy said

    Refer the following AskTom link http://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:232814159006

    to solve the problem
    ORA-06502: PL/SQL: numeric or value error
    ORA-06512: at “SYS.OWA_UTIL”, line 354
    ORA-06512: at “SYS.OWA_UTIL”, line 413
    ORA-06512: at “RSS.RSS”, line 50

    Initialize with the following below code will take care of the above error when running
    exec owa_util.showpage;

    declare
    nm owa.vc_arr;
    vl owa.vc_arr;
    begin
    nm(1) := ‘X’;
    vl(1) := ‘Y’;
    owa.init_cgi_env( nm.count, nm, vl );
    end;
    /

    -raj

  24. Pon said

    Hello,

    Thanks and nice article.

    I am looking for some sample code on how to create a RSS reader using plsql?

    Thanks

  25. cardel said

    Hello,
    I have question about authentication of created rss feed. I would like to use this procedure to create rss feed on oracle portal. I though´t that I will use only basic or some other kind of HTTP authentication for URL of rss feed (procedure accessible with mod_plsql). But portal users are authenticated with oracle single sign-on server. I think, that there is no RSS reader that supports this kind of authentication. RSS readers supports only HTTP basic or HTTP+SSL authentication.

    Do You have some idea how can I offer RSS feed to oracle portal users?

    Thanks

  26. Tyler Muth said

    Cardel,

    You might try Oracle mod_osso. I’ve never used it, but I believe it presents a basic challenge for any application under a protected URL.

    Tyler

  27. cardel said

    I have read that but I think that won´t be usefful for securing RSS feed

    “Selecting the URL should invoke the single sign-on login page
    if mod_osso.conf has been configured properly and mod_osso is
    registered with the single sign-on server.”

    I need create basic http authentication to oracle single sign-on server but no login page. I think that RSS readers can make only HTTP authentication.

  28. Marco said

    For those that are looking to run this in TOAD, the following article explains how to resolve the errors. . .
    ORA-06502: PL/SQL: numeric or value error
    ORA-06512: at “SYS.OWA_UTIL”, line 354
    ORA-06512: at “SYS.OWA_UTIL”, line 413
    ORA-06512: at “RSS.RSS”, line 50

    http://asktoad.com/DWiki/doku.php/howto_debug_plsql_code_that_uses_the_htp_or_htf_packages_to_build_web_applications

  29. Carsten said

    hello,

    i have try this procedure. At the beginning the result was an ORA-06502… how the other people in this thread. After i call the procedure “flush_clob” from gerton the 06502 dont come. In the owa output window from sql developer i see the result of my rss. thats ok.

    But in the browser i become an 404 error. The DAD-attribute request-validation-function is correct integrated from my admin. An other/older procedure for downloading files from an table work correct in the browser.

    Any idea?

  30. vivek said

    I’m new to APEX and trying to implement RSS feeds. But not sure where to write the above code.
    Please help.

  31. vivek said

    Hi,

    I wrote the above code in APEX -> SQL Workshop -> SQL Commands
    but the last step *exec rss;* does not work. It says ORA-00900: invalid SQL statement.

    Please Help

  32. Tyler Muth said

    Vivek,

    The “exec” command does not work in SQL Workshop. Exec is really a shortcut for begin … end, so try this in SQL Workshop:

    begin
      rss;
    end;

    I’m not sure if SQL Workshop will display the output properly though…

  33. vivek said

    Thanks Tyler,

    It worked that way and displayed all the data in the SQL workshop.
    But I’m not sure how an RSS reader will take the update from there.
    And how its integration with any APEX application is done.
    Please help if possible.

    Regards

    Vivek

  34. Diana said

    heyy ur solution worked really good.!

  35. Diana said

    hey again me.!
    i have a problem with de accent marks or tilde in spanish, when in the DB in the title there is a accent mark , this makes a mistake.. and in the mozilla, firefox, etc the rss doesnt show ! :S
    hw can i fix this? .. the accent mark is very important.

  36. Peter G. said

    This is wildly useful to a project I’m working on, not sure if this is still ‘supported’ since it was long ago, but…

    I am having an issue where the output here breaks into new lines seemingly at random, so I may see:

    http://records.library.here.com/record=b2025638

    or even

    The breaks vary if I adjust the numbers for l_idx, l_len, l_maxrows & l_desclen but they still persist. I adapted this to show dbms_output & I get the same results. Any idea what may be causing the bizarre breakage for me and/or how to correct this? 10g R2 here. Thanks for any insights!

    • Peter G. said

      I was able to correct this by using dbms_xslprocessor.clob2file for writing out the file; many thanks for the original code, Tyler, great help!

  37. Peter G. said

    Apologies, I neglected to use ‘pre’ tags. 2 different examples:

    
          Thu, 04 Aug
    2011 12:36:22 EST
    
    
    
  38. Producer Edward Bass…

    Producing RSS from PL/SQL « Tyler Muth’s Blog…

  39. BlogZeal.com for make money online…

    […]Producing RSS from PL/SQL « Tyler Muth’s Blog[…]…

  40. Website said

    Website…

    […]Producing RSS from PL/SQL « Tyler Muth’s Blog[…]…

  41. Wedding Cakes said

    Well written and i am subscribed to you

  42. Delbert said

    An associate of mine just sent me this link and said to check your blog out.
    I’m sure he sent me the wrong link though. The article is good, but what he mentioned isn’t what I’m looking at here. He mentioned something about a shark eating a dude alive, so I don’t understand how I landed here, haha.
    Do you fellas have that post or is he just
    an idiot that sent me to the wrong web site?

  43. Nice post. I learn something new and challenging on websites I stumbleupon
    every day. It will always be exciting to read through articles from other writers and
    practice something from their sites.

  44. Blogueur said

    My name is %name&% and first off I wish to say great post.

    If you don’t mind, I just have one quick question. I was curious to find out how you center yourself and open up your mind before writing. Recently I just can’t get my
    head clear so that I’m ready to work on my ideas. I love writing once I get into the act, but usually I feel as if I end up wasting the first ten to 15 minutes forcing myself to concentrate. Do you have any advice or techniques?

  45. When people buy and sell bulk wholesale nuts, there are some handling
    rules they need to follow to keep their products fresh.
    They have a daily update on their list of suppliers and product.
    With so many proven benefits of oil, every bit of its
    popularity is worth it.

  46. I’ll right away grasp your rss feed as I can’t find
    your email subscription hyperlink or newsletter service.
    Do you’ve any? Kindly permit me recognise in order that I could subscribe. Thanks.

  47. Delbert said

    This is my first time pay a visit at here and i am genuinely pleassant to read
    all at one place.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: