none
Unable to connect to Oracle using Linked server

    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 sp in old environment(which is using MSDAORA) we are getting 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) Down loaded "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.

    Friday, December 16, 2016 10:31 AM

Answers

  • Ok, not sure if you want the long version or the short version.  The long version is very long and has to do with the difference between Oracle SQL and PL/SQL, how PL/SQL programs return resultsets, and how different client libraries handle that.

    The question here is "How do I call a stored procedure returning a ref cursor with Oracle's OleDB provider from a Linked Server"

    This short answer is that you need to add the PLSQLRSet switch to your connection string and use the ODBC escape syntax to call your stored procedure.  EG

    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

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

    David


    Microsoft Technology Center - Dallas

    My Blog

    Friday, December 16, 2016 8:57 PM
  • >Could you please tell us what is for FetchSize=2000?

    Sure.  That controls the size of the buffer used to transmit rows from Oracle to the client (here SQL Server is the client).  It defaults to 100, which is too small for bulk data transfers between servers.

    David


    Microsoft Technology Center - Dallas

    My Blog

    Monday, December 19, 2016 1:23 PM

All replies

  • Can you script out the Linked server and paste here?

    Kindly mark the reply as answer if they help

    Friday, December 16, 2016 11:07 AM
    • EXEC( ' BEGIN NPMSE_INTERFACE.PKGAPP.GETNEWINFOPROJECT(''LB1735'',''TORO'');END;' ) AT NPMSE;
    Friday, December 16, 2016 11:39 AM
    • EXEC( ' BEGIN NPMSE_INTERFACE.PKGAPP.GETNEWINFOPROJECT(''LB1735'',''TORO'');END;' ) ATNPMSE;
    Friday, December 16, 2016 11:39 AM
  • sorry you are asking for this i guess.


    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

    Friday, December 16, 2016 11:52 AM
  • @Phil,

    After installing the Oracle client we are able to see the "ORAOLEDB.PRACLE" and test connection is succeeded.

    The problem is with when we are calling the sp from sql server we are getting the below error.

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

    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

    the same statement we are able to execute and we are able to fetch the results using MSDAORA.

    Please correct us if we are missing any thing else.

    Thanks in advance for your help.

    Friday, December 16, 2016 2:00 PM
  • Sorry about my previous answer being out in left-field.

    Contrary to the title of your post, I think you are actually connecting successfully to Oracle store, it's just that the OraOLEDB.Oracle provider isn't doing some kind of implicit conversion for you like the MSDAORA provider was, and so now you have to modify sp with code that does some kind of explicit conversion, instead, or else SQL is made PL/SQL syntax compliant. You need to rewrite the code so that Oracle will digest it successfully.

    MSDAORA is a 32-bit provider, whereas OraOLEDB.Oracle is a 64-bit provider.

    That 'PLS-00306: wrong number or types of arguments' error seems pretty common, since I got lots of hits on it when googling. You've probably already done this, but here is one video that seems to address the problem with a solution:
    https://www.youtube.com/watch?v=xwMVL3Z_vn8

    I know that's a bit of a vague answer, but hopefully it will help get your troubleshooting pointed in the right direction.

    Good luck,


    Phil Streiff, MCDBA, MCITP, MCSA









    • Edited by philfactor Friday, December 16, 2016 2:32 PM
    Friday, December 16, 2016 2:07 PM
  • Ok, not sure if you want the long version or the short version.  The long version is very long and has to do with the difference between Oracle SQL and PL/SQL, how PL/SQL programs return resultsets, and how different client libraries handle that.

    The question here is "How do I call a stored procedure returning a ref cursor with Oracle's OleDB provider from a Linked Server"

    This short answer is that you need to add the PLSQLRSet switch to your connection string and use the ODBC escape syntax to call your stored procedure.  EG

    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

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

    David


    Microsoft Technology Center - Dallas

    My Blog

    Friday, December 16, 2016 8:57 PM
  • BIG BIG BIG Thank you and you are awesome . It will save lots of people time checked it in lots of places every body stuck with this.

    If i didn't get answer from you i decided to start C# code to fetch this from ODP.Net, it's again death game.

    let me post this to every body where ever people are facing this issue.

    Could you please tell us what is for FetchSize=2000?

    It would be great if you can elaborate  The long version.

    You saved lots of peoples effort.


    Chaitanya


    Monday, December 19, 2016 9:54 AM
  • >Could you please tell us what is for FetchSize=2000?

    Sure.  That controls the size of the buffer used to transmit rows from Oracle to the client (here SQL Server is the client).  It defaults to 100, which is too small for bulk data transfers between servers.

    David


    Microsoft Technology Center - Dallas

    My Blog

    Monday, December 19, 2016 1:23 PM