Execute Oracle Stored Procedure from SQL Server

[LordOfCode]

Hello everyone!

Currently I am developing a distribuited application for a local bank in my country. In this moment I am developing the business layer using stored procedures in SQL Server 2000, but I have to use data from different data sources, including Oracle and other SQL Servers. I have created linked servers for all of them. Now I face problem that I need to call an Oracle Stored Procedure through one of this linked servers, but it does not work or I do not know how to do it.

I am going to paste an example of what I am tryng.

This is oracles stored procedure code

CREATE OR REPLACE PROCEDURE EDALORZO.HELLO
(friendCode IN number,friendName OUT varchar)
  IS
BEGIN
  select NOM_AMIGO into friendName from AMIGOS where COD_AMIGO = friendCode;
END;

...which queries this table

CREATE TABLE EDALORZO.AMIGOS ( 
    COD_AMIGO    NUMBER(3,0) NOT NULL,
    NOM_AMIGO    VARCHAR2(60) NULL,
    PRIMARY KEY(COD_AMIGO)
)

Then I created an Oracle linked server from Enterprise Manager and wrote this code

select * from oracle..EDALORZO.AMIGOS
go
declare @data varchar(60)
execute oracle..EDALORZO.HELLO 1,@data OUTPUT
print @data
go

The first query does work. But the stored procedure calling does not. Any help? Any Idea?

I thank your help beforehand.

edalorzo@hotmail.com

 

[sem]

Any details? Any error code/text? May assume that the error is TOO_MANY_ROWS or NO_DATA_FOUND These errors may occur when your code needs exact fetch while the query doesn't return exactly 1 row.

Regards, Dima

http://seminihin.narod.ru

 

[LordOfCode]

Hello, Dima

The error I receive is this

Server: Msg 7212, Level 17, State 1, Line 2
Could not execute procedure 'HELLO' on remote server 'oracle'.
[OLE/DB provider returned message: One or more errors occurred during processing of command.]

I have tried everything. I've jumped to the conclussion that it is not possible. Now I am downloading the Oracle OLEDB Provider from the OTN in order to see if the Oracle's Propietary OLEDB Provider works better than the default Microsoft OLEDB Provider.

However if you have any idea of something I can try I will appreciate it... even better if you know the solution to my problem.

Thanks in advance,
LoC.

edalorzo@hotmail.com