2

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.

hermiod
  • 1,158
  • 4
  • 16
  • 27

2 Answers2

8

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;
Tony Andrews
  • 129,880
  • 21
  • 220
  • 259
  • Unfortunately, this method doesn't seem to work when running the query from the OLEDB Source, keeps coming up with 'ORA-01008: not all variables bound' even though I have declared, set, and used both variables. This method will come in useful in other scenarios though so up-vote for that. – hermiod Feb 16 '11 at 14:57
1

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]
grapefruitmoon
  • 2,988
  • 1
  • 22
  • 26
  • I was hoping to avoid going down the route of manually building the query in an expression, but I think this may be the best route to go down. Presumably, I can change the OLEDB Source to 'sql command from variable' and just populate the variable from an expression that does the above? – hermiod Feb 16 '11 at 14:55
  • Yes you'll be able to build the expression into a variable and use that in the OleDb Source as you've said. – grapefruitmoon Feb 16 '11 at 15:47