Thanks for the question, Kurt.

Asked: March 16, 2017 - 3:44 pm UTC

Last updated: March 17, 2017 - 10:30 am UTC

Version: 12.1.0.2.0

Viewed 10K+ times! This question is

You Asked

When writing code that deals with time, it would be very useful to be able to "set" the starting point for SYSDATE within a session.

For example, suppose I want to select one set of data if the query runs in March and a different set if it runs in April (perhaps
because the start of a new fiscal year is April 1). I'd like to be able to use SYSDATE in the query for clarity, but that means
I can't actually test the code behavior for April until April 1.

Currently, I just develop and test the code with a custom MY_SYSDATE function that returns a testing date value, but that requires
me to remember to revert the code to use the real SYSDATE (and to change/compile the custom function to use a new date).

What I'd really like to do initialize SYSDATE at the beginning of my session and have Oracle handle the differences for me through
the session, something like this:

SQL>alter session set date_baseline = to_date( '2017-04-01 04:30:12', 'yyyy-mm-dd hh24:mi:ss' ) ;

Then, all subsequent references to SYSDATE would be relative to 2017.04.01 00:00:00 and remain so until I either terminate my
session or run another alter command. Of course, we'd need to have this ability granted to us in DEV/TEST but not granted in
production.

Is this possible? If so, how do I do it?

Thanks!

and Chris said...

Not for an individual session, but you can set the date for a whole database!

Setting fixed_date locks the database to a particular point in time:

select sysdate from dual;

SYSDATE               
16-MAR-2017 10:23:18  

alter system set fixed_date = '2017-01-01';

select sysdate from dual;

SYSDATE               
01-JAN-2017 00:00:00  

alter system set fixed_date = '2017-04-01 12:34:56';

select sysdate from dual;

SYSDATE               
01-APR-2017 12:34:56  

alter system set fixed_date = 'NONE';

select sysdate from dual;

SYSDATE               
16-MAR-2017 10:23:21

Just make sure you set it back to 'NONE' once you're finished!

Rating

(4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks, but it doesn't seem like a solution

Kurt Arthur, March 16, 2017 - 5:54 pm UTC

Thanks for the suggestion, but I don't think it'll work as I envision, primarily because its a database level setting and secondly because the SYSDATE doesn't advance from the baseline. We can work around the second issue, but the first issue is a deal breaker in just about any environment I've ever been in.

Thanks for the quick response, though!

P.S. as a suggestion for AskTom, consider separating the response quality rating from the response utility. In this case, the response is a 5, but the utility is only a 2.

Thanks again!
I think the

March 16, 2017 - 6:02 pm UTC

Yeah it being database wide does mean you have to co-ordinate with the rest of your team!

Very Useful

A reader, March 16, 2017 - 10:02 pm UTC

Conditional Compilation

Rajeshwaran, March 17, 2017 - 1:56 am UTC

....
Currently, I just develop and test the code with a custom MY_SYSDATE function that returns a testing date value, but that requires 
me to remember to revert the code to use the real SYSDATE (and to change/compile the custom function to use a new date). 

What I'd really like to do initialize SYSDATE at the beginning of my session and have Oracle handle the differences for me through 
the session, something like this: 
....

If the data processing logic is inside the PL/SQL then we could leverage Conditional compilation feature like this ( using Inquiry directive )

demo@ORA12C> create or replace procedure process_data
  2  as
  3     l_date1 date;
  4     l_date2 date;
  5     l_cnt int;
  6  begin
  7     l_date1 := case when $$val is null then trunc( sysdate ,'Y' )
  8                             else trunc( to_date($$val,'yyyy') ,'Y') end;
  9
 10     l_date2 := add_months( l_date1 ,12 );
 11     select count(*)
 12     into l_cnt
 13     from emp
 14     where hiredate >= l_date1 and
 15             hiredate < l_date2 ;
 16     dbms_output.put_line(' l_date1 = '||l_date1 );
 17     dbms_output.put_line(' l_date2 = '||l_date2 );
 18     dbms_output.put_line(' cnt = '||l_cnt);
 19  end;
 20  /

Procedure created.

if the processing year is mentioned during compilation then that financial year could be considered else will fall back to SYSDATE.

demo@ORA12C> alter procedure process_data compile plsql_ccflags='val:1981';

Procedure altered.

demo@ORA12C> exec process_data;
 l_date1 = 01-JAN-1981
 l_date2 = 01-JAN-1982
 cnt = 10

PL/SQL procedure successfully completed.

demo@ORA12C> alter procedure process_data compile;

Procedure altered.

demo@ORA12C> exec process_data;
 l_date1 = 01-JAN-2017
 l_date2 = 01-JAN-2018
 cnt = 0

PL/SQL procedure successfully completed.

demo@ORA12C>

In Dev/QA during testing, you could supply different values for $$VAL for each compilation.

In production deployment, you compile them without providing any such values for $$VAL

March 17, 2017 - 10:12 am UTC

Yep, it does complicate your code though. And it's still database wide, so doesn't really solve the "just for me" requirement.

Follow up

A reader, March 17, 2017 - 5:30 am UTC

alter system set fixed_date

Doesn't affect flash back? Scn?
Isn't dangerous isn't risky?

March 17, 2017 - 10:30 am UTC

In a production system yes, it's very dangerous and risky!

But in test/dev? Potentially very handy. You can check that all your queries will work right without having to fudge your data or code (which can bring it's own bugs). But you still need to take care and always unset it when you're done...

It does affect flashback by timestamp. But SCNs are always increasing, so these will still work:

col current_scn new_value sn

create table t (
  x int 
);
insert into t values (1);
commit;
exec dbms_lock.sleep(60);
select current_scn from v$database;

                            CURRENT_SCN
---------------------------------------
                               43197903

insert into t values (2);
commit;
exec dbms_lock.sleep(60);

select * from t as of timestamp sysdate - 1/1440;

         X
----------
         1

select * from t as of scn &sn;

old:select * from t as of scn &sn
new:select * from t as of scn 43197903

         X
----------
         1

alter system set fixed_date = '2017-01-01';

select * from t as of timestamp sysdate - 1/1440;

SQL Error: ORA-08180: no snapshot found based on specified time

select * from t as of scn &sn;

old:select * from t as of scn &sn
new:select * from t as of scn 43197903

         X
----------
         1

alter system set fixed_date = 'NONE';

select * from t as of timestamp sysdate - 1/1440;

         X
----------
         1
         2

select * from t as of scn &sn;

old:select * from t as of scn &sn
new:select * from t as of scn 43197903

         X
----------
         1

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Thanks, but it doesn't seem like a solution

Very Useful

Conditional Compilation

Follow up

More to Explore

Performance

Can I set the basis for SYSDATE within a session?

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Thanks, but it doesn't seem like a solution

Very Useful

Conditional Compilation

Follow up

More to Explore

Performance