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
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
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';
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 */
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:
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
SP2-0804: Procedure created with compilation warnings
SQL> sho err
6/4 PLW-05003: same actual parameter
blog comments powered by Disqus