Problem Creating Link Server Between Sql Server 2005 And Oracle

(Doc ID 414102.1)

Last updated on JULY 05, 2017

Applies to:

Oracle Provider for OLE DB - Version: 8.1.6.0.0 and later   [Release: and later ]
Generic Windows
Microsoft Windows XP
***Checked for relevance on 6-Apr-2012***

Symptoms

Using the Oracle Provider for OLEDB (OraOledb.Oracle) from SQLServer 2005 to call a stored procedure in Oracle may result in an error similar to the following:

Msg 7357, Level 16, State 2, Line 1
Cannot process the object "{call Employee_Pkg.getenames({resultset 15,o_ename})}". The OLE DB provider "OraOLEDB.Oracle" for linked server "SCOTTLINK" indicates that either the object has no columns or the current user does not have permissions on that object.

Using Microsoft's OLEDB provider for Oracle (MSDAORA) works correctly.

The behavior can be reproduced with the following package:

CREATE OR REPLACE PACKAGE Employee_Pkg
AS
  TYPE tblEName IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER; 
  PROCEDURE getenames(o_EName OUT tblEName); 
END Employee_Pkg;
/

CREATE OR REPLACE PACKAGE BODY Employee_Pkg
AS
  procedure getenames(o_EName OUT tblEName)
  IS
    CURSOR cur_employee IS SELECT ename from emp;
    RecordCount NUMBER DEFAULT 0;
  BEGIN
    FOR curRecEmployee IN cur_employee LOOP
      RecordCount:= RecordCount + 1;
      o_EName(RecordCount):= curRecEmployee.ename;
    END LOOP;
  END;
END Employee_Pkg;
/

To reproduce the error, create a linked server in SQL Server Mangement Studio using ORAOLEDB, and call it as follows:

select * from openquery(MYORAOLEDBLINK,
  '{call Employee_Pkg.getenames({resultset 15,o_ename})}' )

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms