Skip to main content

Search

Welcome to Tibero GTS!

DBLINK Workaround (Using Multiple AND Clause Columns)

Document TypeㅣTechnical Information

CategoryㅣInterface/Integration

Document NumberㅣTIITI023

 

Overview

When there are restrictions on using multiple AND clause columns in DBLink from Oracle to Tibero, the following workaround is provided.
 
 

Method

Include the entire select clause result in the cursor and perform the query using that result.
 

1. Create Test Tables

Tibero

CREATE TABLE T4_TIB (TEST_NO NUMBER, TEST_TEXT VARCHAR(20), UPD_CD VARCHAR(2));
INSERT INTO T4_TIB VALUES (1,’ORACLE’,’N’);
INSERT INTO T4_TIB VALUES (2,’ORACLE’,’N’);
INSERT INTO T4_TIB VALUES (3,’ORACLE’,’N’);
INSERT INTO T4_TIB VALUES (4,’ORACLE’,’N’);


Oracle

CREATE TABLE T4_ORA (CD CHAR, ORA_TEST_NO NUMBER, ORA_TEST_TEXT VARCHAR(20));
INSERT INTO T4_ORA VALUES (‘A’,1,’ORACLE’);
INSERT INTO T4_ORA VALUES (‘A’,2,’ORACLE’);
INSERT INTO T4_ORA VALUES (‘A’,3,’ORACLE’);
INSERT INTO T4_ORA VALUES (‘A’,4,’ORACLE’);
 
CREATE TABLE T4_ORA_2 (CD_2 CHAR, ORA_TEST_NO_2 NUMBER, ORA_TEST_TEXT_2 VARCHAR(20));
INSERT INTO T4_ORA_2 VALUES (‘A’,1,’ORACLE’);
INSERT INTO T4_ORA_2 VALUES (‘A’,2,’ORACLE’);
INSERT INTO T4_ORA_2 VALUES (‘B’,3,’ORACLE’);
INSERT INTO T4_ORA_2 VALUES (‘B’,4,’ORACLE’);

 

2. Query Before Workaround

UPDATE T4_TIB@OT_0627 T
                   SET UPD_CD = ‘Y’
         WHERE 1=1
           AND (T.TEST_NO, T.TEST_TEXT,T.UPD_CD)
                           IN (
                                              SELECT A.ORA_TEST_NO
                                                        ,A.ORA_TEST_TEXT
                                                        ,’N’ UPD_CD
                                                      FROM T4_ORA A,
                                                                 T4_ORA_2 B
                                                      WHERE A.CD = B.CD_2
                                )
SQL


3. Query After Workaround

DECLARE

CURSOR B_CUR IS (            SELECT distinct A.ORA_TEST_NO

                                                           ,A.ORA_TEST_TEXT

                                                           ,’N’ UPD_CD

                                                         FROM T4_ORA A,

                                                                     T4_ORA_2 B

                                                         WHERE A.CD=B.CD_2

                                   );

BEGIN

          FOR C_CUR IN B_CUR

          LOOP

                  UPDATE T4_TIB@OT_0627 T

                  SET UPD_CD = ‘Y’

                  WHERE TEST_NO = C_CUR.ORA_TEST_NO

                     AND TEST_TEXT = C_CUR.ORA_TEST_TEXT

                     AND UPD_CD = C_CUR.UPD_CD;

                  COMMIT;

          END LOOP;

END;

/

 

 

 

Related to