How to Override SYSDATE function in Oracle9i

[tamilselvan]

Right. But when you temporarily changed the parameter, the init.ora is changed during the time it's effective. Any jobs that are running on the database, like backup, Unique transaction ID enbedded with timestamp will all take the sysdate as 12-DEC-2007 00:00:00 during the time the FIXED_DATE is defined. My requirement is to fix the sysdate at session level. May not be through FIXED_DATE. Is there any other parameter that can be altered through ALTER SESSION, not ALTER SYSTEM. I see some of the parameters can be altered at session level and also at system level. From OraDoc, it says FIXED_DATE can be altered only at SYSTEM level, which is NOT what I want.

In this case, I would write my own fn.
See below:

create or replace function tamil.mysysdate
return date
as
begin
  return to_date('31-12-2007','DD-MM-YYYY');
end;
/

And instead of sysdate I would call mysysdate where ever I want.

The reason why Oracle does not allow to change at session level is: The SYSDATE fn calls OS to get the date and time. Its name is timeofday(). And I believe many procedures, PKGs, MVs and functions owned by SYS depend on the increasing time.

You CANNOT change the time at session level.

It would be better to change (globally if possible) your application code to use your own fn for testing purpose.

Tamil