substitution variable in where clause in pl/sql

[praveenakbgm]

Hello all,
when i jot, select &col from emp where &name = 'JOHN'; on the SQL> prompt, i am asked for the input for col and name and when i provide the following, the query works perfectly.
But when i write it in a procedure and substitute the &col and &name with some variables and run the procedure.. there is no results fetched. the &name when replaced with a variable, is considered as a value and not as a column name...
I want the program to consider it as column name! Please help me with the solution.

Eg:
declare
coladdress varchar2(20) := 'coladdress';
colname varchar2(20) := 'colname';
address varchar2(20);
Begin
select coladdress into address from emp where colname = 'JOHN';
dbms_output.put _line(address);
end;
/

[debasisdas]

try the following code.

[CODE=oracle]declare
cname varchar2(20) := '&cname';
address varchar2(20);
Begin
select coladdress into address from emp where colname = cname;
dbms_output.put _line(address);
end;[/CODE]

hope that solves your problem.

[praveenakbgm]

thanks for the reply but.........
i am deciding the column name in where clause dynamically. so i am passing the value to the column name variable dynamically. u r reply program will ask a user input during execution, which i do not want! i want to decide the where clause column name dynamically.