How to Override SYSDATE function in Oracle9i

Then I guess the answer is "no, you can't do it". I can't think of a way, in any case.

Quote:

---

Originally Posted by rdevulapalli

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

---

Clone the db to another environment, and change the date using FIXED_DATE, then test your application.

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.

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

---

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> /

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

---

Quote:

---

However, that did not stop me from creating "SYSDATE" with interesting results

---

The original poster said he can't change the code.

Tamil

Quote:

---

Originally Posted by tamilselvan

The original poster said he can't change the code.

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:

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?