Oracle variables

Question

I am trying to write an Oracle query that has some variables set before the query which i can then reference within the query.

I can do the below in SQL Server:

DECLARE @ReviewID as VARCHAR(3)
DECLARE @ReviewYear AS VARCHAR(4)

SET @ReviewID = 'SAR'
SET @ReviewYear = '1011'

select * from table1 where review_id = @ReviewID and acad_period = @reviewyear

What is the Oracle equivalent of the above? I have tried cursors and bind variables but am obviously doing something wrong as these methods aren't working.

The Oracle query is intended to go into an OLEDB Source in SSIS and the variables will then be set from package level variables.

Answer 1

Oracle equivalent in SQL Plus:

VAR ReviewID VARCHAR(3)
VAR ReviewYear VARCHAR(4)

EXEC :ReviewID := 'SAR';
EXEC :ReviewYear := '1011';

select * from table1 where review_id = :ReviewID and acad_period = :reviewyear;

Answer 2

If you're going to be using this query in an OleDb Source from variable, you'll likely need to use an Expression as opposed to SQL variables. So you'd build the SQL statement along the lines of

"select * from table1 where review_id = " + @[User::ReviewID] + " and acad_period = " + @[User::ReviewYear]