Inserting Data To View Mapped Via Dblink To Remote Table Returns Ora-02069 (Doc ID 467787.1)

Last updated on FEBRUARY 24, 2017

Applies to:

Oracle Server - Enterprise Edition - Version 9.2.0.8 to 11.2.0.3 [Release 9.2 to 11.2]
Information in this document applies to any platform.
***Checked for relevance on 06-June-2011***

Symptoms

-- Problem Statement:
Inserting data to view mapped via database link to remote table fails with ORA-02069, And if we set the global_names to true, then will face ORA-04054.

See the next test case for more information :

Test Case  :
========
-- consider that we have two databases ORC1 and ORC2 :

-- On ORC1 :

SQL>
SQL> Create function myFunction ( inParameter in PLS_INTEGER ) return VARCHAR2 is
2 Begin
3 Return '0';
4 End myFunction;
5
6 /

Function created

SQL> select * from dual@orc2;

DUMMY
-----
X

SQL> -- the next step is just for testing :
SQL> Create view  TESTVIEW as
2 Select * from
3 dba_mviews@orc2;

View created

SQL> drop view TESTVIEW;

View dropped

-- Now we will create test table as the following :

SQL> create table test123@orc2(t1 number,t2 varchar2(10));

SQL> Create view TESTVIEW as Select * from system.test123@orc2;

View created

SQL> select * from TESTVIEW ;

T1 T2
---------- ----------

SQL> Insert into TESTVIEW(t1,t2) values(999, to_char ( 1 ) );

1 row inserted

SQL> commit;

Commit complete

SQL> Insert into TESTVIEW( t1,t2 ) values ( 999, myFunction ( 1 ) );

Insert into TESTVIEW( t1,t2 ) values ( 999, myFunction ( 1 ) )

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

SQL> alter session set global_names=true;

Session altered

SQL> Insert into TESTVIEW( t1,t2 ) values ( 999, myFunction ( 1 ) );

Insert into TESTVIEW( t1,t2 ) values ( 999, myFunction ( 1 ) )

ORA-04054: database link ORC1.WORLD does not exist
ORA-02063: preceding line from ORC2

SQL>

 

Note: The error ORA-02069 message shows part of the solution, "global_names parameter must be set to TRUE" But Its not enough, the next parts will help to understand the root cause, and the complete solution.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms