Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

My main skills are with SQL Server, but I have been asked to do some tuning of an Oracle query. I have written the following SQL:

declare @startDate int
select @startDate = 20110501

And I get this error:

declare @startDate int
select @startDate = 20110501
Error at line 1
ORA-06550: line 1, column 9:
PLS-00103: Encountered the symbol "@" when expecting one of the following:

   begin function package pragma procedure subtype type use
   <an identifier> <a double-quoted delimited-identifier> form
   current cursor

How do I declare and use variables in Oracle?

share|improve this question
The first step for creating variable is "read the documentation". You should Have a look at this and should consider reading the other documentations listed here too. – Benoit Jul 5 '11 at 12:15
up vote 15 down vote accepted

Inside pl/sql block:

 startdate number;
  select 20110501 into startdate from dual;

using a bind variable:

var startdate number;
  select 20110501 into :startdate from dual;

PL/SQL procedure successfully completed.

SQL> print startdate

share|improve this answer
Thanks, how do I use the variable in a query? Would I do select * from myTable where START_DATE > startDate;? – Mark Allison Jul 5 '11 at 10:59
select object_name from user_objects where created > to_date (:startdate,'yyyymmdd'); prefix the bind variable wïth ":" – ik_zelf Jul 5 '11 at 11:07

SQL*Plus supports an additional format:

DEFINE StartDate = TO_DATE('2016-06-21');
DEFINE EndDate   = TO_DATE('2016-06-30');

    DateField BETWEEN &StartDate and &EndDate;

Note the ampersands where the substitutions are to be performed within the query.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.