How can I call a SQL Server stored procedure from Oracle and retrieve a scalar return value?


I’m developing a PL/SQL solution in Oracle Database Enterprise Edition 12.1.0.2. I’m calling stored procedures in Microsoft SQL Server 2014.

I am using DG4ODBC with the DBMS_HS_PASSTHROUGH package to call those stored procedures.

For stored procedures that return a tabular result set, I am able to use PARSE, FETCH_ROW, and GET_VALUE to retrieve the values. (My solution uses hard parsing, but it works for the volume we’re expecting. I’m open to tips if you know how I can use binds, but that’s not my question.)

DECLARE    sql_server_cursor_     BINARY_INTEGER;    sql_server_statement_  VARCHAR2(32767);    fetched_value_col1_    whatever_type_from_col1;    fetched_value_col2_    whatever_type_from_col2; BEGIN    sql_server_statement_ := 'EXEC "myDatabase"."dbo"."myProcedure" @p_one = N''' || sanitize__(my_argument_) || ''';';    sql_server_cursor_ := dbms_hs_passthrough.open_cursor@sql_server_link_;    dbms_hs_passthrough.parse@sql_server_link_(sql_server_cursor_, sql_server_statement_);    WHILE dbms_hs_passthrough.fetch_row@sql_server_link_(sql_server_cursor_) > 0    LOOP       dbms_hs_passthrough.get_value@sql_server_link_(sql_server_cursor_,  1, fetched_value_col1_);       dbms_hs_passthrough.get_value@sql_server_link_(sql_server_cursor_,  2, fetched_value_col2_);       dbms_output.put_line('fetched_value_col1_  ' || fetched_value_col1_);       dbms_output.put_line('fetched_value_col2_  ' || fetched_value_col2_);    END LOOP;    dbms_hs_passthrough.close_cursor@sql_server_link_(sql_server_cursor_); END; / 

I have a stored procedure in SQL Server that returns a scalar integer.

DECLARE @return_value int; EXEC @return_value = "myDatabase"."dbo"."myProcedure" @p_one = N'HelloWorld', @p_two = 42; SELECT @return_value; 

From Oracle, how would I call this procedure and retrieve that scalar value?