Skip to main content

Search

Welcome to Tibero GTS!

DBLINK Workaround (Multiple DMLs in One Procedure)

Document Type | Technical Information

Category | Interface/Integration

Document Number | TIITI025

 

Overview

This guide explains how to manage transactions so that multiple DML statements within a single procedure over a DBLink from Oracle to Tibero can be independently committed or rolled back.
Caution
The functionality described in this document commits or rolls back even if an error occurs within the procedure.
Using this feature makes the transaction units within the procedure independent, which may cause data consistency issues.
 
 

Method

Apply โ€˜PRAGMA AUTONOMOUS_TRANSACTIONโ€™ so that the procedure containing the statement can independently commit or roll back.
 

1. Create Test Tables

Oracle

CREATE TABLE T8_ORA (CD CHAR(1),CD_NO NUMBER, TEXT VARCHAR2(20));
CREATE TABLE T9_ORA (CD CHAR(1),CD_NO NUMBER, TEXT VARCHAR2(20));
CREATE TABLE T10_ORA (CD CHAR(1),CD_NO NUMBER, TEXT VARCHAR2(20));

INSERT INTO T8_ORA    VALUES (โ€˜Aโ€™,1,โ€™ORACLE1โ€™);
INSERT INTO T8_ORA    VALUES (โ€˜Bโ€™,1,โ€™ORACLE1โ€™);
INSERT INTO T8_ORA    VALUES (โ€˜Cโ€™,1,โ€™ORACLE1โ€™);
COMMIT;

INSERT INTO T9_ORA VALUES (โ€˜Aโ€™,1,โ€™ORACLE2โ€™);
INSERT INTO T9_ORA VALUES (โ€˜Eโ€™,1,โ€™ORACLE2โ€™);
INSERT INTO T9_ORA VALUES (โ€˜Fโ€™,1,โ€™ORACLE2โ€™);
COMMIT;

INSERT INTO T10_ORA VALUES (โ€˜Gโ€™,1,โ€™ORACLE3โ€™);
INSERT INTO T10_ORA VALUES (โ€˜Hโ€™,1,โ€™ORACLE3โ€™);
INSERT INTO T10_ORA VALUES (โ€˜Iโ€™,1,โ€™ORACLE3โ€™);
COMMIT;

TRUNCATE TABLE T9_ORA;

Tibero

CREATE TABLE T7_TIB (CD CHAR(1) , CD_NO NUMBER , TEXT VARCHAR(20));
CREATE TABLE T8_TIB (CD CHAR(1) , CD_NO NUMBER , TEXT VARCHAR(20));
INSERT INTO T8_TIB VALUES (โ€˜Cโ€™,2,โ€™TIBEROโ€™);
INSERT INTO T8_TIB VALUES (โ€˜Cโ€™,2,โ€™TIBERO2โ€™);
INSERT INTO T8_TIB VALUES (โ€˜Cโ€™,2,โ€™TIBERO3โ€™)

 

2. Query Before Workaround

DECLARE
BEGIN

FOR I IN (SELECT * FROM T8_ORA)
         LOOP
         INSERT INTO T7_TIB@OT_0627 VALUES (I.CD, I.CD_NO,I.TEXT);
         END LOOP;

FOR I2 IN (SELECT * FROM T9_ORA)
         LOOP
         INSERT INTO T7_TIB@OT_0627 VALUES (I2.CD, I2.CD_NO,I2.TEXT);
         END LOOP;

FOR I3 IN (SELECT * FROM T10_ORA)
         LOOP
         INSERT INTO T7_TIB@OT_0627 VALUES (I3.CD, I3.CD_NO,I3.TEXT);
         END LOOP;

FOR I4 IN (SELECT A.CD , B.CD_NO,B.TEXT FROM T8_ORA A, T8_TIB@OT_0627 B WHERE A.CD=B.CD)
         LOOP
         UPDATE T7_TIB@OT_0627 A
         SET A.CD = โ€˜Aโ€™
         WHERE A.CD = I4.CD;
         END LOOP;
         COMMIT;
END;

/

Note

An exception occurs in the UPDATE part and error 02055 is generated.
Update by distributed transaction is not allowed.

 

3. Query After Workaround

CREATE OR REPLACE PROCEDURE SP_GET_UPDATE
AS
BEGIN
FOR I4 IN (SELECT A.CD , B.CD_NO,B.TEXT FROM T8_ORA A, T8_TIB@OT_0627 B WHERE A.CD=B.CD)
         LOOP
         UPDATE T7_TIB@OT_0627 A
         SET A.CD = โ€˜Zโ€™
         WHERE A.CD = I4.CD;
         END LOOP;
         COMMIT;
END;

CREATE OR REPLACE PROCEDURE FN_DIS_TEST
AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
FOR I IN (SELECT * FROM T8_ORA)
         LOOP
         INSERT INTO T7_TIB@OT_0627 VALUES (I.CD, I.CD_NO,I.TEXT);
         END LOOP;

FOR I2 IN (SELECT * FROM T9_ORA)
         LOOP
         INSERT INTO T7_TIB@OT_0627 VALUES (I2.CD, I2.CD_NO,I2.TEXT);
         END LOOP;

FOR I3 IN (SELECT * FROM T10_ORA)
         LOOP
         INSERT INTO T7_TIB@OT_0627 VALUES (I3.CD, I3.CD_NO,I3.TEXT);
         END LOOP;
         SP_GET_UPDATE;

COMMIT;

END;

/

Note

The last UPDATE part is separated into a procedure, and this procedure is assigned as an autonomous transaction.

 

 
 

Related to