1 Reply Latest reply: Dec 9, 2012 11:47 PM by Kgronau-Oracle RSS

    Unable to retrive values from MSSQL procedure call

    593429
      Hi,

      I have procedure named dummy @x in, @y out on MS SQL server.
      Between that MS SQL server and ORA server is HSODBC.

      When I execute this code;
      declare
      y number;
      l_sRemoteCallStatement varchar2(4000);
      l_iRpcHandler binary_integer;
      l_iRetCode binary_integer;
      begin
      l_sRemoteCallStatement := 'exec dummy @x, @y';

      l_iRpcHandler := dbms_hs_passthrough.open_cursor@mylink;
      dbms_hs_passthrough.parse@mylink( l_iRpcHandler, l_sRemoteCallStatement);

      dbms_hs_passthrough.bind_variable@mylink( l_iRpcHandler, 1, 'TEST', '@x');
      dbms_hs_passthrough.bind_out_variable@mylink( l_iRpcHandler, 2, y);

      l_iRetCode := dbms_hs_passthrough.execute_non_query@mylink( l_iRpcHandler );


      dbms_hs_passthrough.get_value@mylink( l_iRpcHandler, 2, y);

      dbms_hs_passthrough.close_cursor@mylink(l_iRpcHandler);

      dbms_output.put_line('y := '||y);
      end;

      I get this error:
      ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
      [Generic Connectivity Using ODBC][A030] The parameter with ordinal 3 does not exist.
      ORA-06512: at line 135

      Anyone see what I am doing wrong? Pls help!