remote dependencygreenspun.com : LUSENET : DBAzine : One Thread
A local database procedure which is accessing a remote procedure through a database link is becoming INVALID after the remote procedure had been re-compiled and after that we never able to execute the local procedure at all...
The scenario is.
Assume initially the remote procedure and the local procedure are compiled and everything is working fine. Database link and a Public synonym are created in the local database for the remote procedure.
The remote procedure: SQL> create or replace procedure test_timestamp (col2 out number) as 2 col1 number; 3 begin 4 col1 := 10; 5 col2 := col1; 6 end; 7 /
Database link: LOCAL:SQL>CREATE PUBLIC DATABASE LINK 'REMOTE.ORACLEDB.COM' CONNECT TO SCOTT IDENTIFIED BY TIGER USING 'REMOTE.ORACLEDB.COM' /
Public Synonym: LOCAL:SQL>CREATE PUBLIC SYNONYM TEST_TIMESTAMP FOR SCOTT.TEST_TIMESTAMP@REMOTE.ORACLEDB.COM / Synonym Created.
Local procedure. SQL> create or replace procedure call_timestamp as 2 col1 number; 3 begin 4 s_test_timestamp(col1); 5 end; 6 /
After months, on a week-end we re-compile the remote rocedure with some mofication to the logic and the defintion of the procedure is not modified at all.
The remote procedure: SQL> create or replace procedure test_timestamp (col2 out number) as 2 col1 number; 3 begin 4 col1 := 999; 5 col2 := col1; 6 end; 7 /
On Monday we noticed that the local procedure "CALL_TIMESTAMP" which is calling the remote "TEST_TIMSTAMP" has become INVALID because of the remote procedure is re-compiled. (which is O.K) But we could not get the local procedure (CALL_TIMESTAMP)work after that..
Even if I try to describe i get the following error. LOCAL:SQL> DESC TEST_TIMESTAMP
02019, 00000, "connection description for remote database not found"
But if I try to qualify like this..gives no problem.
LOCAL:SQL> DESC SCOTT.TEST_TIMESTAMP@REMOTE.ORACLEDB.COM TEST_TIMESTAMP
We dropped and re-created the database link as well as the synonym and still the local procedure "CALL_TIMESTAMP" is not compiling successfully. It does not give any compilation error but the STATUS remains INVALID.
Just to make sure, we commented the reference to TEST_TIMESTAMP and re-compiled the local procedure and it compiled successfully and the status is VALID. And we put back the TEST_TIMESTAMP reference and re-compiled it and the STATUS has become INVALID with no compilation error.
Finally what we did was,
We created totally a new procedure with a different name (say TEST_TIMESTAMP_NEW) but with the same source code as the TEST_TIMESTAMP in the remote database and try to access the new procedure (TEST_TIMESTAMP_NEW) from the local procedure (CALL_TIMESTAMP) and it is working fine.
We just could not figure out what could be the reason. Is it something to do with the time delay ? The time the REMOTE procedure is re-compiled and the time the local procedure is first executed or recompiled is almost 24 hrs.
Actually the REMOTE procedure got re-compiled on SUNDAY morning and the LOCAL procedure has been first accessed only on Monday.
Please guide me on this...
The remote database is on a HP server and the local database is on a NT server. The database version on both server is 22.214.171.124
The "remote_dependencies_mode" parameter in the remote database is set to "TIMESTAMP" and is set to "SIGNATURE" in the local database.
-- Saradha Bavanandam (email@example.com), March 04, 2003