26

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?

1
  • 3
    oracle is pure pain Commented Feb 7, 2020 at 20:57

3 Answers 3

23

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 ":" */
3
  • This unfortunately does not work for me. var my_num NUMBER; BEGIN SELECT 12345 INTO my_num FROM dual; END; / select * from my_table sa where sa.my_col = :my_num;
    – Matthew
    Commented Nov 15, 2018 at 17:59
  • what error do you get? (just tested and works)
    – user953
    Commented Nov 16, 2018 at 14:18
  • I actually tried the solution posted by Jon of All Trades and that worked perfectly for my needs -- i.e. using DEFINE and referencing the variable with &.
    – Matthew
    Commented Nov 19, 2018 at 15:17
5

SQL*Plus supports an additional format:

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

SELECT
    *
FROM
    MyTable
WHERE
    DateField BETWEEN &StartDate and &EndDate;

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

2
  • This worked for me in Toad for Oracle when using any of these functions: Execute as script or Execute via Toad script runner or Execute via SQL*Plus. However, if you try running with the Execute/compile statement at caret it returns an error message: "ORA-009000: invalid SQL statement". Commented May 1, 2019 at 16:43
  • Works in SQL Developer too.
    – Wassadamo
    Commented Feb 2, 2021 at 7:56
1

In ORACLE SQL Developer 20.2.0.175, we can Run Script (F5):

DEFINE usr = 'YourName'; 
SELECT * FROM Department WHERE created_by = '&usr';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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