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?
 
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
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.