Skip to Main Content
  • Questions
  • PLW-07206 compiler warning when assign parameter value to local date value

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Ernesto.

Asked: December 13, 2017 - 3:09 pm UTC

Last updated: December 15, 2017 - 6:46 am UTC

Version: 11.2

Viewed 1000+ times

You Asked

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"???

and Chris said...

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

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Yes, but...

Ernesto, December 14, 2017 - 3:49 pm UTC

First of all thanks for the answer and especially for the hint with the custom not nullable type.

You write that after assign the value I simply return it. Okay. But look at this modified version:
create or replace function next_day_start (
  p_date in date
) return date
  authid definer
is
  -- same plw warning
  l_date date not null:=p_date;
  -- no plw warning
--  l_date date:=p_date;
begin
  l_date:= trunc(l_date);
  return 1 + trunc(l_date);
end;


Inside the Begin block I make a lot of things. I call a function which will modify my (not null) date. But I still get the warning...

Very interesting: without the not null constraint in the declare section I don't get any warning.

But why?
Connor McDonald
December 15, 2017 - 6:46 am UTC

We'll pass it over to the PL/SQL product manager and report back

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library