CREATE TABLE `jti_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 NOT NULL,
  `INT_TYPE` varchar(255) NOT NULL,
  `COPY_TO_STG` varchar(12) DEFAULT 'NO',
  `NEW_STG_SEQ` int(11) DEFAULT NULL,
  `COPY_TO_STG_DATE` datetime DEFAULT NULL,
  PRIMARY KEY (`INT_ID`)
) ENGINE=MyISAM AUTO_INCREMENT=43670 DEFAULT CHARSET=utf8$$

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" ;

CREATE OR REPLACE PROCEDURE COPY_MYSQL_WEB_INT_TO_STG(
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"."jti_member_interact"@"JTIPARTN_WEBSITE" des
    WHERE "NEW_STG_SEQ" IS NULL;
    COMMIT;
    FOR REC IN (SELECT  STG_INT_ID, INSERTING_DATE, MYSQL_INT_ID FROM MEMBER_INTERACT_MYSQL_STG)
    LOOP
        UPDATE "jtipartn_mydb"."jti_member_interact"@"JTIPARTN_WEBSITE" SRC
        SET "COPY_TO_STG"     = 'YES',
            "NEW_STG_SEQ"     = REC.STG_INT_ID,
            "COPY_TO_STG_DATE" = REC.INSERTING_DATE
        WHERE SRC.INT_ID = REC.MYSQL_INT_ID;
    END LOOP;
--    COMMIT;
END;

SELECT INT_ID
FROM "jtipartn_mydb"."jti_member_interact"@"JTIPARTN_WEBSITE";

WHERE SRC."INT_ID" = REC.MYSQL_INT_ID;