This discussion is archived
1 Reply Latest reply: Dec 9, 2012 9:47 PM by kgronau RSS

Unable to retrive values from MSSQL procedure call

593429 Newbie
Currently Being Moderated
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!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points