Oracle SQL Developer - using Variables

I want to use some variables in Oracle SQL Developer - i know how to use them with MS SQL and want to do something similar to the below

Declare @startDate datetime,
@endDate datetime

select @startDate = '01/01/2015',
@endDate = '03/01/2015'

In my Oracle SQL Statment i want to use the variables in this piece of code:

and (call_dt >= to_date('3/1/2015','mm/dd/yyyy') and call_dt <= to_date('3/28/2015','mm/dd/yyyy'))
dkilbyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Two ways:

and (call_dt >= to_date('&start_date','mm/dd/yyyy') and call_dt <= to_date('&end_date','mm/dd/yyyy'))

Or run as script (I believe F5):
undefine start_date
undefine end_date
accept start_date prompt 'Enter start date: '
accept end_date prompt 'Enter end date: '

and (call_dt >= to_date('&start_date','mm/dd/yyyy') and call_dt <= to_date('&end_date','mm/dd/yyyy'))
0
dkilbyAuthor Commented:
If i use

and (call_dt >= to_date('&start_date','mm/dd/yyyy') and call_dt <= to_date('&end_date','mm/dd/yyyy'))

can i enter the values at the beginning of the query? so i dont have to enter them in the popup box?  as i want to use the variables in several places throughout a large query.
0
slightwv (䄆 Netminder) Commented:
You can use define similar to what you are used to.  Still run as script.

define start_date= '01/01/2015
define end_date='03/01/2015'

...
and (call_dt >= to_date('&start_date','mm/dd/yyyy') and call_dt <= to_date('&end_date','mm/dd/yyyy'))
...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Kelvin SparksCommented:
The big difference between SQL Server and Oracle is that you normal don't use the @

For Oracle

DECLARE v_startDate DATE,
                 v_EndDate  DATE

v_startDate := '01/01/2015';
v_End_date := ''03/01/2015'

Note the colomn just before the = sign.


Kelvin
0
Kelvin SparksCommented:
Typo

Note the colon just before the = sign.
0
Kelvin SparksCommented:
Also - semi-colon at end of each line. Oracle insists on that, SQL its optional - for now - but coming.


Kelvin
0
dkilbyAuthor Commented:
Thank you for your help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.