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?
