snippetMajor
How do I declare and use variables in Oracle?
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:
And I get this error:
How do I declare and use variables in Oracle?
declare @startDate int
select @startDate = 20110501And 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 cursorHow do I declare and use variables in Oracle?
Solution
Inside pl/sql block:
using a bind variable:
PL/SQL procedure successfully completed.
in a query:
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
----------
20110501in 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
----------
20110501select 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.