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)
values
(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:
declare
myseq number;
begin
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);
end;
...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...