Is SYSDATE Deterministic? #JoelKallmanDay

Categories
Tags
Is SYSDATE deterministic?

TL;DR

No, SYSDATE is not deterministic. However, it behaves very similarly to a deterministic function within an Oracle Database SQL statement.

Background

On a customer site, we used faketime on the OS to change the date and time for testing purposes. As long as you only travel forward in time, the Oracle Database behaves fine. The solution worked well for the last 8 years. However, this approach had some drawbacks, such as the need for a full-blown VM (we cannot change the time in an isolated manner within a Docker container), disabling time synchronisation processes and issues with third-party software due to failed sanity/license checks. This approach prevented us from simplifying the architecture of the testing environment.

We were exploring alternatives to travel in time only within the Oracle Database. One option was to set FIXED_DATE, and another to replace calls to SYSDATE, SYSTIMESTAMP, CURRENT_DATE, CURRENT_TIMESTAMP and LOCALTIMESTAMP with custom functions that added an offset to the current date/time. For the latter, I’d like to share some simplified test results.

But before that, let’s look at some definitions.

Definitions

Wikipedia defines a deterministic algorithm in computer science as follows:

In computer science, a deterministic algorithm is an algorithm that, given a particular input, will always produce the same output, with the underlying machine always passing through the same sequence of states. Deterministic algorithms are by far the most studied and familiar kind of algorithm, as well as one of the most practical, since they can be run on real machines efficiently.

The Oracle Database PL/SQL Language Reference defines deterministic for the deterministic_clause as follows:

  • A function is deterministic if the DETERMINISTIC clause appears in either a declaration or the definition of the function.
  • The DETERMINISTIC clause may appear at most once in a function declaration and at most once in a function definition.
  • A deterministic function must return the same value on two distinct invocations if the arguments provided to the two invocations are the same.
  • A DETERMINISTIC function may not have side effects.
  • A DETERMINISTIC function may not raise an unhandled exception.
  • If a function with a DETERMINISTIC clause violates any of these semantic rules, the results of its invocation, its value, and the effect on its invoker are all undefined.

How to Check?

There are two ways to check if a function is deterministic. Firstly, you can query the data dictionary like this:

1) Query Data Dictionary
select deterministic 
  from dba_procedures
 where procedure_name = 'SYSDATE';
DETERMINISTIC
-------------
NO

Secondly, you can check the source code to see if it contains a deterministic_clause. SYSDATE is defined in the STANDARD package as a wrapper to the kernel function pessdt and has no deterministic_clause.

2) STANDARD specification excerpt
create or replace package STANDARD AUTHID CURRENT_USER is …
  function SYSDATE return DATE;
    pragma FIPSFLAG('SYSDATE', 1452); …
end;

3) STANDARD body excerpt
create or replace package body STANDARD is …
  -- Bug 1287775: back to calling ICD.
  -- Special: if the ICD raises ICD_UNABLE_TO_COMPUTE, that means we should do
  -- the old 'SELECT SYSDATE FROM DUAL;' thing.  This allows us to do the
  -- SELECT from PL/SQL rather than having to do it from C (within the ICD.)
  function sysdate return date is
    d date;
  begin
    d := pessdt;
    return d;
  exception
    when ICD_UNABLE_TO_COMPUTE then
      select sysdate into d from sys.dual;
      return d;
  end; …
end;

So, SYSDATE is clearly non-deterministic.

Interesting are the comments regarding bug 1287775 (APPSST:PERF: SERVER SIDE PL/SQL SHOULD NOT USE SQL TO OBTAIN SYSDATE). It does not seem to be entirely about performance. The last update of the bug was in mid-2021. I was unable to find out why a pessdt call may fail, and why getting SYSDATE via a query from dual should solve the problem. Anyway, it’s not essential for this blog post.

Comparing SYSDATE to a Non-Deterministic Function

Let’s create a non-deterministic function APPDATE that returns SYSDATE. Then, we compare the SYSDATE and APPDATE results in a PL/SQL cursor.

4) Non-deterministic APPDATE
create or replace function appdate return date is
begin
  return sysdate;
end;
/

set serveroutput on size unlimited
set arraysize 1
declare
   cursor c is 
        select rownum,
               to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') as the_sysdate, 
               to_char(appdate, 'YYYY-MM-DD HH24:MI:SS') as the_appdate
          from user_objects
         where rownum <= 5;
   r c%rowtype;     
begin
    open c;
    sys.dbms_output.put_line('# THE_SYSDATE         THE_APPDATE');
    sys.dbms_output.put_line('- ------------------- -------------------');
    loop
        fetch c into r;
        exit when c%notfound;
        sys.dbms_output.put_line(r.rownum || ' ' || r.the_sysdate || ' ' || r.the_appdate);
        sys.dbms_session.sleep(1);
    end loop;
    close c;
end;
/
Function APPDATE compiled

# THE_SYSDATE         THE_APPDATE
- ------------------- -------------------
1 2025-10-13 08:28:29 2025-10-13 08:28:29
2 2025-10-13 08:28:29 2025-10-13 08:28:30
3 2025-10-13 08:28:29 2025-10-13 08:28:31
4 2025-10-13 08:28:29 2025-10-13 08:28:32
5 2025-10-13 08:28:29 2025-10-13 08:28:33


PL/SQL procedure successfully completed.

You see, the results are different except for the first row. We had to set the fetch array size to one to ensure the sleep call on line 25 affects each fetched record. Otherwise, you would see the effect between the batch of records defined by the default fetch size.

Comparing SYSDATE to a Deterministic Function

Let’s make the APPDATE function deterministic and rerun the script.

5) Deterministic APPDATE
create or replace function appdate return date deterministic is
begin
  return sysdate;
end;
/

set serveroutput on size unlimited
set arraysize 1
declare
   cursor c is 
        select rownum,
               to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') as the_sysdate, 
               to_char(appdate, 'YYYY-MM-DD HH24:MI:SS') as the_appdate
          from user_objects
         where rownum <= 5;
   r c%rowtype;     
begin
    open c;
    sys.dbms_output.put_line('# THE_SYSDATE         THE_APPDATE');
    sys.dbms_output.put_line('- ------------------- -------------------');
    loop
        fetch c into r;
        exit when c%notfound;
        sys.dbms_output.put_line(r.rownum || ' ' || r.the_sysdate || ' ' || r.the_appdate);
        sys.dbms_session.sleep(1);
    end loop;
    close c;
end;
/
Function APPDATE compiled

# THE_SYSDATE         THE_APPDATE
- ------------------- -------------------
1 2025-10-13 08:29:00 2025-10-13 08:29:00
2 2025-10-13 08:29:00 2025-10-13 08:29:00
3 2025-10-13 08:29:00 2025-10-13 08:29:00
4 2025-10-13 08:29:00 2025-10-13 08:29:00
5 2025-10-13 08:29:00 2025-10-13 08:29:00


PL/SQL procedure successfully completed.

Now we have the same result. SYSDATE is evaluated once for the whole query as the deterministic function APPDATE. There is obviously some special treatment of SYSDATE in the Oracle Database code for SQL statements. It handles it similarly to a deterministic function or a bind variable. That’s good. This matches the read consistency concepts. We do not want to get different results of SYSDATE in a single query.

But how does it behave in PL/SQL?

SYSDATE in PL/SQL

24 years ago, someone posted a puzzle in the forum of dbasupport.com that covered the same topic. I snitched the code from there and adapted it slightly.

6) SYSDATE endless-Loop?
set serveroutput on size unlimited
declare
   i binary_integer := 0;
begin
   while sysdate = sysdate loop
      i := i + 1;
   end loop;
   dbms_output.put_line('Left the loop after ' || i || ' iterations');
end;
/
Left the loop after 2957099 iterations


PL/SQL procedure successfully completed.

It appears to be an endless loop, but it is not. The two SYSDATE calls are evaluated independently, and eventually they return different results. Of course, the number of iterations varies. Therefore, the algorithm could be used as a kind of random number generator.

APPDATE in PL/SQL

Let’s use the deterministic variant of the APPDATE function in the same script.

7) APPDATE endless-Loop?
create or replace function appdate return date deterministic is
begin
  return sysdate;
end;
/

set serveroutput on size unlimited
declare 
   i binary_integer := 0;
begin
   while appdate = appdate loop
      i := i + 1;
   end loop;
   dbms_output.put_line('Left the loop after ' || i || ' iterations');
end;
/
Function APPDATE compiled

declare 
*
ERROR at line 1:
ORA-01426: numeric overflow
ORA-06512: at line 5

https://docs.oracle.com/error-help/db/ora-01426/


More Details :
https://docs.oracle.com/error-help/db/ora-01426/
https://docs.oracle.com/error-help/db/ora-06512/

Luckily, the maximum value of a binary integer is 2,147,483,647. With a NULL statement on line 12, the script would run endlessly. It looks like the APPDATE function is evaluated only once within the WHILE statement.

We told the PL/SQL compiler that the APPDATE function is deterministic. So the same input (none in this case) always produces the same output. This is a lie. However, the compiler is a nice component and trusts us. So it can come up with some optimisations to improve the runtime of our code and produce a solution that calls APPDATE only once. And boom, we have an endless loop.

Let’s verify this.

PLSQL_OPTIMIZE_LEVEL

The PL/SQL compiler knows the following four optimisation levels:

  • 0 = Oracle Database 9i style with some minor improvements
  • 1 = Various optimisations, does not move code, therefore required when debugging PL/SQL
  • 2 = More optimisations, including moving code around, this is the default in 19c/23ai
  • 3 = Even more optimisations

The documentation leaves us in the dark about what each level does exactly. However, one thing is sure. The higher the level, the more aggressive the optimisation.

So let’s try the previous code with level 0.

8) APPDATE endless-Loop with plsql_optimize_level 0?
alter session set plsql_optimize_level = 0;
create or replace function appdate return date deterministic is
begin
  return sysdate;
end;
/

set serveroutput on size unlimited
declare 
   i binary_integer := 0;
begin
   while appdate = appdate loop
      i := i + 1;
   end loop;
   dbms_output.put_line('Left the loop after ' || i || ' iterations');
end;
/
Session altered.


Function APPDATE compiled

Left the loop after 3224661 iterations


PL/SQL procedure successfully completed.

Ha, this worked. No endless loop. We get a similar result with level 1. However, levels 2 and 3 lead to an endless loop.

This example should illustrate that we should not lie to the compiler. Sooner or later, another optimisation in a newer version could break our code.

Conclusion

In fact, the previous paragraph could be a good conclusion. However, at the beginning of this post, I mentioned a real use case where we were evaluating an alternative to the faketime library. We opted for FIXED_DATE because it is simple to apply and worked for the application under test. The application uses SYSDATE exclusively for business-relevant dates and times. SYSTIMESTAMP, CURRENT_DATE, CURRENT_TIMESTAMP and LOCALTIMESTAMP, which are unaffected by FIXED_DATE, are used only for logging purposes.

The idea of providing a custom function that returns SYSDATE with a centrally defined offset might have worked in our case. But providing a solution that behaves like SYSDATE is much more challenging than I thought. We would need something that is evaluated once in a SQL statement and per call in other cases. Passing SYSDATE as a default parameter to a deterministic function does the trick. Either way, I’m glad we opted for the simpler solution, which meant there was no need to change the code base.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.