asked on

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'))

slightwv (䄆 Netminder)

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'))

dkilby

ASKER

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.

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kelvin Sparks

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

Kelvin Sparks

Typo

Note the colon just before the = sign.

Kelvin Sparks

Also - semi-colon at end of each line. Oracle insists on that, SQL its optional - for now - but coming.

Kelvin

dkilby

ASKER

Thank you for your help