Geek asks and answers

Oracle Variables

I am trying to write an Oracle query that has some variables set before the query, which I can then refer to in the query.

I can do the following 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 above? I tried cursors and bound variables, but obviously I'm doing something wrong, as these methods do not work.

The Oracle query is designed to go to the OLEDB source in SSIS, and then the variables will be set from the package level variables.

+2
source share
2 answers

If you intend to use this query in an OleDb Source variable from a variable, you will most likely have to use an expression rather than SQL variables. This way you create the SQL statement row by row

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

Oracle 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;
+8

More articles:


All Articles