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.

possible duplicate of Error(2,7): PLS-00428: an INTO clause is expected in this SELECT statement - see Alex Poole's answer — Jeffrey Kemp
– Jeffrey Kemp, Commented Oct 15, 2013 at 4:02
Don't use a PL/SQL block. Simply use a bind variable. Go to Oracle Docs for more details: docs.oracle.com/cd/B10501_01/appdev.920/a96584/oci05bnd.htm — Rachcha
– Rachcha, Commented Oct 15, 2013 at 4:46
It is not clear where you want to initialize and use myDate. Can you update your question ? — igr
– igr, Commented Oct 15, 2013 at 6:22
Possible duplicate of Declaring & Setting Variables in a Select Statement — user565869
– user565869, Commented Jan 26, 2016 at 16:51

Hamidreza · Accepted Answer · 2013-10-15 06:12:54Z

3

try this:

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

and then your select with using :var in it

answered Oct 15, 2013 at 6:12

Hamidreza

3,1581 gold badge20 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ashish Kakkad · Accepted Answer · 2015-08-12 10:31:32Z

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;

edited Aug 12, 2015 at 10:31

Ashish Kakkad

23.9k13 gold badges108 silver badges140 bronze badges

answered Aug 12, 2015 at 10:23

Indika Herath

397 bronze badges

2 Comments

Aaron C Over a year ago

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

Indika Herath Over a year ago

Yes It is plain SQL

Collectives™ on Stack Overflow

Declare a variable in Oracle SQL to use in a query

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related