2

I need a simple example of an Oracle stored procedure that returns a result set. I didn't find out how to return the result set to the SQL Server. What I have now is:

On Oracle:

CREATE OR REPLACE PROCEDURE sp_sessions (cursorParam OUT SYS_REFCURSOR)
IS
BEGIN
OPEN cursorParam FOR select * from v$session;
END;
/

running on sql plus:

variable rc refcursor;
exec OR_WebI_SmartLinkMetrics( :rc );
print rc;

On SQL Server it should be something like: 

SELECT * FROM OPENQUERY(LocalOracle, '{CALL sp_sessions(......)');

Is this possible? I was thinking to use temporary tables?

ROMANIA_engineer
  • 54,432
  • 29
  • 203
  • 199
fredy
  • 575
  • 2
  • 9
  • 20
  • I have the same issue. Have you found a solution? – Gutti Oct 02 '13 at 18:11
  • Without cursor there is an example here: [link](http://stackoverflow.com/questions/13741175/calling-oracle-stored-procedure-with-output-parameter-from-sql-server) – Gutti Oct 02 '13 at 18:17

1 Answers1

0

According to http://msdn.microsoft.com/en-us/library/ms188427.aspx it is not possible to call stored procedures using OPENQUERY, you must use the four-part name, such as...

EXEC SeattleSales.master.dbo.xp_msver
hagensoft
  • 1,497
  • 13
  • 13