Printable View
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
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.
I did not change init.ora file, but changed the parameter temporarily.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
Hope this helps .......
Tamil
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.Quote:
Originally Posted by rdevulapalli
See below:
And instead of sysdate I would call mysysdate where ever I want.Code:create or replace function tamil.mysysdate
return date
as
begin
return to_date('31-12-2007','DD-MM-YYYY');
end;
/
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
Then I guess the answer is "no, you can't do it". I can't think of a way, in any case.
Clone the db to another environment, and change the date using FIXED_DATE, then test your application.Quote:
Originally Posted by rdevulapalli
Do not say that you are shortage of disks. Disks are very very cheap today.
Tamil
I tried creating a local function called sysdate with mixed results.
This would be the direct approach. But it won't compile, as SYSDATE can't be overridden.
However, that did not stop me from creating "SYSDATE" with interesting results.Code:1 create or replace function SYSDATE
2 return date
3 as
4 v_newdate DATE;
5 BEGIN
6 v_newdate := sys.standard.sysdate - 4/24;
7 RETURN v_newdate;
8* END;
SQL> /
create or replace function SYSDATE
*
ERROR at line 1:
ORA-04050: invalid or missing procedure, function, or package name
Code:1 create or replace function "SYSDATE"
2 return date
3 as
4 v_newdate DATE;
5 BEGIN
6 v_newdate := sys.standard.sysdate - 4/24;
7 RETURN v_newdate;
8* END;
SQL> /
Function created.
SQL> SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY:HH24:MI:SS') FROM dual;
TO_CHAR(SYSDATE,'DD-
--------------------
17-AUG-2006:16:34:01
SQL> SELECT TO_CHAR("SYSDATE", 'DD-MON-YYYY:HH24:MI:SS') FROM dual;
TO_CHAR("SYSDATE",'D
--------------------
17-AUG-2006:12:34:07
The original poster said he can't change the code.Quote:
However, that did not stop me from creating "SYSDATE" with interesting results
Tamil
The goal of the exercise was to see if I can create a locel sysdate function that would be called instead of the sysdate function that is a part of dbms_standard. :rolleyes:Quote:
Originally Posted by tamilselvan
You should be developing and testing on a test/dev instance of oracle.. and if you are, why do you care about backups failing and jobs not running because of the time..?
The live system will carry on, and you work and break on the test system.. Surely you do not develop on your live system?