Testing Time Dependent PL/SQL

Submitted by Thomas on Sun, 01/23/2011 - 00:00

One of the main reasons web developers (like me) shy away from placing business logic in the database is the difficulty of testing it. One of those problems is how to test time dependent code - monthly billing cycles, repeat reminders, interest payments, etc. Code that's usually very important to get right. (And tends to run unattended at 2am.)

Oracle does offer a way to set the internal system clock to a fixed time for testing.

ALTER SYSTEM SET FIXED_DATE = '31-JAN-1999';

But it's ugly and requires alter system privileges. With a little work using EXECUTE IMMEDIATE (plsql's answer to LISP!) this can all be wrapped up in package. You can then write your tests against clean utility functions, and just grant your test user execute on the package.

See the the full script at GitHub.