How do I use variables in Oracle SQL Developer?

Below is an example of using variables in SQL Server 2000.

DECLARE @EmpIDVar INT

SET @EmpIDVar = 1234

SELECT *
FROM Employees
WHERE EmployeeID = @EmpIDVar

I want to do the exact same thing in Oracle using SQL Developer without additional complexity. It seems like a very simple thing to do, but I can't find a simple solution. How can I do it?

I am using the SQL-Developer in Version 3.2. The other stuff didn't work for me, but this did:

define value1 = 'sysdate'

SELECT &&value1 from dual;

Also it's the slickest way presented here, yet.

(If you omit the "define"-part you'll be prompted for that value)

There are two types of variable in SQL-plus: substitution and bind.

This is substitution (substitution variables can replace SQL*Plus command options or other hard-coded text):

define a = 1;
select &a from dual;
undefine a;

This is bind (bind variables store data values for SQL and PL/SQL statements executed in the RDBMS; they can hold single values or complete result sets):

var x number;
exec :x := 10;
select :x from dual;
exec select count(*) into :x from dual;
exec print x;

SQL Developer supports substitution variables, but when you execute a query with bind :var syntax you are prompted for the binding (in a dialog box).

Reference:

• http://www.oracle.com/technetwork/testcontent/sub-var-087723.html SQL*Plus Substitution Variables, Christopher Jones, 2004

UPDATE substitution variables are a bit tricky to use, look:

define phone = '+38097666666';
select &phone from dual; -- plus is stripped as it is a number
select '&phone' from dual; -- plus is preserved as it is a string

In SQL*Plus, you can do something very similar

SQL> variable v_emp_id number;
SQL> select 1234 into :v_emp_id from dual;

      1234
----------
      1234

SQL> select *
  2    from emp
  3   where empno = :v_emp_id;

no rows selected

In SQL Developer, if you run a statement that has any number of bind variables (prefixed with a colon), you'll be prompted to enter values. As Alex points out, you can also do something similar using the "Run Script" function (F5) with the alternate EXEC syntax Alex suggests does.

variable v_count number;
variable v_emp_id number;
exec :v_emp_id := 1234;
exec select count(1) into :v_count from emp;
select *
  from emp
 where empno = :v_emp_id
exec print :v_count;