mysql - ORA-02070 : database [MySQL] does not support subqueries in this context. - 甲骨文

Question

我有以下表格:

MySQL 表:member_interact

CREATE TABLE `member_interact` (
  `INT_MEMBER_ID` int(11) NOT NULL,
  `INT_ID` int(11) NOT NULL AUTO_INCREMENT,
  `INT_SOURCE` varchar(1) NOT NULL,
  `INT_DATE` datetime DEFAULT NULL,
  `INT_TYPE` varchar(30) NOT NULL,
  `COPY_TO_STG` varchar(12) NOT NULL DEFAULT 'NO',
  `NEW_STG_SEQ` int(11) DEFAULT NULL,
  `COPY_TO_STG_DATE` datetime DEFAULT NULL,
  PRIMARY KEY (`INT_ID`),
  UNIQUE KEY `INT_ID_UNIQUE` (`INT_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=31246 DEFAULT CHARSET=utf8$$

Oracle 表:MEMBER_INTERACT_MYSQL_STG

  CREATE TABLE "JTI_HTP"."MEMBER_INTERACT_MYSQL_STG" 
   (    "INT_MEMBER_ID" NUMBER(10,0) NOT NULL ENABLE, 
    "INT_ID" NUMBER(10,0), 
    "INT_SOURCE" NVARCHAR2(1) NOT NULL ENABLE, 
    "INT_DATE" DATE, 
    "INT_TYPE" NVARCHAR2(30) NOT NULL ENABLE, 
    "INSERTING_DATE" DATE, 
    "MYSQL_ID" NUMBER(12,0)
   ) SEGMENT CREATION DEFERRED 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  TABLESPACE "USERS" ;

基本上，我需要将 member_interact 复制到 member_interact_mysql_stg 并添加新列，如下所示:

1. MEMBER_INTERACT_MYSQL_STG.STG_INT_ID = NEW_SEQ
2. MEMBER_INTERACT_MYSQL_STG.MYSQL_INT_ID = MEMBER_INTERACT.INT_ID
3. MEMBER_INTERACT.COPY_TO_STG = 'YES'，一旦复制操作完成。
4. MEMBER_INTERACT.NEW_STG_SEQ = MEMBER_INTERACT_MYSQL_STG.STG_INT_ID

我创建了以下程序:

create or replace PROCEDURE COPY_MYSQL_MOB_INT_TO_STG(
    P_BATCH_NO IN NUMBER)
IS
  CURSOR src
  IS
    SELECT *
    FROM "jtipartn_mydb"."MEMBER_INTERACT"@"JTIPARTNER_HTP"
    WHERE "NEW_STG_SEQ" IS NULL
    AND "INT_ID" <= 7000;

    STG_INT_ID NUMBER;
BEGIN
  FOR des IN src
  LOOP
    STG_INT_ID := STG_SEQ.NEXTVAL;
    INSERT
    INTO MEMBER_INTERACT_MYSQL_STG VALUES
      (
        DES.INT_MEMBER_ID,
        STG_INT_ID,
        des.int_source,
        des.int_date,
        des.int_type,
        NULL,
        DES.INT_ID
      );
      UPDATE_COPIED_MEMBER_INTERACT(STG_INT_ID, DES.INT_ID);
           COMMIT;
  END LOOP;

END;

---

create or replace PROCEDURE UPDATE_COPIED_MEMBER_INTERACT( STG_INT_ID IN NUMBER, MYSQL_INT_ID IN NUMBER)
IS
BEGIN
    UPDATE "jtipartn_mydb"."MEMBER_INTERACT"@"JTIPARTNER_HTP"
    SET "COPY_TO_STG"     = 'YES',
        "NEW_STG_SEQ"       = STG_INT_ID
    WHERE "INT_ID" = MYSQL_INT_ID;
END;

事实上，有一个过程可以更新两个表，但由于我收到的错误，我发现将过程分成两部分可能更容易消除错误。我得到的错误是:

ORA-02047: 无法加入正在进行的分布式事务ORA-06512: 在“JTI_HTP.UPDATE_COPIED_MEMBER_INTERACT”，第 19 行ORA-06512: 在“JTI_HTP.COPY_MYSQL_MOB_INT_TO_STG”，第 28 行ORA-06512: 在第 6 行

第 19 行是调用第二个过程的地方。我怀疑这与 MySQL 表更新有关。

更新

我已经根据@Maheswaran Ravisankar 的回答更新了我的脚本，如下所示:

CREATE OR REPLACE PROCEDURE COPY_MYSQL_MOB_INT_TO_STG_V3(
    P_BATCH_NO IN NUMBER)
IS
BEGIN
    INSERT INTO MEMBER_INTERACT_MYSQL_STG
    SELECT "INT_MEMBER_ID",
      STG_SEQ.NEXTVAL,
      "INT_SOURCE",
      "INT_DATE",
      "INT_TYPE",
      CURRENT_DATE,
      "INT_ID"
    FROM "jtipartn_mydb"."MEMBER_INTERACT"@"JTIPARTNER_HTP" des
    WHERE "NEW_STG_SEQ" IS NULL; 

    UPDATE "jtipartn_mydb"."MEMBER_INTERACT"@"JTIPARTNER_HTP" A
    SET "COPY_TO_STG" = 'YES',
      "NEW_STG_SEQ"   =
      (SELECT STG_INT_ID 
      FROM MEMBER_INTERACT_MYSQL_STG B 
       WHERE A."INT_ID" = B.STG_INT_ID
      );
END;

但是，我收到错误ORA-02070:数据库 JTIPARTNER_HTP 在此上下文中不支持子查询。

Answer 1

    INSERT
    INTO MEMBER_INTERACT_MYSQL_STG
      SELECt
        DES.INT_MEMBER_ID,
        STG_SEQ.NEXTVAL,
        des.int_source,
        des.int_date,
        des.int_type,
        NULL,
        DES.INT_ID
     FROM "jtipartn_mydb"."MEMBER_INTERACT"@"JTIPARTNER_HTP" des
     WHERE "NEW_STG_SEQ" IS NULL
  /*  RETURNING STG_INT_ID BULK COLLECT INTO SOME RECORD; --Needed if only processed in array!*/

然后像下面这样更新，

UPDATE "jtipartn_mydb"."MEMBER_INTERACT"@"JTIPARTNER_HTP" A
SET "COPY_TO_STG"     = 'YES',
    "NEW_STG_SEQ"     = (SELECT STG_INT_ID FROM MEMBER_INTERACT_MYSQL_STG B
                         WHERE A.INT_ID = B.INT_ID);

(或)

FOR REC IN (SELECT  STG_INT_ID,INT_ID FROM MEMBER_INTERACT_MYSQL_STG)
LOOP
    UPDATE "jtipartn_mydb"."MEMBER_INTERACT"@"JTIPARTNER_HTP" A
    SET "COPY_TO_STG"     = 'YES',
        "NEW_STG_SEQ"     = REC.STG_INT_ID
    WHERE A.INT_ID = REC.INT_ID;
END LOOP;