HiveBrain v1.2.0
Get Started
← Back to all entries
snippetMajor

How do I declare and use variables in Oracle?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
oracledeclarevariableshowanduse

Problem

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
     form
   current cursor


How do I declare and use variables in Oracle?

Solution

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


in a query:

select object_name 
from user_objects 
where created > to_date (:startdate,'yyyymmdd');  /*prefix the bind variable wïth ":" */

Code Snippets

declare
 startdate number;
begin
  select 20110501 into startdate from dual;
end;
/
var startdate number;
begin
  select 20110501 into :startdate from dual;
end;
/
SQL> print startdate

 STARTDATE
----------
  20110501
select object_name 
from user_objects 
where created > to_date (:startdate,'yyyymmdd');  /*prefix the bind variable wïth ":" */

Context

StackExchange Database Administrators Q#3652, answer score: 23

Revisions (0)

No revisions yet.