No, SYSDATE<\/a> is not deterministic. However, it behaves very similarly to a deterministic function within an Oracle Database SQL statement.<\/p>\n\n\n\n On a customer site, we used faketime<\/a> 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.<\/p>\n\n\n\n We were exploring alternatives to travel in time only within the Oracle Database. One option was to set FIXED_DATE<\/a>, and another to replace calls to But before that, let’s look at some definitions.<\/p>\n\n\n\n Wikipedia<\/a> defines a deterministic algorithm in computer science as follows:<\/p>\n\n\n\n In computer science, a deterministic algorithm is an algorithm that, given a particular input, will always produce the same output<\/strong>, 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.<\/p>\n<\/blockquote>\n\n\n\n The Oracle Database PL\/SQL Language Reference defines deterministic for the deterministic_clause<\/a> as follows:<\/p>\n\n\n\n There are two ways to check if a function is deterministic. Firstly, you can query the data dictionary like this:<\/p>\n\n\n\nBackground<\/h2>\n\n\n\n
SYSDATE<\/code>, SYSTIMESTAMP<\/code>, CURRENT_DATE<\/code>, CURRENT_TIMESTAMP<\/code> and LOCALTIMESTAMP<\/code> with custom functions that added an offset to the current date\/time. For the latter, I’d like to share some simplified test results.<\/p>\n\n\n\nDefinitions<\/h2>\n\n\n\n
\n
\n
DETERMINISTIC<\/code> clause appears in either a declaration or the definition of the function.<\/li>\n\n\n\nDETERMINISTIC<\/code> clause may appear at most once in a function declaration and at most once in a function definition.<\/li>\n\n\n\nDETERMINISTIC<\/code> function may not have side effects<\/a>.<\/li>\n\n\n\nDETERMINISTIC<\/code> function may not raise an unhandled exception.<\/li>\n\n\n\nDETERMINISTIC<\/code> clause violates any of these semantic rules, the results of its invocation, its value, and the effect on its invoker are all undefined.<\/li>\n<\/ul>\n\n\n\nHow to Check?<\/h2>\n\n\n\n