Skip to main content

Columns should not shadow parameters or variables in SQL conditions

ZPA rule for PL/SQL and Oracle SQL · ColumnHidesPlSqlName

Inside a SQL statement, a column of a source in scope takes an unqualified name before a parameter, variable or constant of the same name. When a condition or the value of a SET means the PL/SQL name, Oracle reads the column instead: the condition compares the column with itself, and the SET writes the column back unchanged. The code compiles and runs, but it affects the wrong rows.

This rule reports such a name only when the column is proven to exist: in the table and view metadata read from the database, or in a table the analyzed sources create. A name in the select list, ORDER BY, GROUP BY, an INTO target or the VALUES of a single-row INSERT is not reported.

Examples​

Noncompliant Code Example
create procedure drop_crates(shelf_id number) is
begin
delete from crate where crate.shelf_id = shelf_id; -- Noncompliant
end;
Compliant Solution
create procedure drop_crates(shelf_id number) is
begin
delete from crate where crate.shelf_id = drop_crates.shelf_id;
end;