Bug: refactoring accidentally renames table columns

[Claus Pedersen]

I have the following piece of code:

CREATE PROCEDURE test AS
  deptno emp.deptno%TYPE;
BEGIN
  SELECT deptno
  INTO   deptno
  FROM   emp
  WHERE  empno = 7369;
  dbms_output.put_line (deptno);
END;

It outputs 20 as expected.

I now refactor the variable deptno and rename it into my_deptno.

The first thing I notice is that four instances of deptno are highlighted including the table column name. This is also renamed so my resulting code now looks like:

CREATE PROCEDURE test AS
  my_deptno emp.deptno%TYPE;
BEGIN
  SELECT my_deptno
  INTO   my_deptno
  FROM   emp
  WHERE  empno = 7369;
  dbms_output.put_line (my_deptno);
END;

Now the method has ceased to function correctly, it outputs NULL beause the variable is now selected into itself.

The refactoring must newer rename a table column. If PLD can not handle expressions like the above correctly, it is better off leaving them untouched, this way giving compile errors, and then it is up to the user to correct the code manually.

It was pure luck that our code looking like the one stated above was not put into production before we discovered the error.

The code compiles beautifully, so no one in the development team expected any problems.

This error can cause quite critical errors, so I suggest that is is corrected ASAP, preferably already in the existing version 7.1.4.

My current version is 7.1.4.1391

 

[Marco Kalter]

We'll try to fix it.

 

[Stew Stryker]

Claus,

I would (gently) say that you're asking for trouble using a local variable (or cursor parameter) with the same name as a table column in that procedure. I've been burned by this just recently, so am more careful than before.

I agree that Refactoring shouldn't rename columns from your query, but I'm guessing that would require a major rewrite.

 

[Claus Pedersen]

Hello Stew.

Thanks for your comment.

Yes, I know this was an example of bad coding practice.

But it is an example from the real life (scale 1:1), I was in the process of refactoring old code when this happened.

Now we have a coding standard that says to prefix all parameters, types, constants, exceptions, variables, cursors etc. with certain letters, so they will not be confused with other names in the database.