Getting error while executing Oracle Package from

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

Getting error while executing Oracle Package from

Author

Topic

YogeshDesai
Posting Yak Master

136 Posts

Posted - 2009-03-28 : 07:33:30

Hi all,

I am trying to call Oracle Package from SQL-2005, getting following error while execution :

Details as follows :

Linked Server Name : CISU
Oracle Package Name : WEB_PKG
Oracle Procedure Name : Web_Update_Profile
Input parameter : @parma1ForOracle = ‘10010’
Output Parameter : RetVal

--************

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

SET XACT_ABORT ON

Declare @OracleCall varchar(8000),

@parma1ForOracle varchar(800)

SET @parma1ForOracle = '10010'

set @OracleCall = 'SELECT * FROM OPENQUERY(CISU , ''{call CLP_OWNR.WEB_PKG.Web_Update_Profile('

set @OracleCall = @OracleCall + ''+ @parma1ForOracle + ''

set @OracleCall = @OracleCall + ',{resultset 3, RetVal})}'')'

PRINT @OracleCall

EXEC(@OracleCall )

SET XACT_ABORT OFF

GO

--***************

Print output :

SELECT * FROM OPENQUERY(CISU , '{call CLP_OWNR.WEB_PKG.Web_Update_Profile(10010,{resultset 3, RetVal})}')

Error :

OLE DB provider "MSDAORA" for linked server "CISU" returned message "Argument name not found for PL/SQL procedure.".

Msg 7321, Level 16, State 2, Line 1

An error occurred while preparing the query "{call CLP_OWNR.WEB_PKG.Web_Update_Profile(10010,{resultset 3, RetVal})}" for execution against OLE DB provider "MSDAORA" for linked server "CISU".

SQL IN Minds

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources