Oracle Apps Notes

A collection of my random notes, primarily on Oracle Apps

Monthly Archives: June 2013

Use the Oracle Database to Fetch Cricket Scores Through PL/SQL

There I was working at my desk, one fine afternoon, while simultaneously trying to keep track of an ongoing match between India and Sri Lanka. Now, I do not particularly like the desktop widgets some cricket websites offer and opening up a browser window after every 10 minutes was a bit too distracting (both for me as well as my colleagues). So I wrote this little script to fetch the scores from the ESPNcricinfo RSS feed which allows me view the latest scores without having to move away from my PL/SQL editor. The added advantage is that to the untrained eye, it would seem like I was hard at work while I secretly check out the scores! Of course, for all of these the database needs to be able to connect to the Internet.

Many PL/SQL editors allow you to refresh a query automatically at fixed intervals, so you can convert this script into a function and then use it in a query. That way, you do not even have to execute the script every time you want to refresh the scores.

So without much ado, here is the code which is pretty self-explanatory and has lots of room for improvement.  Note that the team whose score you want goes into the v_my_team variable, partial names would also work.

set serveroutput on;
DECLARE
 l_http_request   utl_http.req;
 l_http_response  utl_http.resp;
 l_clob           CLOB;
 l_text           VARCHAR2(32767);
 v_count          NUMBER          := 1;
 v_score          VARCHAR2(100);
 v_score_my_team  VARCHAR2(1000)  := NULL;
 v_my_team        VARCHAR2(100)   := 'India'; --'ALL' --The team whose score you want or 'ALL' for all ongoing matches
BEGIN
 -- Initialize the CLOB.
 dbms_lob.createtemporary(l_clob, FALSE);

 -- Make a HTTP request and get the response.
 -- We are using the RSS feed from Cricinfo
 --l_http_request  := utl_http.begin_request('http://rss.cricinfo.com/rss/livescores.xml'); --RSS feed changed 21-Oct-16
 l_http_request  := utl_http.begin_request('http://static.cricinfo.com/rss/livescores.xml');
 l_http_response := utl_http.get_response(l_http_request);

 -- Copy the response into the CLOB.
 BEGIN
   LOOP
     utl_http.read_text(l_http_response, l_text, 32767);
     dbms_lob.writeappend (l_clob, LENGTH(l_text), l_text);
   END LOOP;
 EXCEPTION
   WHEN utl_http.end_of_body THEN
     utl_http.end_response(l_http_response);
 END;

  -- Convert the CLOB to XMLType and extract scores the from the XML
  WHILE XMLType(l_clob).existsNode('/rss/channel/item[' || v_count || ']') = 1
  LOOP
    v_score := XMLType(l_clob).extract('/rss/channel/item[' || v_count || ']/title/text()').getStringVal();
    --Store all scores, if 'ALL' is specified
    IF UPPER(v_my_team) = 'ALL' THEN
      --If this is the first value, do not use a comma
      IF v_score_my_team IS NULL THEN
        v_score_my_team := v_score;
      ELSE
        v_score_my_team := v_score_my_team||', '||v_score;
      END IF;
    ELSE
      -- If the score is for your team, store it in a variable
      -- Providing a value of 'Australia' to the variable v_my_team would fetch scores involving both Australia and Australia A.
      -- We will display all matching scores for such cases.
      IF UPPER(v_score) LIKE '%'||UPPER(v_my_team)||'%' THEN
        --If this is the first value, do not use a comma
        IF v_score_my_team IS NULL THEN
          v_score_my_team := v_score;
        ELSE
          v_score_my_team := v_score_my_team||chr(10)||chr(13)||v_score;
        END IF;
      END IF;
    END IF;

      v_count := v_count + 1;
  END LOOP;
  -- Display comma-separated scores
  dbms_output.put_line(NVL(v_score_my_team, 'Sorry, there are no ongoing matches involving '||INITCAP(v_my_team)));

EXCEPTION
 WHEN OTHERS THEN
   utl_http.end_response(l_http_response);
   -- Release the resources associated with the temporary LOB.
   dbms_lob.freetemporary(l_clob);
   dbms_output.put_line (utl_http.get_detailed_sqlerrm);
   dbms_output.put_line (SQLERRM);
END;

Notes:

  1. The database Access Control List should allow network access for this to work.
  2. In scores involving two innings, for example 129/10 & 13/2, the ampersand is not displayed properly.
  3. The scores can also be fetched directly through the query below provided the XML file fetched is not more than 2000 bytes. This is because the REQUEST function used in the query returns up to the first 2000 bytes of the data retrieved from the given URL.
SELECT * FROM (
SELECT extractvalue(column_value, '/item/title') score
FROM TABLE(XMLSequence(XMLTYPE(utl_http.request('http://rss.cricinfo.com/rss/livescores.xml')).EXTRACT('/rss/channel/item')))
)
Advertisements