-
How to Override SYSDATE function in Oracle9i
How do I override SYSDATE function in Oracle9i Rel2. I need to mimic SYSDATE function to return a preset or offset value, without touching the DB Server Date. I need to be able to change the Database clock independent to Server date on which the Database is running.
-
you cant, the db gets it time from the server, you can use fixed_date but that time will never change
-
How to Override SYSDATE function in Oracle9i
But the Fixed_date changed my init parameter file. I need something at session level. Even if the value is fixed, that's fine for me. But it should not affect the other dabase sysdate based processes like mon processes. I need to alter the session to override SYSDATE.
Please let me know whether this is possible.
Feature something like this would have beed used during Y2K testing days.
-
you cant do what you want then
-
Can you tell us what problem you are trying to solve here? Is it an issue with time zones?
-
No. This is not an issue with the time Zone. This is to do regression testing to produce same result consistantly with same input data. Unfortunately, SYSDATE is also one of the input parameter. When I run the test case for the 2nd time, it produces different result. There is no automatic way whether the produced result is correct or not, unless I manually calculate each time I run the case and certify. If I could re-set the sysdate to the date and time, I ran the 1st case, then I am sure to produce same results all the time, then there is an automatic way to tell whether the test case is pass or fail. I am talking about such cases of about 2000 cases.
Each time, I release anything, I need to run these 2000 cases and certify, they are not affected.
-
Code:
SQL> show parameter fixed_date
NAME TYPE VALUE
--------------- ----------- ------------------------------
fixed_date string NONE
SQL> alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS' ;
Session altered.
SQL> select sysdate from dual;
SYSDATE
--------------------
16-AUG-2006 12:23:52
SQL> alter system set fixed_date = '12-DEC-2007 00:00:00' ;
System altered.
SQL> select sysdate from dual ;
SYSDATE
--------------------
12-DEC-2007 00:00:00
SQL> alter system set fixed_date = none ;
System altered.
SQL> show parameter fixed_date
NAME TYPE VALUE
-------------------- ----------- ------------------------------
fixed_date string NONE
SQL>
SQL> alter session set fixed_date = '12-DEC-2007 00:00:00' ;
alter session set fixed_date = '12-DEC-2007 00:00:00'
*
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this
option
I did not change init.ora file, but changed the parameter temporarily.
Hope this helps .......
Tamil
Last edited by tamilselvan; 08-16-2006 at 01:29 PM.
-
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.
-
Originally Posted by rdevulapalli
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:
Code:
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
-
Unfortunately, I am talking about using SYSDATE in 111 packages for 1800 times. It's not possible to change those packages for testing alone. Both the UAT and Production environments have the same Code. So, the only alternative is to stall the SYSDATE without affecting the Database date, because there are other systems with different front ends working on other schemas. When I do "Alter SYSTEM SET FIXED_DATE=...", those systems are not expected to show the SYSDATE what I set. New package/function is an not an alternative in my case. I need SYSDATE function override.
Thanks for your response.
Ravi
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|