Skip to Main Content

SQL Developer Data Modeler

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!

Connection between Oracle and MySQL - Execute SET SESSION SQL_MODE

861488May 12 2011
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.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 9 2011
Added on May 12 2011
0 comments
305 views