Home arrow Oracle arrow Page 2 - Compile-Time Warnings for Oracle DB 10g

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

In this fourth part of a nine-part series on managing PL/SQL code, you will learn about the compile-time warnings that were introduced in Oracle Database 10g Release 1. This article is excerpted from chapter 20 of the book Oracle PL/SQL Programming, Fourth Edition, written by Steven Feuerstein and Bill Pribyl (O'Reilly; ISBN: 0596009771). Copyright © 2006 O'Reilly Media, Inc. All rights reserved. Used with permission from the publisher. Available from booksellers or direct from O'Reilly Media.

TABLE OF CONTENTS:
  1. Compile-Time Warnings for Oracle DB 10g
  2. PLW-05001: previous use of 'string' (at line string) conflicts with this use
  3. PLW-05004: identifier string is also declared in STANDARD or is a SQL built-in
  4. PLW-05005: function string returns without value at line string
By: O'Reilly Media
Rating: starstarstarstarstar / 2
November 08, 2007

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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



 
 
>>> More Oracle Articles          >>> More By O'Reilly Media
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: