Re: SYSDATE, how to fast-forward?

itimms_at_crcl.com.au (Ian Timms) wrote:

>Hi,
 

>We have a need to test payroll cycles and need some method of
>advancing the date used by the application. We have no
>control over how the front end application does its date
>handling, nor (for the most part) over the batch jobs run
>on the server. So how _should_ one advance the date to
>perform any valid testing of this kind of thing? Is it
>possible to set SYSDATE forward a number of days?
>Advancing the machines date is not an option as it's used
>for other development, so what _do_ people do in this
>situation?
 

>I've had one response indicating that fixed_date might be
>used in the init.ora, but I don't think I want it fixed,
>what I want is to fast-forward.
 

>Cheers, Ian.

What you may want to do is abandon the use of sysdate, at least don't call it directly.

Use your own function instead. Try something like this: ( this code is 100% untested; I'm at home without Oracle access )

create or replace package datepkg
is

• offset represents number of days to fast forward Offset integer := 0 ; function MySysdate( reset boolean default FALSE) return date; procedure FastForward( days integer ); end datepkg; /

create or replace package datepkg
is

	function MySysdate( reset boolean default FALSE ) return date
	is
		mydate date := sysdate;		-- initialize - just a good habit
	begin
		if reset then
			datepkg.offset :=0;
		end if;

		mydate := sysdate + datepgk.offset;
		return mydate;
	end;

	procedure FastForward ( days integer ) 
	is
	begin
		datepkg.offset := days;
	end;

end datepkg;
/

Always call datepkg.MySysdate instead the builtin sysdate.

Where you want to make sure that you always have the actual sysdate, call it with 'datepkg.MySysdate(TRUE)'

HTH

jared still                  ---- ___o
jared_at_valleynet.com       ----  _`\ <,_
                       -----   (*)/o (*)
--------------------------------------