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?

Solution 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.

Solution 2

Note also, that a typical usage of bind variables covers the literal string in the WHERE predicate, so you may additionally replace the department name with it (to be able use the same query for all departments).

VARIABLE v_max_sal NUMBER;
VARIABLE v_dname VARCHAR2(14);

begin
:v_dname := 'SALES';

select max(sal) INTO :v_max_sal
from scott.emp e, scott.dept d
where e.deptno=d.deptno
and d.dname = :v_dname;
END;
/

print v_max_sal;

You may also simple remove the colon before the variable and it will work too:

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

Note that this variant and the solution proposed by @Alex lead to an identical query in the database:

 SELECT MAX(SAL) FROM SCOTT.EMP E, SCOTT.DEPT D WHERE E.DEPTNO=D.DEPTNO AND D.DNAME='SALES'

So in the database no bind variable is used.

The extended option in the begin of this answer leads to a query with a BV in the WHERE clause:

 SELECT MAX(SAL) FROM SCOTT.EMP E, SCOTT.DEPT D WHERE E.DEPTNO=D.DEPTNO AND D.DNAME = :B1

Solution 3

Lose the : in front of v_max_sal on your select ... into statement:

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  /

Comments

[Ionut]

I must use bind variable, so can't replace the " : ".

[Marmite Bomber]

Please reformulate the question; what is youf goal.