How do I use variables in Oracle SQL Developer?

sql oracle variables declare
Nathan picture Nathan · Apr 13, 2011 · Viewed 387.4k times · Source

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?

Answer

Omphaloskopie picture Omphaloskopie · Jan 15, 2013

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)

Related questions

Declare a variable in Oracle SQL to use in a query

Hi I am trying to declare a variable to use in Oracle SQL select query as such: DECLARE myDate DATE; BEGIN SELECT Source as "Source", DT as "Date", Status as "Status", COALESCE("Count", 0) as "Count" FROM (Huge SubQuery that includes …

Read More
How to declare variable and use it in the same Oracle SQL script?

I want to write reusable code and need to declare some variables at the beginning and reuse them in the script, such as: DEFINE stupidvar = 'stupidvarcontent'; SELECT stupiddata FROM stupidtable WHERE stupidcolumn = &stupidvar; How can I declare a variable …

Read More
Get list of all tables in Oracle?

How do I query an Oracle database to display the names of all tables in it?

Read More