The solution to the problem of ORA-00604

The customer encountered a problem, described as follows:--Environment is Oracle 9.2.0.8

(Statements and scenes are not true, online search, the situation is consistent)

A db_link was created to connect to another Oracle database. SELECT * from Tablename@dblinkname;

The execution of the simple sentence is fine, but write this SQL into the stored procedure:

Create or replace procedure Prc_test

Is

Begin

INSERT INTO Test

SELECT * from Tablename@dblinkname;

Commit

End

The following error occurred and the stored procedure was not compiled:

Compilation errors for PROCEDURE Prc_test

ERROR:PL/SQL:ORA-04052: Error locating remote object Tablename@dblinkname

ORA-00604: Recursive SQL Layer 1 error occurred

ORA-03106: Fatal Duplex Communication protocol error

ORA-02063: Line immediately (originating from XXX (connected database service name))

Line:4

Text:insert into Test

Error:pl/sql:sql Statement ignored

Line:4

Text:insert into Test

#########################################################

The following Metalink problem solving method, the corresponding patch can be

I have not patched here, just to find out the reason to the customer.

Subject:errors ORA-00604 and ORA-03106 During Procedure compilation

Doc id:577347.1 Type:problem

Modified date:26-may-2008 status:moderated

Applies To:

pl/sql-version:9.2.0.4 to 9.2.0.8

This problem can occur to any platform.

This note applies ' only if ' underlying Oracle Client is below 9.2.0.8 Patch 4

Symptoms

While trying to run Pl/sql code involving queries which access remote objects, your may endup getting below:

Pl/sql:ora-04052:error occurred when looking up remote object <object name>

Ora-00604:error occurred at recursive SQL level 1

Ora-03106:fatal Two-task Communication Proto Error

Ora-02063:preceding Line from AFQ

When the same query was run outside Pl/sql block it runs perfectly.

Cause

You are most probably hitting the Bug 5866805.

Bug 5866805 Duplicate of Bug 5576340 which is itself a duplicate of bug 5671074.

Solution

The Bug 5671074 is fixed in 9.2 0.8 Patch 4 OR above.

This column more highlights: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/