21

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?

Nick Chammas
  • 14,170
  • 17
  • 73
  • 119
Mark Allison
  • 475
  • 1
  • 3
  • 9

3 Answers3

21

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 ":" */
ik_zelf
  • 6,492
  • 19
  • 35
  • 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 Nov 15 '18 at 17:59
  • what error do you get? (just tested and works) – ik_zelf Nov 16 '18 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 Nov 19 '18 at 15:17
4

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.

Jon of All Trades
  • 5,722
  • 5
  • 37
  • 62
  • 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". – SherlockSpreadsheets May 01 '19 at 16:43
  • Works in SQL Developer too. – Wassadamo Feb 02 '21 at 07:56
0

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

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