Andrew Fraser DBA

Bug (ora-2019 or ora-2069) with User Defined Functions Insert/Update over Database Links

If you try to insert or update across a database link some values called by a user defined function:

insert into mytable@remote (mycol) values ( myfunction ('01-JAN-2009') ) ;

That will fail with either:

ORA-02019: connection description for remote database not found

or

ORA-02069: global_names parameter must be set to TRUE for this operation

There are three workarounds to the problem:

1) create database links in both directions, local->remote and remote->local.

2) Specify “@database link name” when calling your user defined function, like this:

insert into mytable@remote (mycol) values ( myfunction@remote ('01-JAN-2009') ) ;

That requires that the function exist in the remote database.

3) Insert the data first into a local table, then copy that data over to the remote database, like this:

insert into mytable_local values ( myfunction ('01-JAN-2009') ) ;
insert into mytable@remote select * from mytable_local ;
delete mytable_local ;

And a fourth option, of pursuing a complete fix:
4) Raise this as a bug with Oracle Support and have them provide a complete fix, rather than resort to workarounds. This issue was actually logged as bug number 671775 way back in 1998, but was closed off on the grounds that there were easy workarounds available.

Reference – My Oracle Support (Metalink) Note 342320.1