Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

HS call Mysql stored procedure with OUT parameter

Marc BattleMar 25 2019 — edited Mar 25 2019

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;

This post has been answered by Marc Battle on Mar 25 2019
Jump to Answer
Comments
Post Details
Added on Mar 25 2019
1 comment
590 views