The customer encountered a problem, which is described as follows: -- The environment is ORACLE9.2.0.8 (the statement and scenario are not true, and the online search is the same). A DB_LINK is created to connect to another Oracle database. Select * fromtablename @ dblinkname; the execution of a single sentence is fine, but this SQL statement is written to the stored procedure: createorreplacep
The customer encountered a problem, which is described as follows: -- The environment is ORACLE 9.2.0.8 (the statements and scenarios are not authentic, and the online search is consistent). A DB_LINK is created to connect to another Oracle database. Select * from tablename @ dblinkname; the execution of a single sentence is fine, but this SQL statement is written into the stored procedure: create or replace p
The customer encountered a problem, which is described as follows: -- The environment is ORACLE 9.2.0.8 (the statements and scenarios are not authentic, and the online query is consistent)
A DB_LINK is created to connect to another Oracle database. Select * from tablename @ dblinkname;
There is no problem with executing a single statement, but this SQL statement is written into the stored procedure:
Create or replace procedure prc_test
Is
Begin
Insert into test
Select * from tablename @ dblinkname;
Commit;
End;
The following error occurs:
Compilation errors for PROCEDURE prc_test
Error: PL/SQL: ORA-04052: Error while searching remote object tablename @ dblinkname
ORA-00604: recursive SQL Layer 1 error
ORA-03106: Fatal Duplex Communication Protocol Error
ORA-02063: followed by line (from XXX (name of the Database Service to be connected ))
Line: 4
Text: insert into test
Error: PL/SQL: SQL Statement ignored
Line: 4
Text: insert into test
######################################## #################
To solve the problem on metalink, add the corresponding patch.
-- I have not patched the patch here, but I just want to find out the reason and explain it 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:
PL/SQL-Version: 9.2.0.4 to 9.2.0.8
This problem can occur on any platform.
This note applies only if the 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, you may endup getting below errors:
PL/SQL: ORA-04052: error occurred when looking up remote object
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 is 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.
