Variables in where clause in Oracle PL/SQL Developer

Question

In ORACLE PL/SQL DEVELOPER, am I able to do the following as I could easily do in SQL Server?

DECLARE @EmpIDVar INT

SET @EmpIDVar = 1234

SELECT *
FROM Employees
WHERE EmployeeID = @EmpIDVar

This seems like such a simple thing and yet it seems impossible to do in Oracle!

I know about using & in front of variables that will prompt me to enter their values but why can't I just do something like the above?

score 0 · Answer 1 · answered Mar 05 '14 at 08:24

0

Declare Using VARIABLE command

VARIABLE EmpIDVar NUMBER;

Set the value Using EXEC like this

EXEC :EmpIDVar := 1234;

Run the Query prefixing the variable with colon

SELECT *
FROM Employees
WHERE EmployeeID = :EmpIDVar;

answered Mar 05 '14 at 08:24

Maheswaran Ravisankar

17,652
6
47
69

@user3359423 It works with `SQL*Plus`.. For `PL/SQL Developer`, you can try [an answer here](http://stackoverflow.com/questions/14887848/how-to-substitute-bind-variable-in-pl-sql-developer-tool) – Maheswaran Ravisankar Mar 07 '14 at 09:19
Tried the other answer and I couldn't get it to work. It seems that the only way you can do this in PL SQL Developer is to just use substitution variables. ie. put a & in place of where you put your variable and PL/SQL developer will substitute for you for e.g. select * from employees where EmployeeId = &EmployeeId – user3359423 Mar 10 '14 at 07:20
Yes, but that is not executed as bind variable.. it is just like a macro. String is formed dynamically. – Maheswaran Ravisankar Mar 10 '14 at 07:31

score 0 · Answer 2 · answered Nov 13 '15 at 15:17

Using Variable and select with IN clause, perhaps is more useful.

VARIABLE EmpIDVar NUMBER
DEFINE EmpIDVar ="123, 124"
EXEC : EmpIDVar := '& EmpIDVar'

SELECT 
  E.*
FROM Employees E
WHERE EmployeeID IN ( & EmpIDVar )

If not, simple replace IN with "=" and "123, 124" with "123".

score 0 · Answer 3 · edited May 23 '17 at 12:01

Hi here you can see another answer

How to use variables in an Oracle PL/SQL where clause

 set serveroutput on

 DECLARE 
    EmpIDVar integer; 
    Result integer;

  BEGIN
     EmpIDVar := 2000;


     SELECT Employees_id into Result      
     FROM Employees 
     WHERE Employees_ID = EmpIDVar ;

     dbms_output.Put_line(Result); 
  end;

if you wanted to show all(*) you would need, to show all variables , so like i said you need as much varialbes as you fields have.

Variables in where clause in Oracle PL/SQL Developer

3 Answers3