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

1 Answer 1

up vote 8 down vote accepted

Inside pl/sql block:

declare
 startdate number;
begin
  select 20110501 into startdate from dual;
end;
/

using a bind variable:

var startdate number;
begin
  select 20110501 into :startdate from dual;
end;
/

PL/SQL procedure successfully completed.

SQL> print startdate

 STARTDATE
----------
  20110501
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

Your Answer

 
discard

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.