Connection between Oracle and MySQL - Execute SET SESSION SQL_MODE
Hello guys.
I implemented a database link between Oracle and MySQL.
During the implementation, I had an error becouse my MySQL database did not undestand quotes, because the database is not set to ANSI mode.
I managed to solve this by running the procedure:
CREATE OR REPLACE PROCEDURE LINKTOMYSQL IS
ret integer;
c integer;
BEGIN
COMMIT;
c := DBMS_HS_PASSTHROUGH.OPEN_CURSOR@ DBLINK.WORLD;
DBMS_HS_PASSTHROUGH.PARSE@ DBLINK.WORLD(c, 'SET SESSION SQL_MODE=''ANSI_QUOTES'';');
ret := DBMS_HS_PASSTHROUGH.EXECUTE_NON_QUERY@ DBLINK.WORLD(c);
dbms_output.put_line(ret ||' passthrough output');
END;
This solved the problem.
But, We will be use in the ABAP program, that will read data in the MySQL and will write in the Oracle Database (SAP).
After execute the procedure in the ABAP program, the system show me the error:
Runtime Errors DBIF_RSQL_SQL_ERROR
Exception CX_SY_OPEN_SQL_DB
SQL error in the database when accessing a table.
Database error text........: "ORA-02047: cannot join the distributed transaction in progress"
Internal call code.........: "[RSQL/INSR/YSD0048_TB ]"
I think this error is due to run the PASSTHROUGH.OPEN_CURSOR instruction in the procedure. How do I disable it after the procedure execute?
Thanks for you help.