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.

XE: ODBC connection to a remote XE server

How do you set up an ODBC connection to a XE instance on a remote server?

The basic steps are:
* Download and install the XE client on your Windows machine
* Create an ODBC DSN entry
* Create a new MS Access MDB and link two tables from XE (using the sample user HR)

First, you download the XE Client software from the Oracle Technet: http://www.oracle.com/technology/software/products/database/xe/index.html

Double-click on the downloaded file to install it:

During the installation choose to install the client software into a directory of your choice and finish the installation.



After that you can configure the ODBC entry. Goto Start > Control Panel > Administration > ODBC DatasourcesOn the tab System-DSN add another data source:


Choose Oracle in XEClient:


Enter the details for the connection to the remote machine, in my case the remote machine is daust3.opal-consulting.de

Data Source Name: choose an arbitrary name
Description: choose an arbitrary description
TNS Service Name: host:port/SID
User ID: Oracle user to connect to

The service name is critical here, the hostname is the name of the remote machine but it can also be an ip-address. The port is the port on which the Oracle listener is operating, usually 1521. The SID is fixed, it is called XE.

Then test the connection by entering the password for the user HR. The result has to be "Connection successful".


After that start MS Access (in my case I used MS Access 2000) and create a link to the tables stored in XE. You can do this either by clicking on New > Link table


or by right clicking in the table pane and selecting Link Tables ...


then choose ODBC Databases from the bottom of the list:


Choose the computer data source XE, which we just configured:


then enter the password for the user HR. You can also just overwrite the User Name with a different user you want to connect to:


then select the relevant tables by holding down the control-key and click on the table names. Then hit OK.


Voila! The tables are linked and you can start using them:


Just be aware of firewalls!!! If you cannot connect to the remote machine the reason can be that port 1521 is blocked by your firewall!

Update: 18.01.2007

Storing the password in the DSN entry

You can also store the password together with the username in the DSN entry. This way the login prompt won't show when you link tables in MS Access. This might be useful in certain situations.
Just use the username/password in the User ID field when configuring the DSN entry.
For example you want to connect as the oracle user HR with password hrpwd. Then enter HR/hrpwd as the User ID instead of HR.

Thanks to Jer for this tip ( post in the XE forum ) .


Update: 18.01.2007

Using tnsnames.ora with XE client

In the DSN entry you can specify the connection information to the XE server instance using the easy connect syntax, just as shown above.

But you can also use the traditional resolution via the tnsnames.ora file. This adds another layer of abstraction so that you can access the tnsnames entry in you VBA code and not hardcode the server, port and SID information into your code.

To accomplish this, the following steps are necessary:

First, you have to add the variable TNS_ADMIN to your Oracle configuration in the registry.
Start regedit and go to HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_XEClient.



Then add a new string called TNS_ADMIN. Set the value to the directory where you want to store the tnsnames.ora file (you can also reference a directory of another Oracle install where a valid file is located).




Then create (or copy) the file tnsnames.ora and enter the following content:

XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = DAUST3.opal-consulting.de)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)

Then you can reconfigure your DSN entry and replace daust3.opal-consulting.de:1521/XE with XE.


Regards,
~Dietmar.