Please look at this small function:
create or replace function next_day_start (
p_date in date
) return date
authid definer
is
l_date date not null:=p_date;
begin
return 1 + trunc(l_date);
end;
The idea to assign the parameter first to a local variable before do anything with it. If the caller for example pass a NULL value (or any other not allowed value) then the error is typically a numeric or value error and the caller should handle the error.
What is the reason why Oracle gives here the PLW-07206 warning message "analysis suggests that the assignment to 'L_DATE' may be unnecessary"???
You're getting this because the only thing you do after assigning a value to the variable is return it. i.e. you don't make any further changes to its value.
As the docs say:
Cause: This assignment may be unnecessary; the PL/SQL optimizer could not find any further use of the variable which was being set. If the assignment was being done for some side-effect such as raising a specific exception, the compiler may not have been able to understand the side-effect and this warning may be inappropriate.
Action: If the assignment sets a variable whose value will not be used again and there are no side-effects (exceptions or calls) to consider, remove the assignment for better performance. If the assignment was in place to raise a specific known exception, replace the assignment with a RAISE statement.This is one of the instances where it's unable to detect a side-effect. Notably that the code raises an exception if the parameter is null.
If the warning is annoying you, there is a way around it.
PL/SQL parameters inherit not null constraints from their types. So you can declare a subtype as not null. And use that in your parameter definition:
create or replace package pkg as
subtype date_nn is date not null;
end;
/
create or replace function next_day_start (
p_date in pkg.date_nn
) return date
authid definer
is
begin
return 1 + trunc(p_date);
end;
/
select next_day_start(null) from dual;
SQL Error: ORA-06553: PLS-567: cannot pass NULL to a NOT NULL constrained formal parameter