CODEWITHSUNDEEP
oracle-databasevariablesplsqlssis
hermiod
hermiod

Reputation: 1168

Oracle variables

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.

Upvotes: 2

Views: 3413

Answers (2)

grapefruitmoon
grapefruitmoon

Reputation: 3008

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]

Upvotes: 1

Tony Andrews
Tony Andrews

Reputation: 132650

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;

Upvotes: 8

Related Questions