Oracle PL/SQL User input error "Bind variable not declared"

Question

accept x char prompt 'Please enter something: '
declare 
  a varchar2(50);
begin
  a := '&x';
  Select MIN(lowest_elevation) as lowest , Max(highest_elevation)as highest FROM countries where country_name = :a;
end;           

i am taking input from a user then passing it into a where clause. and i cant seem to find any solutions online. I don't know what i am doing wrong. Thank you in advance.

Answer 1

a is a PL/SQL local variable, not a SQL bind variable, so it should not have the colon when you refer to it:

where country_name = a;

For it to be a bind variable it would need to be defined by the client/application/IDE; in SQL Developer, for instance:

accept x char prompt 'Please enter something: '
variable a varchar2(50);

begin
  :a := '&x';
  Select MIN(lowest_elevation) as lowest , Max(highest_elevation)as highest
  FROM countries
  where country_name = :a;
end; 

Note that now there is no declare section, as you are no longer declaring a as a PL/SQL variable - it's a client variable. To assign the substitution variable &x to it it then needs the colon, :a := '&x';, because it's a bind variable there too.

I'm not saying that's what you should do; just showing the difference.

You can also skip the a variable completely, of course:

accept x char prompt 'Please enter something: '

begin
  Select MIN(lowest_elevation) as lowest , Max(highest_elevation)as highest
  FROM countries
  where country_name = '&x';
end;

Incidentally, you have to select into something, so your code still won't work as it's currently written.