暫時解決方式如下:
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.
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.
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