Declare a variable in Oracle SQL to use in a query

sql oracle variables select declare
Kairan picture Kairan · Oct 15, 2013 · Viewed 40.1k times · Source

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 many WHERE date between x and y);
END;

I need to use myDate for the query so I dont have to update it in 10 places everytime I run the query. Basically its just for declaring a variable that can be used in a where date is between clause in several places.

Answer

Hamidreza picture Hamidreza · Oct 15, 2013

try this:

variable var DATE
exec :var := '15-OCT-13'

and then your select with using :var in it

Related questions

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
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 …

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