I have Sql server and oracle installed on same machine. So I created a heterogeneous connection between the two server. Now I need to do some transaction in my sql server and need to send the result to remote SQL Server. I tried achieve this by following steps,
- Created a Link between Oracle and Sql server (Works fine)
- Created a Link server in Sql server to connect Remote sql server (working)
- So I rewrite the Sql server procedure to call remote server.
DECLARE c INTEGER; nr INTEGER; v_sql VARCHAR2(32767); BEGIN V_SQL :='EXEC dbo.TESTPROCEDURE ''test'',1'; C := DBMS_HS_PASSTHROUGH.OPEN_CURSOR@SQLSERVER; DBMS_HS_PASSTHROUGH.PARSE@SQLSERVER(C, V_SQL ); NR := DBMS_HS_PASSTHROUGH.EXECUTE_NON_QUERY@SQLSERVER(C); DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@SQLSERVER(C); commit; END;
Local Sql server
CREATE PROCEDURE [dbo].[TESTPROCEDURE] @abc varchar(100), @xyx int, AS DECLARE @result int BEGIN SET @result = 1; INSERT INTO dbo.TEST VALUES(@abc) /**Some code**/ EXEC @result = REMOTESQL.[DATABASENAME].[dbo].[remoteproceduretest] @abc, @xyz; END
When I tried to execute the oracle block I get the following error.
Error report - ORA-28500: connection from ORACLE to a non-Oracle system returned this message: ORA-02063: preceding line from SQLSERVER ORA-06512: at line 14 28500. 00000 - "connection from ORACLE to a non-Oracle system returned this message:" *Cause: The cause is explained in the forwarded message. *Action: See the non-Oracle system's documentation of the forwarded message.
Is it possible to call like above.