How to Override SYSDATE function in Oracle9i
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: How to Override SYSDATE function in Oracle9i

  1. #1
    Join Date
    Aug 2006
    Posts
    5

    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.

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    you cant, the db gets it time from the server, you can use fixed_date but that time will never change

  3. #3
    Join Date
    Aug 2006
    Posts
    5

    Angry 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.

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    you cant do what you want then

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Can you tell us what problem you are trying to solve here? Is it an issue with time zones?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #6
    Join Date
    Aug 2006
    Posts
    5
    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.

  7. #7
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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.

  8. #8
    Join Date
    Aug 2006
    Posts
    5
    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.

  9. #9
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Quote 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

  10. #10
    Join Date
    Aug 2006
    Posts
    5
    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
  •  



Click Here to Expand Forum to Full Width