Unable to execute Oracle 10g SP from SQL SERVER

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Other SQL Server 2008 Topics

Unable to execute Oracle 10g SP from SQL SERVER

Author

Topic

Ifor
Aged Yak Warrior

700 Posts

Posted - 2013-10-04 : 10:50:32

I have a simple Oracle SP, with no parameters which returns nothing, setup like:


CREATE OR REPLACE PROCEDURE MYSCHEMA.MYPROC
AS
BEGIN

	INSERT INTO MYSCHEMA.MYTABLE
	SELECT ...;
	
	INSERT INTO MYSCHEMA.MYTABLE
	SELECT ...;

	COMMIT;
END;


GRANT EXECUTE ON MYSCHEMA.MYPROC TO MSUSER;

I also have a few views with SELECT granted to MSUSER on the Oracle box.

The SP works fine when executed through Oracle's SQLDeveloper. (It does come with the message 'anonymous block completed' - whatever that means.)

I then setup a Linked Server, ORACLEBOX, from a SQL2008R2 instance to the Oracle machine connecting as MSUSER.

I have no problems using the four part naming convention to select from the Oracle views.

When I run EXEC ORACLEBOX..MYSCHEMA.MYPROC from Management Studio,

If ORACLEBOX has Server Options of RPC True and RPC Out False I get:

Msg 7411, Level 16, State 1, Line 1
Server 'ORACLEBOX' is not configured for RPC.

If ORACLEBOX has Server Options of RPC True and RPC Out True I get:

OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLEBOX" returned message "Unspecified error".
Msg 7323, Level 16, State 2, Line 1
An error occurred while submitting the query text to OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLEBOX".

I am not an Oracle expert, but I would have thought this should have been straight forward as there are no IN or OUT parameters and no result set requiring a cursor.

Does anyone have an idea on how to get this to work?

Ifor
Aged Yak Warrior

700 Posts

Posted - 2013-10-07 : 05:35:30

If anyone is interested, I managed to get the proc to work by going:


EXEC ('CALL MYSCHEMA.MYPROC()') AT ORACLEBOX

I suspect there are better ways but this is good enough for me now.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources