Skip to main content

Deterministic functions should not depend on external state

ZPA rule for PL/SQL and Oracle SQL · DeterministicFunction

The DETERMINISTIC keyword is a promise that a function returns the same result for the same input values. Oracle trusts this declaration and does not verify that the implementation satisfies it. An incorrect declaration can therefore allow queries to use stale or incorrect results.

This rule reports deterministic functions whose implementation contains clear evidence of a dependency on changing time, session or sequence state, reads mutable database data, or performs a side effect. The analysis is intentionally conservative and does not prove that an unreported function is deterministic. It does not infer impurity from calls to user-defined routines, unresolved package or global state, or NLS-sensitive expressions. For package functions, the declaration must be DETERMINISTIC in the matched package specification.

Examples​

Noncompliant Code Example
create function today_code return varchar2 deterministic is -- Noncompliant
begin
return to_char(sysdate, 'YYYYMMDD');
end;
Compliant Solution
create function today_code return varchar2 is
begin
return to_char(sysdate, 'YYYYMMDD');
end;