Execute Oracle Stored Procedure

[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

 

[dbomrrsm]

this may work

declare @data varchar(60)
set @data = execute oracle..EDALORZO.HELLO 1
print @data

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[LordOfCode]

Thanks for your reply Dbomrrsm. Nevertheless the code that you posted produces this error:

Error: Incorrect syntax near the keyword 'execute'.

I guess you meant:

declare @data varchar(60)
execute @data = oracle..EDALORZO.HELLO 1
print @data

However this code produces this error:

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.]

What did I do wrong?


edalorzo@hotmail.com

 

[LordOfCode]

I am starting to beleive that I might need any special privilege in the Oralce Server. What do you think?

edalorzo@hotmail.com

 

[dbomrrsm]

if you have access to the oracle DB can you run the proc directly and see the output - ie do you have access to the proc - however if you created the proc you should have access to it and you obviously have access to the table.

I would try accessing the ODB directly and look at the exact syntax you need to execute the proc and get the right output.

Also - do you not need a RETURN friendName in the oracle proc code ?

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[LordOfCode]

It works in Oracle:

DECLARE
	FRIENDCODE NUMBER := 1;
	FRIENDNAME VARCHAR2(25) := '-';
BEGIN
	EDALORZO.HELLO(FRIENDCODE, FRIENDNAME);
	DBMS_OUTPUT.PUT('FRIENDCODE: ');
	DBMS_OUTPUT.PUT_LINE(FRIENDCODE);
	DBMS_OUTPUT.PUT('FRIENDNAME: ');
	DBMS_OUTPUT.PUT_LINE(FRIENDNAME);
END;

Produces this output

DBMS_OUTPUT:
 ------------
 FRIENDCODE: 1
 FRIENDNAME: Tomas Araya

edalorzo@hotmail.com

 

[dbomrrsm]

Think that might be the problem - the proc doesnt actually return anything - it populates friendcode but doesnt return friendcode - would you need to exec the proc and then select friendcode from dual into the SDL Server variable @data

soemthing like

execute oracle..EDALORZO.HELLO 1
set @data = Select friendcode from oracle..dual;

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[LordOfCode]

Did you actually tested that code?

I wrote this version of your post:

declare @data varchar(60)
execute oracle..EDALORZO.HELLO 1
set @data = (Select friendcode from oracle..dual)

And this is the output

Error: Could not execute procedure 'HELLO' on remote server 'oracle'.
More exceptions ... Invalid object name 'oracle..dual'.

edalorzo@hotmail.com