Oracle Apps Notes

A collection of my random notes, primarily on Oracle Apps

Category Archives: Non-Oracle

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;
 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
 -- 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(''); --RSS feed changed 21-Oct-16
 l_http_request  := utl_http.begin_request('');
 l_http_response := utl_http.get_response(l_http_request);

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

  -- Convert the CLOB to XMLType and extract scores the from the XML
  WHILE XMLType(l_clob).existsNode('/rss/channel/item[' || v_count || ']') = 1
    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;
        v_score_my_team := v_score_my_team||', '||v_score;
      END IF;
      -- 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;
          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;
  -- Display comma-separated scores
  dbms_output.put_line(NVL(v_score_my_team, 'Sorry, there are no ongoing matches involving '||INITCAP(v_my_team)));

   -- Release the resources associated with the temporary LOB.
   dbms_output.put_line (utl_http.get_detailed_sqlerrm);
   dbms_output.put_line (SQLERRM);


  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 extractvalue(column_value, '/item/title') score
FROM TABLE(XMLSequence(XMLTYPE(utl_http.request('')).EXTRACT('/rss/channel/item')))

What ate up my hard disk space in Windows 7?

I was quite surprised today to see that out of the 188GB allotted to the C: drive in my Windows 7 laptop, only around 16GB was free. This was surprising since the total size of all the files and folders(including hidden ones) turned out to be around 157GB. So where did the ~15GB of hard disk space go? What ate it up?

Cleaning up files through Disk Clean up did not help either. I was rolling up my sleeves to search the Internet for some solutions when I thought of checking out the System Restore option. Turns out that System Restore was using up more than 13GB of space for storing innumerable restore points, most of which I did not need. Thankfully, there was an option to specify the maximum amount of space to be used for storing restore points and setting this to an optimum value meant that I could reclaim around 12GB from being used to store unnecessary restore points.

End result, the C: drive now has 28GB of free space!

Reclaiming reserved space using tune2fs

The mountpoint (/u01) on which I had installed Oracle E-Business Suite was running out of space.   More frustratingly, almost 5GB of precious space was unavailable since it was reserved by the system. This being an installation on my laptop, hard disk space was at a premium.

[root@myapps ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/hda3 6.0G 178M 5.5G 4% /
/dev/hda1 99M 8.7M 85M 10% /boot
none 681M 0 681M 0% /dev/shm
/dev/hda7 2.0G 36M 1.9G 2% /tmp
/dev/hda8 95G 89G 1.3G 99% /u01
/dev/hda2 6.0G 1.9G 3.8G 34% /usr
/dev/hda6 3.0G 93M 2.8G 4% /var
[root@myapps ~]#

The 5GB of space was reserved because ext2 or ext3 filesystem by default allocate 5% of the available blocks for use by the root user. This allows the system to continue running if non-root users fill up the file system and also assists in preventing file fragmentation because the filesystem does not fill up completely.

The good news is that you an use the tune2fs utility to fully reclaim the reserved space. Be careful though not to reclaim all of the reserved space if the file system is used by the root user or for storing log/system files (such as /var and /tmp).

You can use tune2fs with the -l option to view the file system information.

[root@myapps ~]# tune2fs -l /dev/hda8
tune2fs 1.35 (28-Feb-2004)
Filesystem volume name: /u01
Last mounted on: <not available>
Filesystem UUID: e458ad7d-d6c7-481f-8476-e2cd878bd38c
Filesystem magic number: 0xEF53
Filesystem revision #: 1 (dynamic)
Filesystem features: has_journal ext_attr resize_inode dir_index filetype needs_recovery sparse_super large_file
Default mount options: (none)
Filesystem state: clean
Errors behavior: Continue
Filesystem OS type: Linux
Inode count: 12648448
Block count: 25276261
Reserved block count: 1263813
Free blocks: 1589667
Free inodes: 11888886
First block: 0
Block size: 4096
Fragment size: 4096
Reserved GDT blocks: 1024
Blocks per group: 32768
Fragments per group: 32768
Inodes per group: 16384
Inode blocks per group: 512
Filesystem created: Mon May 23 21:44:24 2011
Last mount time: Tue Jan 31 07:12:49 2012
Last write time: Tue Jan 31 07:12:49 2012
Mount count: 44
Maximum mount count: -1
Last checked: Mon May 23 21:44:24 2011
Check interval: 0 (<none>)
Reserved blocks uid: 0 (user root)
Reserved blocks gid: 0 (group root)
First inode: 11
Inode size: 128
Journal inode: 8
Default directory hash: tea
Directory Hash Seed: d32dc62a-1aef-417e-a80e-50f8882bf946
Journal backup: inode blocks
[root@myapps ~]#

And then use the -m option to set the reserved space according to your needs. In this case, I have set the reserved space to 1% of the total space. (Note that the available space is now 5.1GB from the earlier 1.3GB…muchos happiness)

[root@myapps ~]# tune2fs -m 1 /dev/hda8
tune2fs 1.35 (28-Feb-2004)
Setting reserved blocks percentage to 1 (252762 blocks)
[root@myapps ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/hda3 6.0G 178M 5.5G 4% /
/dev/hda1 99M 8.7M 85M 10% /boot
none 681M 0 681M 0% /dev/shm
/dev/hda7 2.0G 36M 1.9G 2% /tmp
/dev/hda8 95G 89G 5.1G 95% /u01
/dev/hda2 6.0G 1.9G 3.8G 34% /usr
/dev/hda6 3.0G 93M 2.8G 4% /var
[root@myapps ~]#