Chinaunix首页 | 论坛 | 博客
  • 博客访问: 2057337
  • 博文数量: 200
  • 博客积分: 2413
  • 博客等级: 大尉
  • 技术积分: 3067
  • 用 户 组: 普通用户
  • 注册时间: 2011-04-01 22:07
  • 认证徽章:
文章分类

全部博文(200)

文章存档

2018年(2)

2017年(8)

2016年(35)

2015年(14)

2014年(20)

2013年(24)

2012年(53)

2011年(44)

分类: Oracle

2013-01-04 16:11:27

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


Symptoms

Cause

Solution

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.
阅读(1807) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册