How do I declare and use variables in Oracle?
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 = 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 <an identifier> <a double-quoted delimited-identifier> form current cursorHow do I declare and use variables in Oracle?
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 ---------- 20110501in 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;
what error do you get? (just tested and works)
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 &.
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".
License under CC-BY-SA with attribution
Content dated before 6/26/2020 9:53 AM
Fabio Marreco one year ago
oracle is pure pain