PLW-05001: previous use of 'string' (at line string) conflicts with this use - Oracle

This warning will make itself heard when you have declared more than one variable or constant with the same name. It can also pop up if the parameter list of a program defined in a package specification is different from that of the definition in the package body.

You may be saying to yourself: I’ve seen that error before, but it is a compilation error, not a warning. And, in fact, you are right, in that the following program simply will not compile:

  CREATE OR REPLACE PROCEDURE plw5001
  IS
    a  BOOLEAN;
    a  PLS_INTEGER;
  BEGIN
    a := 1;
    DBMS_OUTPUT.put_line ('Will not compile');
  END plw5001;
  /

You receive the following compile error: PLS-00371: at most one declaration for 'A' is permitted in the declaration section.

So why is there a warning for this situation? Consider what happens when I remove the assignment to the variable named a:

  SQL> CREATE OR REPLACE PROCEDURE plw5001
    2  IS
    3     a   BOOLEAN;
    4     a   PLS_INTEGER;
    5  BEGIN
    6     DBMS_OUTPUT.put_line ('Will not compile?');
    7  END plw5001;
    8  /
  Procedure created.

The program compiles! Oracle does not flag the PLS-00371 because I have not actually used either of the variables in my code. The PLW-05001 warning fills that gap by giving us a heads-up if we have declared, but not yet used, variables with the same name, as you can see here:

  SQL> ALTER PROCEDURE plw5001 COMPILE plsql_warnings = 'enable:all';
  SP2-0805: Procedure altered with compilation warnings
  SQL> SHOW ERRORS
  Errors for PROCEDURE PLW5001:

  LINE/COL ERROR
  -------- -------------------------------
  4/4      PLW-05001: previous use of
           'A' (at line 3) conflicts with
           this use

PLW-05003: same actual parameter(string and string) at IN and NOCOPY may have side effects

When you use NOCOPY with an IN OUT parameter, you are asking PL/SQL to pass the argument by reference, rather than by value. This means that any changes to the argument are made immediately to the variable in the outer scope. “By value” behavior (NOCOPY is not specified or the compiler ignores the NOCOPY hint), on the other hand, dictates that changes within the program are made to a local copy of the IN OUT parameter. When the program terminates, these changes are then copied to the actual parameter. (If an error occurs, the changed values are not copied back to the actual parameter.)

Use of the NOCOPY hint increases the possibility that you will run into the issue of argument aliasing, in which two different names point to the same memory location. Aliasing can be difficult to understand and debug; a compile-time warning that catches this situation will come in very handy.

Consider this program:

  /* File on web: plw5003.sql */
  CREATE OR REPLACE PROCEDURE very_confusing (
     arg1  IN               VARCHAR2
   , arg2  IN OUT           VARCHAR2
   , arg3  IN OUT NOCOPY    VARCHAR2
  )
  IS
  BEGIN
     arg2 := 'Second value';
     DBMS_OUTPUT.put_line ('arg2 assigned, arg1 = ' || arg1);
     arg3 := 'Third value';
     DBMS_OUTPUT.put_line ('arg3 assigned, arg1 = ' || arg1);
  END;
  /

It’s a simple enough program. pass in three strings, two of which are IN OUT; assign values to those IN OUT arguments; and display the value of the first IN argument’s value after each assignment.

Now I will run this procedure, passing the very same local variable as the argument for each of the three parameters:

  SQL> DECLARE
    2     str   VARCHAR2 (100) := 'First value';
    3  BEGIN
    4    DBMS_OUTPUT.put_line ('str before = ' || str);
    5    very_confusing (str, str, str);
    6    DBMS_OUTPUT.put_line ('str after = ' || str);
    7  END;
    8  /
  str before = First value
  arg2 assigned, arg1 = First value
  arg3 assigned, arg1 = Third value
  str after = Second value

Notice that while still running very_confusing, the value of the arg1 argument was not affected by the assignment to arg2. Yet when I assigned a value to arg3, the value of arg1 (an IN argument) was changed to “Third value”! Furthermore, when very_confusing terminated, the assignment to arg2 was applied to the str variable. Thus, when control returned to the outer block, the value of the str variable was set to “Second value”, effectively writing over the assignment of “Third value”.

As I said earlier, parameter aliasing can be very confusing. So, if you enable compile-time warnings, programs such as plw5003 may be revealed to have potential aliasing problems:

  SQL> CREATE OR REPLACE PROCEDURE plw5003
    2  IS
    3     str VARCHAR2 (100) := 'First value';
    4  BEGIN
    5     DBMS_OUTPUT.put_line ('str before = ' || str);
    6     very_confusing (str, str, str);
    7     DBMS_OUTPUT.put_line ('str after = ' || str);
    8  END plw5003;
    9  /

  SP2-0804: Procedure created with compilation warnings

  SQL> sho err
  Errors for PROCEDURE PLW5003:

  LINE/COL ERROR 
  -------- ---------------------------------
  6/4      PLW-05003: same actual parameter
           (STR and STR) at IN and NOCOPY
           may have side effects

  6/4      PLW-05003: same actual parameter
           (STR and STR) at IN and NOCOPY
           may have side effects