Hi,
I have a procedure on MySQL with an OUT parameter.
I can't obtain the OUT parameter. I have an error: "Internal error on heterogeneous remote agent".
There's no example on Oracle documentation nor internet.
I've tried a lot of things with the code I attach below. I replaced the "?" by a "@variable_name" too.
Can you help me, please?
Thanks!!
MySQL Procedure:
sp_proc_do_something(
<{OUT pIdOut int}>
<{IN pCodeIn varchar(45)}>
<{IN pCodeIn2 tinyint}>
<{IN pText varchar(250)}>
);
My call on Oracle:
DECLARE
vSql VARCHAR2(32767);
l_cursor BINARY_INTEGER;
vId NUMBER;
vFecth number;
BEGIN
vSql :='CALL sp_proc_do_something(
?,
''00001'',
1,
''message'');';
l_cursor := DBMS_HS_PASSTHROUGH.open_cursor@DataBaseRemote;
DBMS_HS_PASSTHROUGH.parse@DataBaseRemote(l_cursor,vSql);
DBMS_HS_PASSTHROUGH.BIND_OUT_VARIABLE@DataBaseRemote(l_cursor, 1, vId);
vFecth := DBMS_HS_PASSTHROUGH.EXECUTE_NON_QUERY@DataBaseRemote(l_cursor);
DBMS_HS_PASSTHROUGH.get_value@DataBaseRemote(l_cursor, 1, vId);
dbms_output.put_line('vId: ' || vId);
DBMS_HS_PASSTHROUGH.close_cursor@DataBaseRemote(l_cursor);
commit;
EXCEPTION
WHEN OTHERS then
IF (l_cursor IS NOT NULL) THEN
DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@DataBaseRemote(l_cursor);
END IF;
rollback;
raise_application_error(-20000, substr(sqlerrm || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, 1, 2000));
END;