Linked Server Stored Procedure call from MSSQL > ORACLE

Question

I am trying to make an SP call from MSSQL to ORACLE, they are both setup in a linked server environment successfully, the only issue im having is creating the SQL SP call to Oracle;

Below is the procedure I am trying to reproduce in SQL as the SP call;

SET TIMING ON
SET SERVEROUTPUT ON
DECLARE 
  AVEMAIL VARCHAR2(255);
  ANCDPXID NUMBER;
  AVCDPURN VARCHAR2(12);

BEGIN 
  AVEMAIL := 'a@b.com';
  ANCDPXID := NULL;
  AVCDPURN := NULL;

  STP_FAC_PERSON ( AVEMAIL, ANCDPXID, AVCDPURN );

  DBMS_OUTPUT.Put_Line('ANCDPXID = ' || TO_CHAR(ANCDPXID));
  DBMS_OUTPUT.Put_Line('AVCDPURN = ' || AVCDPURN);

END;

I have attempted many times with examples below, but all not working;

SELECT * FROM OPENQUERY(FACUAT, '{Call FACUAT.CDBWEB.TACOMCDB.STP_FAC_PERSON( ''test@test.com'','''',''''}' )

EXEC ('CALL TACOMCDB.STP_FAC_PERSON(''test@test.com'','''','''')') at FACUAT

EXEC FACUAT.CDBWEB.TACOMCDB.STP_FAC_PERSON

Please advise Many thanks in advance!

Answer 1

just to update, below is the working query, plus i resintalled the 32-bit Oracle driver;

EXECUTE('Call STP_CAF_PERSON(?,?,?)', @AVEMAIL OUTPUT, @ANCDPXID OUTPUT, @AVCDPURN OUTPUT) AT [CAFUAT]