环境描述：

源库：aix5.3+oracle9208的用户t创建到目标库windows2008x64+oracle10205的user1用户下的数据库连接。

sql可以查询select * from table1@bdly;，但是放在存储过程里就报错:ORA-04052

即使创建成同义词也不行：http://blog.csdn.net/jay198746/article/details/5651285

最后通过创建成视图view后，存储过程编译通过。

错误信息：

Compilation errors for PROCEDURE LYJK.SP_RPT_INCOME_DTWO

Error: PL/SQL: ORA-04052: error occurred when looking up remote object ADMIN.TAR_BOSS_INCOME@BDLY
       ORA-00604: error occurred at recursive SQL level 1
       ORA-03106: fatal two-task communication protocol error
       ORA-02063: preceding line from BDLY
Line: 15
Text: select 1 DataType, '1月' monthname, sum(ic.JANINCOME) curmonthvalue , sum(il.JANINCOME) lastmonthvalue from tar_boss_income ic

Error: PL/SQL: SQL Statement ignored
Line: 15
Text: select 1 DataType, '1月' monthname, sum(ic.JANINCOME) curmonthvalue , sum(il.JANINCOME) lastmonthvalue from tar_boss_income ic

解决办法：

create  database link BDLY connect to user1 identified by user1 using 'bdly';
create view table1 as select * from table1@bdly;

CREATE OR REPLACE PROCEDURE SP_RPT_INCOME_DTWO 
(
  p_code in varchar2,

  return_cursor out SYS_REFCURSOR
) Authid Current_User 
as
  v_cursor sys_refCURSOR; 
BEGIN

解决参考文档：http://blog.csdn.net/jay198746/article/details/5651285

确认是Bug.

ORA-00604 ORA-04052 ORA-03106 ORA-02063 With Any PLSQL Call Via Database Link To Remote Machine With Different OS Platform [ID 559452.1]
		修改时间 12-JAN-2012     类型 PROBLEM     状态 PUBLISHED

In this Document
  Symptoms
  Changes
  Cause
  Solution
  References

---

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.8 and later   [Release: 9.2 and later ]
Information in this document applies to any platform.
***Checked for currency on 06-June-2011***

Symptoms

The following errors may occur when running PLSQL code which executes a SQL statement referencing a database on a remote machine.

Changes

This issue occurs only when connecting two databases on two different machines with different operating systems (with different endian):

for example : Windows <=> AIX , or Linux <=> SPARC

To detect the endian of your platform, you can use the following query: 

Cause

bug 5671074 : ORA-4052/ORA-3106 on create / refresh of materialized view

The above is the DDR description of the bug, but it affects any PLSQL call between different endian platforms and not just materialized views.
Example:
Windows == > little endian.
AIX == > big endian.

This bug only applies to 9.2.0.8 has been solved on 10g and later databases.

Solution

-- To implement the solution, please execute the following steps::
1. Login to Metalink.
2. Find and download the patch for  bug 5671074 , PATCH 5671074 :
https://updates.oracle.com/download/5671074.html
3. Review the attached readme file for the patch installation notes.
4. Apply the patch.
5. Verify the issue after applying the patch.

Note: please choose the correct platform when downloading the patch. Normally you need to apply it
only on the side in where you have the PLSQL issue, but in some cases you may need to apply it on
both sides. 


Still have questions ?


 

References

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