Friday, January 26, 2007

Oracle: Changing the value for SYSDATE

In many applications you work with time sensitive data, e.g. a table of products that have valid_from and valid_to dates.

You might want to display the currently valid products at some point and thus compare the current date (SYSDATE) to the valid_from and valid_to date values.

Or you calculate some values based on the value of SYSDATE.

These parts of your application are difficult to test. Altering the system clock is generally not a good idea!!!

So, how can you do this? Usually you have to modify the test data or change the code for testing purposes. But this is cumbersome and you usually can only test parts of the program.

I have come across this post recently how to set a specific fixed return value when you call SYSDATE.

It is very simple, you can set any date as the current date for SYSDATE:

ALTER SYSTEM SET fixed_date = '2003-01-01-10:00:00';
can be reset with a simple
ALTER SYSTEM SET fixed_date = NONE;


Consider the following example:


HR@o102> conn hr/hr
Connected.
HR@o102> select to_char(sysdate, 'dd.mm.yyyy hh24:mi:ss') now from dual;

NOW
-------------------
26.01.2007 22:18:56

HR@o102> create table products(id number, product varchar2(50), valid_from date, valid_to date);

Table created.

HR@o102> insert into products(id,product,valid_from,valid_to)
2 values (1, 'product1', to_date('01.01.2006','dd.mm.yyyy') , to_date('25.12.2007','dd.mm.yyyy'));

1 row created.

HR@o102> insert into products(id,product,valid_from,valid_to)
2 values (2, 'product2', to_date('01.01.2006','dd.mm.yyyy') , to_date('25.01.2007','dd.mm.yyyy'));

1 row created.

HR@o102> insert into products(id,product,valid_from,valid_to)
2 values (3, 'product3_new', to_date('31.01.2007','dd.mm.yyyy') , to_date('31.12.2007','dd.mm.yyyy'));

1 row created.

HR@o102> commit;

Commit complete.

HR@o102> create or replace view products_current_v as
2 select * from products where trunc(sysdate) between valid_from and valid_to;

View created.

HR@o102> select id, product from products_current_v;

ID PRODUCT
---------- --------------------------------------------------
1 product1

HR@o102> conn / as sysdba
Connected.
SYS@o102> alter system set fixed_date='2006-12-31-10:00:00';

System altered.

SYS@o102> conn / as sysdba
Connected.
SYS@o102> select to_char(sysdate, 'dd.mm.yyyy hh24:mi:ss') now from dual;

NOW
-------------------
31.12.2006 10:00:00

SYS@o102> select id, product from hr.products_current_v;

ID PRODUCT
---------- --------------------------------------------------
1 product1
2 product2

SYS@o102> conn / as sysdba
Connected.
SYS@o102> alter system set fixed_date='2007-02-01-10:00:00';

System altered.

SYS@o102> conn hr/hr
Connected.
HR@o102> select to_char(sysdate, 'dd.mm.yyyy hh24:mi:ss') now from dual;

NOW
-------------------
01.02.2007 10:00:00

HR@o102> select id, product from products_current_v;

ID PRODUCT
---------- --------------------------------------------------
1 product1
3 product3_new

HR@o102> conn / as sysdba
Connected.
SYS@o102> alter system set fixed_date=none;

System altered.

SYS@o102> select to_char(sysdate, 'dd.mm.yyyy hh24:mi:ss') now from dual;

NOW
-------------------
26.01.2007 22:18:56

Update 22.03.2007

There is an important thing to note!!! In Oracle 9.2.x there is a bug regarding the setting of fixed_date. Once you restart the instance, you will run into an oracle error: ORA-00065: initialization of FIXED_DATE failed

SQL> ALTER SYSTEM SET fixed_date = '2007-04-01-10:00:00';
System altered.

SQL> ALTER SYSTEM SET fixed_date = NONE;
System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-00065: initialization of FIXED_DATE failed
SQL>

Once you hit this error, you will need to recover using the following approach:
SQL> create pfile from spfile;
File created.
A new pfile was created in the $ORACLE_HOME/dbs directory, in my case:
/opt/oracle/product/9205/dbs/inito920.ora

Edit the file and delete the line *.fixed_date='NONE'

Then restart the database:
SQL> startup pfile=/opt/oracle/product/9205/dbs/inito920.ora
ORACLE instance started.

Total System Global Area 571545196 bytes
Fixed Size 452204 bytes
Variable Size 402653184 bytes
Database Buffers 167772160 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.

SQL> create spfile from pfile;
File created.
Then restart the database to have it started using the spfile.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 571545196 bytes
Fixed Size 452204 bytes
Variable Size 402653184 bytes
Database Buffers 167772160 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL>
Now do it again, this time the proper way:

SQL> ALTER SYSTEM SET fixed_date = '2007-04-01-10:00:00';

System altered.

SQL> ALTER SYSTEM SET fixed_date = NONE;

System altered.

SQL> alter system reset FIXED_DATE scope=spfile sid='*';

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 571545196 bytes
Fixed Size 452204 bytes
Variable Size 402653184 bytes
Database Buffers 167772160 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL>

Regards,
~Dietmar.

8 comments:

Anonymous said...

Dietmar,

I found this trick a few years ago when I was trying to do unit testing with SQLUnit. I think I originally found it on asktom.

It's surprising to learn that not many people know about it.

chet

Patrick Wolf said...

Dietmar,

thanks for bringing this trick to my attention! Didn't know that.

In our company we have written a getSYSDATE function which can be use for the same effect, but that trick is much easier and also works if a developer has used SYSDATE.

Conclusio: You can learn every day something new when working with Oracle :-)

Patrick

Unknown said...

Hi Chet,

>I think I originally found it on asktom.

Well, where else could it have been ;).

>It's surprising to learn that not many people know about it.

It is definitely surprising but nevertheless very true. I am working with Oracle now for nine years in many different projects but I have not yet come across this very useful feature.

I am interested in your unit testing experiences. Can you mail me at diet_mar.aust@opal-con_sulting.de (remove the underscores) ?

Thanks,
~Dietmar.

Unknown said...

Hi Patrick,

thanks for your comment!

BTW, your BLOG is really excellent, good job.

Greetinx,
~Dietmar.

Patrick Wolf said...

Thanks Dietmar! :-)

Anonymous said...

Hi dietmar,
good post, although I knew about this some time ago but never had the opportunity to try it out.
Now I have but specifically on an oracle database running e-business suite. You wouldnt happen to know what special side-effects apps will show when using this feature would you? tia.

Unknown said...

Hi,

no, unfortunately I have no experience with using the BI-Suite whatsoever.

Regards,
~Dietmar.

Sid said...

Thanks Dietmar..

It worked out and my problem is resolved.