Ora-02069 When Using a Local Function While Updating a Remote Table [ID 342320.1]

Ora-02069 When Using a Local Function While Updating a Remote Table [ID 342320.1]

转到底部

修改时间:2012-12-14类型: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.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.