How to avoid misleading plsql warning PLW-07206: analysis suggests that the assignment to ‘V_DUMMY’ may be unnecessary

PLW-messages are warnings issued by the PL/SQL compiler.

The PLW-07206 warning tells us that we have some code (an assignment statement) that is not needed. Usually because the target of the assignment is not used later anywhere.

PLW-07206: analysis suggests that the assignment to <something> may be unnecessary

Problem demonstration

Example 1)

Here is a very reduced demonstration of the issue.

First we enable the PL/SQL warnings. I assume this setting is still in place for all following examples.

-- set plsql warning on
alter session set PLSQL_WARNINGS = 'ENABLE:ALL';

In SQL Developer we can set the warning level in the preferences, but this setting is not applied to code running in a SQL worksheet as a script.

Then a function is created.

create or replace function dummy_existsCheck return boolean 
authid definer
is
      v_dummy number(1);
begin
      select 1
      into v_dummy
      from all_objects
      where 1=2;
      return true;  
      -- Do something here if a record was found
exception  
     when no_data_found then
      -- Do something else here if NO record was found
      return false;
end dummy_existsCheck;
/  

Compiling the function shows the warning.

Function DUMMY_EXISTSCHECK compiled
LINE/COL ERROR

6/7 PLW-07206: analysis suggests that the assignment to 'V_DUMMY' may be unnecessary
Example 2)

Here is an example where the warning is correctly added.

create or replace function dummy_existsCheck return boolean 
authid definer
is
   v_dummy varchar2(1);
begin
   v_dummy := 'B';
     
   -- Do something here without using v_dummy
   return null; 
end dummy_existsCheck;
/ 
Function DUMMY_EXISTSCHECK compiled
LINE/COL ERROR

6/5 PLW-07206: analysis suggests that the assignment to 'V_DUMMY' may be unnecessary

The difference between the two examples is that a SELECT statement in PL/SQL needs an INTO clause. And because of that this dummy variable is needed. So the warning in the first example is wrong.

The compiler is aware of this situation already. For some strange reason the warning goes away when we change the datatype of the variable from number to varchar2. Which is also the suggested workaround.

Workaround for example 1)
create or replace function dummy_existsCheck return boolean 
authid definer
is
      v_dummy varchar(1);
begin
      select 'x'
      into v_dummy
      from all_objects
      where 1=2;
      return true;  
      -- Do something here if a record was found
exception  
     when no_data_found then
      -- Do something else here if NO record was found
      return false;
end dummy_existsCheck;
/  

Function DUMMY_EXISTSCHECK compiled

No warning issued.

Obviously this is a bug. Although a very minor one.

Example 3)

Here is another slightly more complex example. The same message appears when we assign the result of a function. Because a function always has a return value, we need to assign it to some variable, even if the variable later in the code is not used anymore.

create or replace function dummy_getID(p_table in varchar2) return number
authid definer
is
      v_result number(1);
begin
      select 1
      into v_result
      from user_tables
      where table_name = p_table;
      
      return v_result;
end dummy_getID;
/  

create or replace function dummy_existsCheck return boolean
authid definer
is
  v_tab varchar2(30) := 'DUMMY';
  v_dummy number(1);
begin
  v_dummy := dummy_getID(v_tab);
  return true;
exception
  when no_data_found then
    dbms_output.put_line('Table '||v_tab||' does not exist.');
    return false;
end dummy_existsCheck;
/ 

Function DUMMY_GETID compiled
Function DUMMY_EXISTSCHECK compiled
LINE/COL ERROR

7/3 PLW-07206: analysis suggests that the assignment to 'V_DUMMY' may be unnecessary
Workaround for example 3)

Again, changing the variable to a varchar2 data type, avoids the warning.

create or replace function dummy_existsCheck return boolean
authid definer
is
  v_tab varchar2(30) := 'DUMMY';
  v_dummy varchar2(1);
begin
  v_dummy := dummy_getID(v_tab);
  return true;
exception
  when no_data_found then
    dbms_output.put_line('Table '||v_tab||' does not exist.');
    return false;
end dummy_existsCheck;
/ 
Function DUMMY_EXISTSCHECK compiled

Datatype check

Surprisingly other numeric data types also avoid the warning. It seems that it happens mostly for number(x).

The following data types did not produce the warning:

  • varchar2(1)
  • varchar(1)
  • char(1)
  • pls_integer
  • binary_integer
  • number
  • float

The following data types did produce the warning:

  • number(1)
  • number(38)
  • decimal
  • integer
  • integer(1)
  • simple_integer

Conclusion

Compiler warnings are a good thing. Wrong or misleading warnings are not.

PLW-07206 can (sometimes) be avoided by changing the data type of the assignment target from number(x) to some other data type.

Cleanup demo code
drop function dummy_existsCheck;
drop function dummy_getID;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.