Environment:
Local Database: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
Remote Database: Informix accessed through Heterogeneous Connectivity (Oracle Gateway for ODBC - 12.1.0.2.0)
Scenario:
I have a some procedures that import data from the remote Informix database to the local Oracle database. Now I need to update the remote source, providing the ID of the inserted record into a new field that was added to the source table. Since the ODBC Gateway does not allow two phase commit, I cannot do the insert into the local database and the update to the remote one in the same transaction, but that is not an issue at this moment. Since I cannot do many changes to the procedures that currently insert in the local database, I have done the following to them:
After they insert in the local database the record that needs to be imported, the procedures creates a record in a "temporary log" table with the following structure:
TargetTable VARCHAR2(30) (Name of the table where the record ha been inserted)
LocalID NUMBER(10) (ID of the inserted record, is the value of the Primary Key)
RemoteID NUMBER(10) (ID of the source record, is the value of the Primary Key)
I know I could have added the RemoteID to each table where I am inserting, but I cannot modify the structure without affecting existing apps, and data is only for records that are imported and not generated by the application.
Since the insert is on the local DB, I can commit the operation as desired (I cannot update the source table in the remote DB in the same transaction)
After the process ends, I wanted to run a process that reads from the new "temporary log" and updates the remote data. But I'm finding different issues that do not allow me to do what I actually need.
Approach #1
UPDATE ( SELECT t."id_estado_migr", t."id_sigma_migr", t."id_mov_chofer", a.id_sigma
FROM LocalLog a
JOIN m\_mov\_chofer@Remote t ON a.id\_informix = t."id\_mov\_chofer"
AND a.tabla = 'T\_MOV\_CHOFER' )
SET "id_estado_migr" = 'I',
"id_sigma_migr" = id_sigma;
Approach #2
MERGE INTO m_mov_chofer@Remote t
USING (SELECT a.id_sigma,
a.id\_informix FROM LocalLog a
WHERE a.tabla = 'T\_MOV\_CHOFER' ) s
ON (t."id_mov_chofer" = s.id_informix)
WHEN matched THEN
UPDATE SET t."id\_estado\_migr" = 'I',
t."id\_sigma\_migr" = s.id\_sigma;
Approach #3
UPDATE m_mov_chofer@Remote t
SET t."id_estado_migr" = 'I',
t."id\_sigma\_migr" = ( SELECT id\_sigma FROM LocalLog a
WHERE a.id\_informix = t."id\_mov\_chofer"
AND a.tabla = 'T\_MOV\_CHOFER')
WHERE t."id_mov_chofer" IN (SELECT a.id_informix FROM LocalLog a
WHERE a.tabla = 'T\_MOV\_CHOFER');
The latest one seems to be a restriction of Informix, but the other two errors are confusing me, if someone has some suggestions regarding how to implement this functionality It would be really appreciated!
If I loop through a cursor and update records one by one, it works, but I would prefer a solution that uses less resources.
Regards,
Lisandro