1 Reply Latest reply on Mar 25, 2019 3:37 PM by 1033010

    HS call Mysql stored procedure with OUT parameter

    1033010

      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;

        • 1. Re: HS call Mysql stored procedure with OUT parameter
          1033010

          I've resolved the problem without using BIND_OUT_VARIABLE that raises an error. I attach the code I'm using, if it can help someone:

           

          DECLARE

            vSql     VARCHAR2(32767);

            l_cursor BINARY_INTEGER;

            vId      NUMBER;

            vFecth   NUMBER;

          BEGIN

            vSql :='call sp_proc_do_something(@idMessageOut,''00001'',1, ''message'')';

           

            l_cursor := DBMS_HS_PASSTHROUGH.open_cursor@DataBaseRemote;

            DBMS_HS_PASSTHROUGH.PARSE@DataBaseRemote(l_cursor,vSql);

           

            vFecth := DBMS_HS_PASSTHROUGH.EXECUTE_NON_QUERY@DataBaseRemote(l_cursor);

           

            DBMS_HS_PASSTHROUGH.close_cursor@DataBaseRemote(l_cursor);

           

            --Retrieve OUT parameter Id

           

            vSql :='select @idMessageOut';

           

            l_cursor := DBMS_HS_PASSTHROUGH.open_cursor@DataBaseRemote;

           

            DBMS_HS_PASSTHROUGH.parse@DataBaseRemote(l_cursor,vSql);

           

            vFecth := DBMS_HS_PASSTHROUGH.fetch_row@DataBaseRemote(l_cursor);

            DBMS_HS_PASSTHROUGH.get_value@DataBaseRemote(l_cursor, 1,  vId);

           

            DBMS_HS_PASSTHROUGH.close_cursor@DataBaseRemote(l_cursor);

           

             COMMIT;

           

            :P_ID_MIS := vId;

           

          EXCEPTION

            WHEN OTHERS then

              IF (l_cursor IS NOT NULL) THEN

                DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@DataBaseRemote(l_cursor);

              END IF;

           

              raise_application_error(-20000, substr(sqlerrm || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, 1, 2000));

            END;