Category Archives: Programming


It’s a sign you need either:

  1. more sleep; or
  2. more coffee

when you find code committed into a source code repository that you don’t remember writing and realise when you find it that it was really handy that you wrote that code at some obscure hour of the morning a week ago.

Learning How To Scale An ASP.NET Application

For the last nine months, the development team at Stella Hospitality Group have been working on integrating a new piece of software into the enterprise. Throughout that process, we’ve come up against various stumbling blocks and subsequently learned how to climb over them.

One of the interesting parts of this project involved learning how to scale an ASP.NET web application. Unlike most other pieces of development we’ve previously worked on, we didn’t have access to hardware and services that were capable of delivering smoking performance (read: Oracle 10g clustered using RAC). As a by product of the constraints which were placed on us, scaling the new web application proved a little harder than it first looked.

Over the course of the next few weeks, I’ll be posting about various steps which we’ve taken to scale our ASP.NET application. Some of the points are hinged in the physical world, others operational and of course technical as well. Items which come to mind immediately include:

  • load balancers
  • clustering physical servers
  • clustering web servers
  • web gardens
  • user interface process control
  • session handling
  • web services & XML
  • spike testing

Getting Your CSS Off

Toward the end of March 2006, Dustin Diaz thought that stripping all of the presentation from his web site was an excellent way of promoting web standards and CSS based design. The idea caught on and a whole swag of people participated in the first CSS Naked Day on April 5th 2006.

A year has passed and once more, web standards advocates alike around the world are stripping all of the presentation from their sites by running around butt naked without any cascading style sheets to hide their wobbly bits!

I thought about participating in the naked day this year, however I pretty much participate in it all year around so I didn’t think it would have had that much effort. If I had a graphic design bone in my body, I could have created the anti-naked day by getting my CSS on and releasing an amazing visual feast for everyone.

Unfortunately, I don’t have a visual feast for you this year – in the mean time, you should check out some cute cat photos!

Oracle Dynamic SQL Using The DECODE Function

When developing most applications, you end up having to interact with a database. If you’re application is large enough and warrants the design and effort, it will no doubt be tiered as well. If your application is tiered, one of your layers will probably include a database API. A database API is used to restrict access to the database, a funnel which all requests from your application must pass through. Implementing this additional layer of abstraction normally yields in higher performance, increased security and easier maintenance.

In most cases, you’re application code will require a fairly standard interface into your database to retrieve information. A simple way to achieve that consistent interface is using the DECODE function in your PL/SQL procedures and functions. There are always cases where you need to do something special, and in those cases it is probably a perfect scenario for this methods counter-part – the string building technique.

For a simple but practical example, consider the following table definition:

  1. CREATE TABLE MyTable (
  2. PrimaryKeyID NUMBER NOT NULL,
  3. ForeignKeyID NUMBER NOT NULL,
  4. Name VARCHAR2 (255) NOT NULL
  5. )

As I have mentioned before, the Oracle DECODE function provides similar functionality to an IF-THEN-ELSE code block. Lets examine an example which relates to MyTable above.

  2. (pDetails OUT grcDetails,
  3. pPrimaryKeyID IN MyTable.PrimaryKeyID%TYPE DEFAULT NULL,
  4. pForeignKeyID IN MyTable.ForeignKeyID%TYPE DEFAULT NULL,
  5. pName IN MyTable.Name%TYPE DEFAULT NULL)
  6. IS
  7. BEGIN
  8. OPEN pDetails FOR
  9. SELECT *
  10. FROM MyTable
  11. WHERE PrimaryKeyID = DECODE(pPrimaryKeyID, NULL, PrimaryKeyID, 0, PrimaryKeyID, pPrimaryKeyID)
  12. AND ForeignKeyID = DECODE(pForeignKeyID, NULL, ForeignKeyID, 0, ForeignKeyID, pForeignKeyID)
  13. AND LOWER(Name) LIKE DECODE(pName, NULL, LOWER(Name), '%'||LOWER(pName)||'%');
  14. END;

Working top down, the second line declares pDetails (the parameter name) as being an OUT parameter. As the name suggests, an OUT parameter is a way for you to pass information out of your procedure. In some aspects it would be similar to returning a value out of a typical programming function. The difference to an OUT parameter is that you can have many OUT parameters in a single procedure, where as a typical programming function can only return a single value at a time.

The next thing you’ve probably noticed is this weird thing in there, grcDetails. Just as the IN parameters below it, grcDetails is the type of the parameter. It would be akin to defining a paramter in a traditional function as accepting an integer or string type. In this case, grcDetails is declared in the package header:


The important thing to notice about grcDetails is that it is a REF CURSOR and its type is the ROWTYPE of MyTable. The benefit of defining it as a ROWTYPE is that if tomorrow the definition of the table changed in some way, you don’t have to worry about changing any code to match the new table definition – it simply continues to work. This is the same reason why the INPUT parameters above have their type defined against the field that they represent. Of course, if you are passing in or out a value which isn’t based on a field – you would simply define it as a standard PL/SQL type.

Next, you have probably noticed that each IN parameter has a DEFAULT value. As with most programming languages, providing a DEFAULT value allows the programmer to optionally supply that parameter. For our example, this is a pivotal point as it simplifies the use of this method; more on that later.

Moving on to the actual SQL statement, the flexibility of the DECODE function starts to show. Since the DECODE function acts like an IF-THEN-ELSE block, you’re actually seeing an inline IF-THEN-ELSE IF-ELSE block per field. Lets take a practical example of that statement and assume we passed in pPrimaryKeyID with a value of 1, while the other two input parameter’s are not passed in (thus taking their default value of NULL). The SQL statement would be parsed and executed as follows:

  1. SELECT *
  2. FROM MyTable
  3. WHERE PrimaryKeyID = 1
  4. AND ForeignKeyID = ForeignKeyID
  5. AND LOWER(Name) LIKE LOWER(Name);

Of course, the net effect of that SQL statement is that the first WHERE condition is used, while the second and third conditions are nullified as the left and right half of each expression are equivalent. So by passing in a NULL value for the pForeignKeyID and pName parameters, they are effectively removed from the SQL statement by evaluating to themselves. Of course, you could also pass in two, three or none of the parameters to the procedure as well. In which case you would get either a restricted set from the statement or all records in the table.

As mentioned above, the DEFAULT value of each IN parameter is critical to this methods success. By providing a default value, the programmer no longer needs to call the procedure with all parameters; all, some or none are also perfectly valid combinations. Since the input parameter’s default value is NULL, the following DECODE function call is all that is required to negate the parameter in the WHERE clause:

  1. PrimaryKeyID = DECODE(pPrimaryKeyID, NULL, PrimaryKeyID, pPrimaryKeyID)

By now, you have probably noticed some extra parameters in the example GET procedure listed above. The additional values are used to exclude any other ‘not important’ values from the statement. In the example procedure, NULL and the value 0 are considered unimportant; whilst all other values are considered useful.

If you’re wondering why you’d want to exclude other values, it might be to make another section of your application simpler. Some programming languages don’t support nullable primitive types. If you use such a language and you intend to pass in all parameters to your procedure in all circumstances (this is the ‘simpler’ above); then all values will have a value of some sort. In the case of a primitive such as an integer, you might find that its default uninitialised value is zero. If that is the case and you don’t require the ability to filter on a zero value, then excluding it within the DECODE function makes things simpler.

Don’t think you’re limited to using this method on SELECT statements, it will work a treat on DELETE too. Consider the following DEL procedure:

  2. (pRowCount OUT NUMBER,
  5. IS
  6. BEGIN
  9. FROM RoomTypes
  10. WHERE RoomTypeID = DECODE(pRoomTypeID, NULL, RoomTypeID, 0, RoomTypeID, pRoomTypeID)
  11. AND BuildingID = DECODE(pBuildingID, NULL, BuildingID, 0, BuildingID, pBuildingID);
  12. ELSE
  13. RAISE_APPLICATION_ERROR(-20001, 'At least one parameter must be supplied');
  14. END IF;
  15. pRowCount := SQL%ROWCOUNT;
  16. END;

There is a caveat to using this method for building dynamic SQL within Oracle, it cannot handle columns which are nullable. If you consider the use of the DEFAULT value on all of the input parameters, it will become clear. Within Orcale PL/SQL, it is not possible to use an equality (=) operator to compare a NULL value. As a simple example, take the two simple SQL statements:


The first SQL statement above will return the expected row from the DUAL table, while the second statement will return no results as you cannot compare the NULL value in that manner. If a comparison against a NULL is required, it must be handled using the IS NULL clause.

Other than not being able to use this method against columns which are nullable, its a really convenient way to write dynamic SQL in Oracle. Next time you need a little flexibility and you don’t want to go down the string building path, try using the Oracle DECODE function to produce your dynamic SQL.

Storing Time Information In Oracle

I recently had a requirement to store a time within Oracle, not a timestamp or a date – just the time. As it turns out, storing just the time information in Oracle isn’t something you can do without a little bit of work.

Oracle provides you a few different data types for handling date and time:

  • DATE

Unfortunately, none of the supplied data types are an exact match for a time such as 10:30 AM. The good news is that both a DATE and a TIMESTAMP data types contain time information – so it is possible to do what you want with a little slight of hand.

The slight of hand I mentioned has to do with the default behaviour of Oracle when inserting only a time component of a DATE or a TIMESTAMP data type. For the rest of this discussion, lets assume you have a simple table set up as follows:

  1. CREATE TABLE Times (
  2. id INT NOT NULL,
  3. thedate DATE NOT NULL,
  4. thetimestamp TIMESTAMP NOT NULL
  5. );

With the following three rows of data:

  1. INSERT INTO Times (id, thedate, thetimestamp) VALUES (1, SYSDATE, SYSDATE);
  2. INSERT INTO Times (id, thedate, thetimestamp) VALUES (2, TO_DATE('10:30 AM', 'HH:MI AM'), TO_DATE('10:30 AM', 'HH:MI AM'));
  3. INSERT INTO Times (id, thedate, thetimestamp) VALUES (3, TO_DATE('0001-01-01 10:30 AM', 'YYYY-MM-DD HH:MI AM'), TO_DATE('0001-01-01 10:30 AM', 'YYYY-MM-DD HH:MI AM'));

When selecting that grid of information out, you’ll receive:

id thedate thetimestamp
1 28/03/2007 11:32:25 PM 28/03/2007 11:32:25.000000 PM
2 1/03/2007 10:30:00 AM 1/03/2007 10:30:00.000000 AM
3 1/01/0001 10:30:00 AM 1/01/0001 10:30:00.000000 AM

There are more than one solution to this problem, as if you’re using any of the standard date/time data types – you can always store the time component. What may or may not be of interest to everyone is what Oracle does with the date component of a DATE or TIMESTAMP data type when you don’t provide the standard date components of a date/time data type.

If you take notice of row #1 returned, you’ll see that because the inserted values for thedate and thetimetamp was SYSDATE, it has stored the date component as you’d expect.

Compare that against row #2 and you’ll notice that the insert statement simply provided the time component and made no mention of the date. When Oracle returned that time value out, it has automatically substituted the first day of the current month into the date component.

When inserting row #3, an arbitrary date of 1 Jan 0001 was supplied. When reading that information back out of Oracle, it handles the date component in a similar fashion to how it was handled for row #1 using the SYSDATE; ie you get the expected result back.

Since you can return any number of rows from Oracle doing standard date/time arithmetic, it really comes down to preference. What option above has the most semantic meaning to a user or the data in the database when you’re required to store a time and not a timestamp?

For consistency reasons, my personal preference leans towards applying technique #3 and here are a couple of simple reasons why:

  • When dealing with a DATE or TIMESTAMP value stored like that in PL/SQL, you’ll always know what value is stored in the date component of the value.
  • If you’re handling this information in an application language such as Microsoft .NET, Java or Python – you will always know what value to expect in the date component. Using technique #1, the date component will change for every row. Using #2, the date component of the value will change per row depending on what month the row was inserted

Does anyone else have a hot tip for storing a straight time value in Oracle?