2

I have the following tables:

MySQL table: jti_member_interact

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$$

Oracle table: 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" ;

Basically, I needed to copy jti_member_interact into member_interact_mysql_stg with adding new columns as follow:

  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', once the copy operation is completed.
  4. MEMBER_INTERACT.NEW_STG_SEQ = MEMBER_INTERACT_MYSQL_STG.STG_INT_ID

I have created the following procedure:

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;

The procedure compiles successfully. However, when I run the procedure, I get the following error:

[MySQL][ODBC 5.1 Driver][mysqld-5.5.31-30.3-log]Unknown column 'jtipartn_mydb.jti_member_interact.INT_ID' in 'where clause' {42S22,NativeErr = 1054} ORA-02063: preceding 2 lines from JTIPARTN_WEBSITE

Apparently, the error I get has to do with MySQL table jti_member_interact specially, the column INT_ID. I double checked the name, it is correct! I tried to change it to something else but I got the same error.

I search this error and I found it quite common in some MySQL versions (version 5). I could not find a way to fix my problem (I do not use any trigger here or USING statement). In addition, my knowledge in MySQL is very limited. More interesting is that I ran the same procedure on another MySQL table that has identical structure and column names and it worked perfectly!

When I query the column in normal query such as:

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

I get NO errors on both DB's (oracle & MySQL)! Only UPDATE statement cause errors. In addition, I tried instead of SRC.INT_ID in where clause to use "INT_ID". I got the same error. Using without double quote has the same the problem. I have created another identical MySQL table and tried to update it and I got the same error. The only difference between the table that I can update successfully and the the table I cannot, is that they belong to different MySQL databases (in different locations). But I do not know how to solve this problem.

migrated from stackoverflow.com Aug 27 '14 at 8:47

This question came from our site for professional and enthusiast programmers.

  • What if you try without the double quotes in "INT_ID" and is it a typo? The error you posted have is int_iid! – OracleUser Aug 13 '14 at 4:02
  • @MaheswaranRavisankar I have tried, I got the same error! INT_IID was a typo. Thanks for pointing it out – Hawk Aug 13 '14 at 4:05
  • 1
    I suspect.. It is not the INT_ID but the sequence.. Because we query a dblink table.. But use oracle sequence in select.. – OracleUser Aug 13 '14 at 4:20
  • @MaheswaranRavisankar I used the same procedure with another identical table and it worked fine. Could it be because I am using the same sequence? My ultimate goal is copy several MySQL tables to one oracle table so all will share same sequence in oracle! – Hawk Aug 13 '14 at 4:26
  • 1
    Great! last question, is it the insert or update causing issue? You see the records inserted into oracle? – OracleUser Aug 13 '14 at 4:36
1

by experience sometimes creating oracle tables by using ODBC produce errors.

so please try to do the following:

  1. create the table manually on oracle db by using sql plus.
  2. try to use capital letters for table and column names without double quotes (example: use TABLE_NAME not table_name or Table_Name)
  3. try to run your application to check if any errors appears.

also in where clause you have to check it. try:

WHERE SRC."INT_ID" = REC.MYSQL_INT_ID;

does it works?

  • I did create oracle table on oracle db. The problem is when I update MySQL table from oracle using ODBC I get this error. – Hawk Aug 13 '14 at 10:49
  • did you drop it and recreate it with capitals without double quotes, if so, then you have to add exception to your procedure to catch which record cause this error, also check the line: WHERE SRC.INT_ID = REC.MYSQL_INT_ID; – Eng. Samer T Aug 13 '14 at 12:11
  • I've done that, unfortunately I got the same error. More disappointing is that I have another table using the same script I used to create the first table for which the procedure ran successfully. I got the same error. The only thing I can think about is that both tables are in different DB's (different hosting). So the table the procedure fails with, is the one that I can query but I cannot update and I get the error above. – Hawk Aug 14 '14 at 9:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.