Enhancement Req: warning for same column name and variable name

[Kent]

Note this code:

startDate date := trunc(sysdate) - 30;

begin
select count(*) from tableexample t
where t.startdate >= startDate;
end;

This is intended to count all entries in the table where the startdate column is greater than the value of the startDate variable. But Oracle will use the column value for both sides of the comparison, and return all rows, since a column always equals itself.
I know the solution is to not name your variables (or parameters) the same as your columns, but when you work with many tables and packages written by a team, it is bound to happen occasionally. And when it does the query actually runs fine so you don't discover the problem until you start getting unexpected data results, and then the problem can be hard to pinpoint.

It would be very helpful if PL/SQL developer could produce a warning when compiling a procedure (or function, package, etc.) that has a query containing a table column that is named the same as a variable or parameter in that procedure.

Thanks.

 

[Roeland]

Although I think your proposition have some merits, I rather think that you better could make a guideline in your company to start variable's with some predefined sentence. This will also improve the readability of your code.

For example start all variables with 'v_' or 'm' ...