10 Replies Latest reply: May 1, 2012 12:36 PM by Tubby RSS

    Error in Merge command

    787094
      I have two database, one is 10g R2 as server and another one is 9i R1 as remote database.
      I have create public database link from the 9i database and execute MERGE command from 9i database.
      But, I got the following error :

      MERGE INTO TERRITORY@mv a
      *
      ERROR at line 1:
      ORA-02021: DDL operations are not allowed on a remote database

      Please, provide me the solution....
      Thanks
      Khalil
        • 1. Re: Error in Merge command
          ajallen
          Move the procedure that does the merge to the remote database and 'pull' the information you are going to merge in - instead of 'pushing' the merge to the remote database.
          • 2. Re: Error in Merge command
            787094
            I am not getting the points actually, please explain clearly....
            Thanks
            Khalil
            • 3. Re: Error in Merge command
              krramnik
              Run the merge from 10g database(Destination).
              If required create db link from 10g to 9i.
              • 4. Re: Error in Merge command
                krramnik
                Try to run the merge from 10g database(Destination).
                If required create db link from 10g to 9i.
                • 5. Re: Error in Merge command
                  787094
                  From 10g database it is ok. but i need to run from 9i r1 database. It is possible to run from 9i R1 database ??
                  Please help...
                  Thanks
                  Khalil
                  • 6. Re: Error in Merge command
                    Tubby
                    784091 wrote:
                    I have two database, one is 10g R2 as server and another one is 9i R1 as remote database.
                    I have create public database link from the 9i database and execute MERGE command from 9i database.
                    But, I got the following error :

                    MERGE INTO TERRITORY@mv a
                    *
                    ERROR at line 1:
                    ORA-02021: DDL operations are not allowed on a remote database

                    Please, provide me the solution....
                    Thanks
                    Khalil
                    The error message is telling you DDL is not allowed, that's not an error message i'd expect to see from a merge command (which does only DML).

                    What EXACTLY are your 2 Oracle versions?
                    select * from v$version
                    9i and 10g are labels given to market Oracle, they are not actual version numbers.
                    • 7. Re: Error in Merge command
                      787094
                      Version of 9i :

                      Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
                      PL/SQL Release 9.0.1.1.1 - Production
                      CORE 9.0.1.1.1 Production
                      TNS for 32-bit Windows: Version 9.0.1.1.0 - Production
                      NLSRTL Version 9.0.1.1.1 - Production


                      Version of 10g :

                      Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
                      PL/SQL Release 10.2.0.3.0 - Production
                      CORE 10.2.0.3.0 Production
                      TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
                      NLSRTL Version 10.2.0.3.0 - Production

                      Pls...
                      Thanks
                      Khalil
                      • 8. Re: Error in Merge command
                        787094
                        MERGE INTO TERRITORY@mv a
                        USING(SELECT
                        teri_code,
                        teri_name,
                        teri_add1,
                        teri_add2,
                        dp_code,
                        edate,
                        teri_status,
                        trndate,
                        trnstatus,
                        finalst
                        FROM Territory
                        ) b
                        ON (a.teri_code=b.teri_code)
                        WHEN MATCHED THEN
                        UPDATE set
                        a.teri_name=b.teri_name,
                        a.teri_add1=b.teri_add1,
                        a.teri_add2=b.teri_add2,
                        a.dp_code=b.dp_code,
                        a.edate=b.edate,
                        a.teri_status=b.teri_status,
                        a.trndate=b.trndate,
                        a.trnstatus=b.trnstatus,
                        a.finalst=b.finalst
                        WHEN NOT MATCHED THEN
                        INSERT
                        (
                        a.teri_code,
                        a.teri_name,
                        a.teri_add1,
                        a.teri_add2,
                        a.dp_code,
                        a.edate,
                        a.teri_status,
                        a.trndate,
                        a.trnstatus,
                        a.finalst)
                        VALUES
                        (
                        b.teri_code,
                        b.teri_name,
                        b.teri_add1,
                        b.teri_add2,
                        b.dp_code,
                        b.edate,
                        b.teri_status,
                        b.trndate,
                        b.trnstatus,
                        b.finalst
                        )
                        /
                        • 9. Re: Error in Merge command
                          787094
                          anybody help me, I am in serious problem . Please....
                          Thanks
                          Khalil
                          • 10. Re: Error in Merge command
                            Tubby
                            784091 wrote:
                            Version of 9i :

                            Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
                            PL/SQL Release 9.0.1.1.1 - Production
                            CORE 9.0.1.1.1 Production
                            TNS for 32-bit Windows: Version 9.0.1.1.0 - Production
                            NLSRTL Version 9.0.1.1.1 - Production
                            Well, you're working with a very old and unpatched version here.

                            It looks like you've hit a bug based on that version.

                            Your options would seem to be
                            1) upgrade to a supported version
                            2) check metalink for instances of this bug, for any potential workarounds (assuming you have a valid account for metalink)
                            3) run the merge from the 10.2.0.3 instance which you say works fine
                            4) rewrite the mege statement as an update statement and an insert statement
                            5) .... that's all i can think of, others may have more options for you

                            Cheers,