Re: local variable/column name conflict?

Based on your description, it appears that when a variable name is the same as a column name used in a WHERE clause, the column name takes precedence over the variable name.

Notice in your SQL statement the SELECT .... FROM test_table, the column name specified is assumed to be part of a table in the database. If is not, oracle looks for declared variable. So, when you change the varaible name to be different from the table column name, oracle correctly evaluates the statement using the variable name

This is just my reasoning! Someone else may have a different explanation. wozi_at_dhc.net

Brenda A Graham wrote in message ...
>In PL/SQL, I find that if a table column name and a local variable to
>which I compare it in a WHERE clause have the same exact name, the value
>in the local variable is irrelivant, but rather acts like a wild care in
>the WHERE clause.
>
>TABLE test_table has a COLUMN named code_name that is varchar2(5). Then...
>
>DECLARE
> code_name varchar2(5) := 'ZZZ%';
> kount integer;
>BEGIN
> SELECT count(*) into kount
> FROM test_table
> WHERE code_name like code_name;
>END;
>
>This query selects all rows in the table, reguardless of them matching
>the value stored in the local variable called code_name. By simply
>changing the vocal varieble name to be different from the column name,
>the restriction
>works correctly.
>
>Is this a known 'feature' of PL/SQL?
Received on Thu May 20 1999 - 02:13:28 CDT