Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
add comment

2 Answers

up vote 7 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;? –  Moo MinTroll 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
add comment

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.

share|improve this answer
Thanks for the links, useful. –  Moo MinTroll Jul 5 '11 at 12:49
While the links were useful, the tone maybe not so much. Also, in the future, just a tad more about what the link is pointing to and something of what the usefulness is would be good (but isn't required so make of that what you will). Consider it "if the documentation wasn't here in two weeks, what would someone else reading this gather from where I was linking them so they could figure it out themselves via google if push came to shove?". –  jcolebrand Jul 6 '11 at 3:51
add comment

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.