7 Replies Latest reply: Jan 11, 2008 7:19 AM by 416047 RSS

    Global Database Names issue

      Oracle 10gR2, Windows, 64bit.

      This code produces the infamous ORA-02069 Global Names must be set to TRUE error:
      insert into job_bag@dblink1 (job_bag_id, user_id, name, created_date, status)
      (job_bag_id_seq.nextval@dblink2, 80487, 'Some Name or Other', sysdate, 1)
      But the link names don't match the database names, so even setting global_names is a non-starter (and I'm not going to be able to change the link names any time soon).

      Fortunately, I can re-write this like so:
        myseq number;
      select job_bag_id_seq.nextval@dblink1 into myseq from dual;
      insert into job_bag@dblink2 (job_bag_id, user_id, name, created_date, status)
      values (myseq, 80487, 'Some Name or Other', sysdate, 1);
      ...and it all works beautifully. So fixes and workarounds I don't actually need on this occasion, just for a change. But, more out of curiosity than a cry for help: why does the first example fail and the second not?

      I can speculate that one operation and two links is not allowed, whereas the second code splits the operation into two steps and passes. I can also speculate it's got something to do with the use of a sequence as one part of the 'simultaneous' operation.

      But I'm curious if anyone really knows why the first example fails.

      Searching Google, I see plenty of requests for help on ORA-02069, and one or two workarounds of the 'second example' variety shown here, but no actual explanations, so here's hoping that might change this time...
        • 1. Re: Global Database Names issue
          Maran Viswarayar


          Just to ask one thing

          When we update a value to column it treats as a column value rather than any operation mentioned it..it checks the value against the datatype and updates it

          Am i right here?
          • 2. Re: Global Database Names issue
            I'm afraid I don't know what you're asking, really. All I can say by way of reply is that the fact I get a "global names" error indicates that it's seeing the database link, treating it as a database link and producing vaguely appropriate errors accordingly. It's not throwing a data type exception, for example, as it would if I was trying to store some text in a numeric column.

            Again, I can only really repeat that example 2 has the same basic code inserting the same basic data as example 1 tries to do. One works, the other doesn't... I don't think that makes it a datatype issue (but I could well be completely wrong on that, of course).

            I did wonder whether the first example might work better if it was of the form

            ...values ((select sequence@dblink2 from dual),'other data','here')...

            In other words, whether it's just a syntactical issue. But I didn't test that. I'll give it a whirl...
            • 3. Re: Global Database Names issue
              Just so we are on the same hymn-sheet... This code:
              insert into job_bag@dblink1 (job_bag_id, user_id, name, created_date, status)
              ((select job_bag_id_seq.nextval@dblink2 from dual), 80487, 'Some Text', sysdate, 1)
              ...produces an ORA-02287 Sequence not allowed here error. So again, referring back to the earlier reply, I don't think it's a syntactical issue or a datatype issue.
              • 4. Re: Global Database Names issue
                Maran Viswarayar
                Oh thanks

                Maran Viswarayar
                • 5. Re: Global Database Names issue
                  Howard, did you see Metalink note 1047673.6?
                  • 6. Re: Global Database Names issue
                    I have now, thanks to you!

                    That seems to get it in one, doesn't it:

                    The reference to the local sequence has to be qualified so that the remote end knows to call back to the local instance. The qualification is made by appending @local_dbname.domain to the sequence reference if global_names=true.

                    This also neatly explains why, when I quickly created new db links, set global_names to true and then tried running my example 1 code, the execution failed with an error along the lines of 'don't know that db link name'. Our databases don't know how to talk to each other. A special intermediary database (in which these bits of code were running) knows how to talk to all of them -but the call back meant that db1 was trying to contact db2 directly, and that's not possible for us.

                    So two mysteries solved in one 7-word post!! Thanks Yas, very much.
                    • 7. Re: Global Database Names issue
                      You are welcome Howard, this db link behavior was one of the things some developers kept asking me repeatedly about.