Skip to main content

Consider declaring suitable functions DETERMINISTIC

ZPA rule for PL/SQL and Oracle SQL · FunctionShouldBeDeterministic

The DETERMINISTIC keyword is a promise that a function returns the same result for the same input values. Declaring a suitable function deterministic can allow Oracle to reuse its result, but Oracle trusts the declaration rather than proving it. This rule therefore recommends the declaration only for a narrow, high-confidence subset of functions.

The recommendation is limited to functions with input-only parameters and an implementation made from local state, literal values, deterministic operators, simple control flow, and calls to no unknown routines. Database reads, side effects, external state, sequences, explicit exception behavior, and unsupported constructs suppress the recommendation. The only function calls currently recognized as safe are numeric NVL and one-argument character LTRIM, RTRIM, and simple TRIM calls with statically known argument types. The absence of an issue does not prove that a function is deterministic, and this rule does not provide complete exception-flow analysis.

This rule is not applied when Oracle Forms metadata is available. In that context, functions are local to a Forms module, so adding DETERMINISTIC does not provide the stored-database-function benefit targeted by this recommendation.

Examples​

Noncompliant Code Example
create function double_value(p_value in number) return number is -- Noncompliant
begin
return p_value * 2;
end;
Compliant Solution
create function double_value(p_value in number) return number deterministic is
begin
return p_value * 2;
end;

A function whose result depends on the current time, such as RETURN SYSDATE, is never recommended.