0 Replies Latest reply: Dec 13, 2012 11:53 AM by 979828 RSS

    Help needed with variable - trying to get data from Oracle using linked svr

    979828
      Sorry if I posted this in the wrong forum - I just didn't know where to post it.

      I'm trying to write a simple stored procedure to get data from an oracle database via a linked server in SQL Enterprise manager. I have no idea how to pass a variable to oracle.

      Here's how I would get the variable in SQL:

      declare @maxkey INTEGER
      select @maxkey= MAX(keyfield) from [server].Data_Warehouse.dbo.mytable
      select * from [server].Data_Warehouse.dbo.mydetailtable where keyfield=@maxkey

      the select statement I need to do in oracle would use that variable like this:
      select * from OPENQUERY(OracleLinkedServer,'select
      * from ORACLEDB.TABLE where keyfield > @maxkey')
      and I get this message: OLE DB provider "OraOLEDB.Oracle" for linked server "OracleLinkedServer" returned message "ORA-00936: missing expression".

      I realize that I can't pass the @maxkey variable to oracle - so how do I accomplish this?