Monthly Archives: May 2006

Oracle Derived Columns In SELECT Statements

Database, Programming

It seems that using a derived column in a SELECT statement, without a table alias can cause problems.

The following base statement executes error free:

  1. SELECT *
  2. FROM MyTable

This statement errors with ORA-00936: missing expression:

  1. SELECT TO_DATE(B, 'DD-MON-YYYY') - TO_DATE(A, 'DD-MON-YYYY'), *
  2. FROM MyTable

This slightly modified statement will execute as expected:

  1. SELECT TO_DATE(B, 'DD-MON-YYYY') - TO_DATE(a, 'DD-MON-YYYY'), mt.*
  2. FROM MyTable mt

Initially, the idea of changing the order of the fields came to mind; it didn’t help. After adding in the alias and checking with a colleague – it worked. I don’t know why the derived column causes problems, however I thought it interesting none the less.

Transmission Restored

Blogging, Websites

As you might be aware, my site has been offline for the last day or so due to bandwidth restrictions. Since March this year, something has happened and the traffic per day to my site has increased dramatically.

I contacted the great people at AussieHQ who host this site and they were gracious enough to enable my account for the remainder of the month! This is the sort of sensible management that earns a web host a really good name in the industry in my opinion – so a huge thank you to the AussieHQ crew.

Dealazon, The Cheap Amazon

Websites

Last night I stumbled across Dealazon, an online shop offering all sorts of products. Dealazon is an Amazon clone, the difference is that they aren’t trying to imitate Amazon, Dealazon are actually selling Amazon products! Using Dealazon gives you an incredibly convenient way of browsing cheap products from Amazon; in fact they only show the deals.

I didn’t go looking too much, however I assume that Pete Freitag has implemented it using the Amazon web services, Coldfusion and stores the results of the requests locally for performance. Great idea in my opinion and the site is nice and fast too.

Oracle COALESCE Function

Database, Programming

Oracle implements many convenient functions to handle NULL values in the database, one of which is the COALESCE function. The Oracle COALESCE function accepts a varying length list of arguments and returns the first non-NULL value in the list. If all arguments in the list evaluate to a NULL value, then the COALESCE function will return a NULL value.

The standard format for the COALESCE function is:

  1. COALESCE(arg1, arg2, ..., argN)

There are many practical uses for the Oracle COALESCE function, two common actions include:

  1. Passing in a list of common items, which may or may not have data in them in a hope you’ll get back something which you can use.
  2. Generating a default value for a field which contains NULL values.

As a simple, yet practical example of how to use the COALESCE function; following demonstrates how to substitute in a default value in a SELECT statement when there are NULL values present:

  1. SELECT COALESCE(ADDRESS2, 'EMPTY') FROM ADDRESSES

Since there is always more than one way to achieve the same outcome, the following two statements will return the same results as the previous example:

  1. SELECT DECODE(ADDRESS2, NULL, 'EMPTY', ADDRESS2) FROM ADDRESSES
  2. SELECT CASE WHEN ADDRESS2 IS NULL THEN 'EMPTY' ELSE ADDRESS2 END FROM ADDRESSES

The next time you’re dealing with NULL values and you’d prefer a nicer looking result; consider giving the COALESCE function a try.

The Cost Of Popularity

Blogging

Bandwidth usage during April 2006 for http://www.lattimore.id.auHalf way through last month, I upgraded the version of WordPress that powers this site. During the upgrade process, I neglected to update the .htaccess file with additional configuration options. For those unaware, the .htaccess file is a plain text file used in the Apache web server to allow per site, per directory configuration of the web server.

As most are aware, hosting a web site generally costs money. Like most things which cost money, the people paying the bills generally like to get as much bang for their buck as possible. Unfortunately, there are people which take it upon themselves to reduce the bang, to a point where it will end up costing the site owner more money to host thier site.

Bandwidth usage during May 2006 for http://www.lattimore.id.auThe scenario I speak of very nearly happened to this site over the last fortnight. The additional configuration options which I neglected to add back into the .htaccess file were used to stop people linking directly to my images from another website; its generally referred to as hot linking.

Once I had realised that I hadn’t updated the information, I immediately checked my traffic logs to see if it had done any damage yet. At that point, somewhere around the 12th April there was no noticeable impact so as a test I thought I would allow Google access to the images via http://images.google.com.au. A few days later and the impact was clear; daily data consumption had increased from approximately 60Mb to 200Mb.

For the first week of May, the daily consumption has carried on from April. From the 8th May onwards, something changed dramatically as the site started to push out a literally double my previous daily peak! If the traffic trend from April continued for an entire month (~200Mb/day), I would exceed my monthly hosting plan and be billed an additional AU$600! When I checked how much traffic my site has been moving in the last couple of days, I was shocked to find out that if that trend continued (> 400Mb/day) that I’d be billed in excess of AU$1800 for a month of hosting!

To make sure this doesn’t happen again, I have re-enabled the hot linking protection through the .htaccess file. If you’re having a similar problem, you could achieve a similar outcome as follows:

  1. <IfModule mod_rewrite.c>
  2. RewriteEngine On
  3. RewriteBase /
  4. RewriteCond %{HTTP_REFERER} !^$
  5. RewriteCond %{HTTP_REFERER} !^http://(www\.)?lattimore\.id\.au [NC]
  6. RewriteRule \.(gif|jpe?g)$ - [NC,F]
  7. </IfModule>

Each line of the previous block is explained as follows:

  1. Check if the mod_rewrite module is available in Apache
  2. Enable the rewrite engine
  3. Set the base to the root of the domain
  4. Check that the referring information in your browser is not blank
  5. Check that the referring information in your browser is not www.lattimore.id.au
  6. Do a case insensitive check if the request has a .gif, .jpg or .jpeg in it and return a HTTP Forbidden to deny access

In a language that makes sense, it says that if you’re trying to access images on my site and you aren’t viewing the images from my site or entering the URL directly into your browser; you will be denied access to the image. So, if you were to directly link to one of my images from another website, your referring information won’t be blank and it won’t be this site – therefore you won’t be given access to the image.

If you did want to provide Google Images access to your files, you could insert the below two lines after line 5 above:

  1. RewriteCond %{HTTP_REFERER} !google\. [NC]
  2. RewriteCond %{HTTP_REFERER} !search\?q=cache [NC]

Traffic to your website is generally “a good thing”™. However, if you’re the one paying the bills – you’ll be well served to keep an eye on your website hosting statistics to make sure you aren’t going to get a nasty surprise.