Unable to fetch data from SQL Server linked server from oracle

Question

We are upgrading SQL Server 2005 to SQL Server 2014 with Windows Server 2012.

We have linked server in SQL Server to connect to oracle to fetch the data, and in old environment we are using MSDAORA provider.

But in while installing Oracle client in new environment we are not getting MSDAORA Provider.

When googling found that this "MSDAORA" provider is deprecated. So we are trying to fetch the data using ORAOLEDB.ORACLE.

While calling the stored procedure from oracle using linked server we are getting the below error.

OLE DB provider "ORAOLEDB.Oracle" for linked server "NPMSE" returned message "ORA-06550: line 1, column 8:

PLS-00306: wrong number or types of arguments in call to 'GETNEWINFOPROJECT'

ORA-06550: line 1, column 8:

PL/SQL: Statement ignored".

Msg 7215, Level 17, State 1, Line 1

Could not execute statement on remote server 'NPMSE'

But while executing the same stored procedure in the old environment (which is using MSDAORA), we are getting the expected result.

Below is the stored procedure code from Oracle.

PROCEDURE GETNEWINFOPROJECT
    ( i_projectname    IN  PROJECT.NAME%TYPE
      , i_locationname IN  LOCATION.NAME%TYPE
      , o_newObject_cursor OUT PKGAPPCMOR.R_CURSOR )
AS
     huge Business logic
END GETNEWINFOPROJECT;

We are suspecting that may be (o_newObject_cursor OUT PKGAPPCMOR.R_CURSOR ) causing the issue.

Tried in different ways.

1. Downloaded "MSDAORA" and tried to install didn't work.

2. Placed "inProcess" in linked server didn't work.

3. Now we are suspecting that OLEDB ORA is not compatible with any of the data type which we are using in Oracle.

Hence we are running out of the time, need experts suggestion to pin point the problem, if any body faced this issue kindly respond to this.

Your help will be highly appreciated.

Thanks in advance.

Below is my linked server script:

IF  EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'NPMSE')EXEC master.dbo.sp_dropserver @server=N'NPMSE', @droplogins='droplogins'
GO

/ Object:  LinkedServer [NPMSE]    Script Date: 12/16/2016 17:15:37 /
EXEC master.dbo.sp_addlinkedserver @server = N'NPMSE', @srvproduct=N'Oracle', @provider=N'ORAOLEDB.Oracle', @datasrc=N'xxx', @provstr=N'UID=xxx;PWD=xxx'
 / For security reasons the linked server remote logins password is changed with ######## /
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'NPMSE',@useself=N'False',@locallogin=NULL,@rmtuser=N'xxx',@rmtpassword='########'

GO

EXEC master.dbo.sp_serveroption @server=N'NPMSE', @optname=N'collation compatible', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'NPMSE', @optname=N'data access', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'NPMSE', @optname=N'dist', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'NPMSE', @optname=N'pub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'NPMSE', @optname=N'rpc', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'NPMSE', @optname=N'rpc out', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'NPMSE', @optname=N'sub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'NPMSE', @optname=N'connect timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'NPMSE', @optname=N'collation name', @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N'NPMSE', @optname=N'lazy schema validation', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'NPMSE', @optname=N'query timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'NPMSE', @optname=N'use remote collation', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'NPMSE', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

and we are calling the stored proedure from SQL Server using this query.

EXEC( ' BEGIN NPMSE_INTERFACE.PKGAPP.GETNEWINFOPROJECT(''LB1735'',''TORO'');END;' ) ATNPMSE;

and getting errors:

OLE DB provider "ORAOLEDB.Oracle" for linked server "NPMSE" returned message "ORA-06550: line 1, column 8:

PLS-00306: wrong number or types of arguments in call to 'GETNEWINFOPROJECT'

ORA-06550: line 1, column 8:

PL/SQL: Statement ignored".

Msg 7215, Level 17, State 1, Line 1
Could not execute statement on remote server 'NPMSE'

Answer 1

It may be helpful to some others in future.

exec sp_addlinkedserver @server=N'MyOracle',
        @srvproduct='Oracle', 
        @provider= 'ORAOLEDB.Oracle', 
        @datasrc= N'//10.0.0.22/xe', 
        @provstr= N'FetchSize=2000;PLSQLRSet=1'

and you need to call oracle sp in the below manner.

EXEC( '{CALL HR.GETNEWINFOPROJECT(''LB1735'',''TORO'')}' ) at MyOracle;