17

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?

18

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 ":" */
  • 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
3

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.

  • 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 1 at 16:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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