Date sensitive integration testing of database code with Oracle

Note:

• Commands starting with $ are to be entered in a shell, e.g. BASH.


• Commands starting with SQL> are to be entered in a database client, e.g. SQLPlus or JDBC code

Scenario

Lately, I've been working with a lot of database code, mostly packaged as stored procedures, running on Oracle 10g XE. These stored procedures are tested using a set of automated integration tests written in Java (JUnit + DBUnit). Everything was fine until one day, I needed to implement a logic that filters out data based on whether a date field stores a date within a certain period of time. The SQL for this was quite trivial, for example:

SELECT ... WHERE table1.the_date_field > ADD_MONTHS(SYSDATE, -6)

This example would filter out all results with the_date_field storing a date older than 6 months ago.

The Problem

The problem arose when I needed to write automated tests for this. The tests loaded up a set of hardcoded test data fixture (using DBUnit). The test data would have hardcoded date values. Obviously, these tests would give different results over time because SYSDATE would return the current system time by default.

The Solution(s)

One possible solution would be to dynamically inject the dates into the test data when the tests were executed. However, this would require a lot of work because of the quantity and spread of the dates. I would need to convert existing hardcoded dates into offsets from SYSDATE, then write some code to perform the injection at runtime.

Given this, I chose the reverse approach :- make SYSDATE return a fixed value.

Stopping time

Luckily, Oracle provided a way to let me do just this.
Firstly, I had to login to the database as a SYSDBA and grant my Oracle user the ALTER SYSTEM system privilege.
Now, I could invoke the following SQL in the set-up code of the JUnit tests:

SQL> alter system set fixed_date = '01-APR-2005'

This would make SYSDATE always return April 1st, 2005. All the test data could now be measured relative to this date, and the tests would not break no matter when they were run.

The tear-down code of the JUnit tests would invoke:

SQL> alter system set fixed_date = 'none'

to revert SYSDATE back to its default behaviour.

PFile and SPFile

However, when I tried to restart Oracle server, it failed to start up. After a few hours, I found out why. Oracle by default stored parameter settings in a binary file format called SPFile. Any changes made with ALTER SYSTEM were always persisted to this file, so that when Oracle was restarted, it tried to load them again. For some reason (a bug IMHO), Oracle could not load the fixed_date value of 'none', it simply died.

The workaround was to change to a different file format called PFile. PFile is a normal text file that could be editted, and unlike SPFile, Oracle would not persist any parameter changes to it. In my opinion, PFile is a better choice for a development or automated testing database, where changes made during tests should not be persisted or interfere each other. To convert from SPFile to PFile, first login as a SYSDBA:

$ $ORACLE_HOME/bin/sqlplus sys as sysdba

then execute:

SQL> create pfile from spfile;

Then shut down Oracle server, and remove the SPFile. The SPFile name has the following format: spfileSID.ora, where SID is the database SID. I was developing on a Ubuntu machine and my SID was XE. Hence, I ran this:

$ rm $ORACLE_HOME/dbs/spfileXE.ora

The PFile name has the format initSID.ora, so it was initXE.ora on my machine. Edit this file with a text editor and delete the line:

*.fixed_date='NONE'

Finally, restart Oracle.

Limitations

I found that when SYSDATE was fixed to a hardcoded value, jobs could not be scheduled using DBMS_JOB functions. DBMS_JOB.SUBMIT did not seem to work at all. Therefore, I can't yet think of a way of both freezing time and testing any code that rely on scheduled jobs.

---