A collection of my random notes, primarily on Oracle Apps
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'); 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||', '||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;
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'))) )
If you are planning an upgrade to R12 and wondering about all the ways it will affect you customizations, the following three tools will make your life easier. You can check all the components which have changed between the releases allowing you to manage your customiszations during the upgrade.
EBS File Comparison Report: Provides detailed information about what files were added, removed, or stubbed (made inactive) during changes between Releases 220.127.116.11 and 12.1.3 of Oracle E-Business Suite. It allows you to drilldown into the files(provided they are text-based) and view the differences between the two versions. It can be downloaded from Metalink Note#1446430.1.
EBS Data Model Comparison Report: Provides the database object definition changes between two EBS releases. This includes information regarding which objects were added, deleted and modified. In case of modified objects, it lists the actual changes made to the objects. This one is more useful than the previous one in the sense that it lists differences between 12.1.3 and all other releases from 11.5.9 onwards. The report can be downloaded from Metalink Note#1290886.1 .
EBS ATG Seed Date Comparison Report: Provides details on the changes between different EBS releases based upon the seed data changes delivered by the product data loader files (.ldt extension) based on EBS ATG loader control (.lct extension) files. This report list differences between 12.1.3 and all other releases from 18.104.22.168 onwards. It can be downloaded from Metalink Note#1327399.1.
The EBS Release Management Team deserves a shout out for this one.