In this Document
  Symptoms
  Changes
  Cause
  Solution
  References

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.8
Information in this document applies to any platform.

Symptoms

After applying the 9.2.0.8 patchset, compilation of functions and/or materialized view refreshes that reference remote databases via database links start failing with:

ORA-04052: error occurred when looking up remote object OMS.PREMISE@JEAUSER_OMPR_L.WORLD
ORA-00604: error occurred at recursive SQL level 1
ORA-03120: two-task conversion routine: integer overflow
ORA-02063: preceding line from JEAUSER_OMPR_L

The operation had previously worked before applying the 9.2.0.8 patchset.

Changes

The 9.2.0.8 patchset has been applied.

Cause

Bug 5671074 "ORA-4052/ORA-3106 on Create / Refresh of Materialized View", can cause this problem.  Contrary to the bug abstract and description, this bug does not just apply to materialized view creation and refresh.  It can occur for any PL/SQL that references a remote object via database link where the 'Endianness' of the two platforms differs.  If one platform. is 'Big-Endian', and another platform. is 'Little-Endian', then this bug can be triggered between them.  A list of the endianness of different platforms can be obtained from the following query (note that the v$transportable_platform. view is only available in 10.1 and above):

SQL> select * from v$transportable_platform;

PLATFORM_ID   PLATFORM_NAME                      ENDIAN_FORMAT
-----------   ------------------------------     --------------
1             Solaris[tm] OE (32-bit)            Big
2             Solaris[tm] OE (64-bit)            Big
7             Microsoft Windows NT               Little
10            Linux IA (32-bit)                  Little
6             AIX-Based Systems (64-bit)         Big
3             HP-UX (64-bit)                     Big
5             HP Tru64 UNIX                      Little
4             HP-UX IA (64-bit)                  Big
11            Linux IA (64-bit)                  Little
15            HP Open VMS                        Little
8             Microsoft Windows IA (64-bit)      Little
9             IBM zSeries Based Linux            Big
13            Linux 64-bit for AMD               Little
16            Apple Mac OS                       Big
12            Microsoft Windows 64-bit for AMD   Little

Also, only the local database where the database link resides has to be 9.2.0.8 to encounter this bug.

Solution

To implement the solution, it will be necessary to apply the one-off patch for Bug 5671074 to the 9.2.0.8 database.  One-off patches to 9.2.0.8 for most platforms are already available for download on Metalink.

References

BUG:5671074 - MATERIALIZED VIEW REFRESH FAILS WITH ORA-04052 ERROR