1

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,

  1. Created a Link between Oracle and Sql server (Works fine)
  2. Created a Link server in Sql server to connect Remote sql server (working)
  3. So I rewrite the Sql server procedure to call remote server.

Oracle Block

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.

Thanks.

  • How did you prove that the connection bet. oracle and the first MSSQL db "works fine". All 3 ORA- msgs are a bit generic, after googling all three myself (hint, hint) I'd suggest you try googling ORA-28500. – EdStevens Aug 25 '17 at 12:24
  • @EdStevens HI mate, I tested it by executing a remote procedure in ms sql server from oracle server. I can able to communicate oracle to sql server , sql server to remote sql server. The problem arises only when i tried below approach Oracle->local sql server->remote sql server – User12345 Aug 28 '17 at 11:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.