0

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?

  • That's not a "scalar integer". That's a single row result set with a single column of type integer. – mustaccio Oct 16 at 15:03
  • @mustaccio, The third line is, yes. Are you saying the second line is too? – durette Oct 16 at 15:30
  • That's absolutely irrelevant to how you retrieve the SP result. – mustaccio Oct 16 at 15:39
  • 1
    @durette We can't really help without useful information. If your stored procedure does not return a resultset, then what do you expect to "receive" in your oracle code? What is your goal? The return value from a SQL Server procedure is used to generally indicate success/logical error - but that is a only convention. Rarely does code use this return value. – SMor Oct 16 at 16:41
  • 1
    Seems the oracle doc has important information. Note the 3 mutually-exclusive modes. Don't know oracle but perhaps it would be easier to change the procedure and return the "result" in a manner that is better suited. – SMor Oct 16 at 17:00

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.