Define variables in SQL Developer

Question

Hi I am quite new to Oracle SQL Developer. It looks like a very simple thing, but it is driving me nuts.

DEFINE START_DATE = "to_date('03/01/2018', 'dd/mm/yyyy')";
DEFINE END_DATE = "to_date('01/06/2018', 'dd/mm/yyyy')";

SELECT  
    SOMETABLE.JOB_BASE_NUM,
    SOMETABLE.CUSTOMER_CODE
FROM
    SOMETABLE
WHERE              
    SOMETABLE.WORK_END_DATE>= &START_DATE
    AND SOMETABLE.WORK_END_DATE < &END_DATE;

It always ask me to Enter value for END_DATE:

Can someone help please? Thank you.

Edit: I run this in SQL Developer:

Are you running all the Lines in the script as one batch or just SELECT? — rs.
– rs., Commented Nov 16, 2018 at 19:39
By the way, Oracle supports the ANSI standard syntax for date literals, e.g. date '2018-01-03' — William Robertson
– William Robertson, Commented Nov 17, 2018 at 10:50

OptimusPrime · Accepted Answer · 2018-11-16 22:13:37Z

2

Remove the semicolon here:

DEFINE START_DATE = "to_date('03/01/2018', 'dd/mm/yyyy')"
DEFINE END_DATE = "to_date('01/06/2018', 'dd/mm/yyyy')"

The semicolon is separating your define variable from your sql.

answered Nov 16, 2018 at 22:13

OptimusPrime

3012 silver badges12 bronze badges

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

Collectives™ on Stack Overflow

Define variables in SQL Developer

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related