Inserting Data To View Mapped Via Dblink To Remote Table Returns Ora-02069 [ID 467787.1]

Inserting Data To View Mapped Via Dblink To Remote Table Returns Ora-02069 [ID 467787.1]

转到底部

修改时间:2012-11-10类型:PROBLEM状态:MODERATED优先级:3

注释 (0)

In this Document

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.

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

Global_names is not set to true. And no Database link to the local database is existing on the remote database.

In this scenario, you need the following to have the insert statement working :

1. Global_names set to true.
2. Database link to the local database has to be exist on the remote database.

When call the PLSQL function inline with the insert statement. Its needs to execute and return the
output to the remote session via the database link (the local one) . After the insert the remote
session will need to send acknowledge to the local database , and therfore will need another
database link on the remote site connecting to the local database.
For this reason, its mandatory to have the global_names set to true, this enables oracle to
automatically detect the remote database link name, which will be the same like the local database
name (this is the case only if the global_names was set to true).

Solution

-- To implement the solution, please execute the following steps::

1. Set the global_names to true (you may do it on the session level if not possible on the system level).

2. Create database link on the remote site connecting to the local one with the same name like the local global_name .
For example on the above test case :

You will need to create database link on ORC2 using the next syntax :

SQL> Create database link ORC1 connect to  Identified by  using 'ORC1'; 

-- Then On the local database ORC1 :

SQL> alter session set global_names=true; 

-- Now we can test the insert statement :

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

1 row inserted.