set pages 80
set lines 132
set long 10000
set define off
set echo on

--conn rss/rss

-- Need one of the following to access RSS feeds on the Internet
call utl_http.set_proxy('www-proxy.us.oracle.com')
--call utl_http.set_proxy(NULL)
/

-- Insert RSS news item nodes (or ATOM entry nodes) into the newsItemsTable
DECLARE
  TYPE news_cursor_type is REF CURSOR;
  news_cur news_cursor_type;
  cursor feeds is
    select feed_uri from rssfeeds;
  sql_stmt VARCHAR2(2000); -- Dynamic SQL statement to execute
  newsItem  XMLType;
BEGIN
  sql_stmt :=
    'select column_value from xmltable (''declare namespace a = "http://purl.org/atom/ns#"; (: :)
                                          declare namespace r1="http://purl.org/rss/1.0/";  (: :)
                                          for $i in . return 
                                              if ($i//item) then $i//item 
                                              else if ($i//r1:item) then $i//r1:item 
                                              else //a:entry'' passing xmlparse (document ' ||
               'httpuritype(:1).getclob())) ';
  DBMS_OUTPUT.put_line(sql_stmt);

  FOR feed_rec in feeds LOOP
      OPEN news_cur FOR sql_stmt USING feed_rec.feed_uri;
      LOOP
         FETCH news_cur into newsItem;
         BEGIN
            insert into newsItemsTable (object_value)
			values (newsItem);
            EXCEPTION WHEN DUP_VAL_ON_INDEX THEN null; 
         END;
         EXIT WHEN news_cur%NOTFOUND;
      END LOOP;
      CLOSE news_cur;
  end loop;
END;
/

commit
/
