Declare a variable in Oracle SQL to use in a query

Question

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.

Solution 1

try this:

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

and then your select with using :var in it

Solution 2

I have found a way to add variables for the sql query as follows

DEFINE RES_DT = TO_DATE('11-AUG-15');

And also to access the variable through the query we have to use '&' notation as follows

select * from customer where assign_date = &RES_DT;

Comments

[Aaron C]

Are you using Query Manager? Or is this just plain SQL?

[Indika Herath]

Yes It is plain SQL