8

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.

Kairan
  • 5,342
  • 27
  • 65
  • 104
  • 2
    possible duplicate of [Error(2,7): PLS-00428: an INTO clause is expected in this SELECT statement](http://stackoverflow.com/questions/4710471/error2-7-pls-00428-an-into-clause-is-expected-in-this-select-statement) - see Alex Poole's answer – Jeffrey Kemp Oct 15 '13 at 04:02
  • Don't use a PL/SQL block. Simply use a bind variable. Go to Oracle Docs for more details: http://docs.oracle.com/cd/B10501_01/appdev.920/a96584/oci05bnd.htm – Rachcha Oct 15 '13 at 04:46
  • It is not clear where you want to initialize and use myDate. Can you update your question ? – igr Oct 15 '13 at 06:22
  • 1
    Possible duplicate of [Declaring & Setting Variables in a Select Statement](http://stackoverflow.com/questions/3400058/declaring-setting-variables-in-a-select-statement) –  Jan 26 '16 at 16:51

2 Answers2

3

try this:

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

and then your select with using :var in it

Hamidreza
  • 3,038
  • 1
  • 18
  • 15
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;
Ashish Kakkad
  • 23,586
  • 12
  • 103
  • 136
Indika Herath
  • 29
  • 7