How to solve SP2-0552: Bind variable not declared?

Question

When I run this PL/SQL block:

SQL> set serveroutput on
SQL> declare
  2  v_max_sal NUMBER(20)
  3  begin
  4  select max(sal) INTO :v_max_sal
  5  from emp e, dept d
  6  where e.deptno=d.deptno
  7  and d.dname='SALES';
  8  END;
  9  /

it throws to me the next error: SP2-0552: Bind variable "V_MAX_SAL" not declared. What am I missing or doing wrong?

Answer 1

If you definitely want a bind variable then you need to declare it outside the block:

variable v_max_sal number;

begin
  select max(sal)
  into :v_max_sal
  from dept d
  join emp e
  on e.deptno=d.deptno
  where d.dname='SALES';
end;
/

print v_max_sal

Notice the SQL*Plus client variable and print commands, and that there is no longer a declare section in the block, as you don't now have or need a local PL/SQL variable. A local variable can act as a bind variable when it's used in a query - the parser sees it like that, and you'll see a placeholder in the query's plan - but it's not quite the same thing, as you usually want the bind variable to be referencable outside the Pl/SQL code.

I've also used modern join syntax, though that isn't relevant to the problem.