2014年10月30日 星期四

執行Procedure 對Remote Database 做DML 動作時發生ORA-2069 錯誤訊息

Ora-02069 When Using a Local Function While Updating a Remote Table (Doc ID 342320.1) 這篇文章有提到:無法使用 Local Function 針對 Remote Database做 DML ( insert / update / delete) 動作,否則就會發生 ORA-2069 錯誤訊息。
暫時解決方式如下:
1. 將 session level 的 global_name 設成 true:alter session set global_names=true
2. 將 function 建在 Remote Database 上面
3. PL/SQL Code 中,盡可能的不使用function
4. 先將要處理的資料放在Local Table / Local Temp Table 中,當對 Remove Database 做DML 時,再拿出來引用


PS:
Doc ID 342320.1:

APPLIES TO:

Oracle Server - Enterprise Edition - Version 9.2.0.1 to 11.2.0.2 [Release 9.2 to 11.2]
Information in this document applies to any platform.

SYMPTOMS

In Oracle Server executing the following update:
update t1
set c1=c1
where c2=pkg_date.Fn_GetDate;

where pkg_date.Fn_GetDate is a function of a local package that returns a sysdate,
and t1 is a synonym that points to a remote table, defined through a database link.

returns
"ORA-02069: global_names parameter must be set to TRUE for this operation"
on the function pkg_date.Fn_GetDate on the where condition.

CHANGES

CAUSE

Because of a limitation, it is not possible to use a local function when doing a dml operation on a remote table .When this is attempted, the ora-2069 is raised.

SOLUTION

These are possible workaround to avoid the ora-2069 error in the described scenario
1. Use global_names=true.  This can be done on a session basis: "alter
    session set global_names=true".  If you are having problems getting this
    to work, it is no doubt a configuration problem.  Please open a separate
    TAR for this if you can't figure it out.
2. Put the function to be used at the remote site.
3. Put a wrapper function at the remote site which calls the actual function
    over a database link back to the local site.
4. Include in the "from" the "dual" table. You'll have a Cartesian product (with dual) and the functions will be applied in the calling side, hence some performance issues can be raised.

REFERENCES

BUG:671775 - GET A ORA-2069 OR ORA-2019 WHEN TRYING TO USE A FUNCTION WITH A DB_LINK