SQL & PL/SQL

Hi all

I am getting ORA-02069 when trying the following statement
INSERT INTO remote_schema.remote_table@ABCSTG.ch.oracle.com
(col1,....col_n)
select col_1,...,col_n
from local_table

My local set up (Oracle 9.2.0.5) is that GLOBAL_NAMES is false, and the db_domain is CH.ORACLE.COM. The remote set-up (Oracle 9.2.0.4) is that GLOBAL_NAMES is FALSE, and the db_domain is null, and the remote database has a global_name of - for argument's sake - ABCSTG (and the service name is the same).

I would have expected, that if GLOBAL_NAMES is FALSE on the remote db, then my db link would NOT have to be the same as the name of the remote DB. In any case, any db link that I create automagically (sic) gets the default domain (ch.oracle.com) if I neglect to add one.

However, when I issue ALTER SESSION SET GLOBAL_NAMES=TRUE, I get ORA-02085 which tells me that DB link ABCSTG.ch.oracle.com is trying to connect to ABCSTG. Which I suppose is correct behaviour as the db link and the remote target have different names.

I think I must be missing something very obvious...

Just as an after-thought..the following works [poor programming practice, I know :-( ]

for x in (select col_1,....,col_n from local_table) loop
insert into remote_schema.remote_table@ABCSTG.ch.oracle.com
(col_1, ..., col_n)
values
(x.col_1, ....., x.col_n);
end loop;



thanks

Gerard