I can't create a test case because I don't know how to do it.
PACKAGE FOO has PROCEDURES BAR_ONE and BAR_TWO in database ALPHA that query table ALL_RECOGNITION in database BETA via DB Link BETA_LINK.
Pseudo/garbage code for packagecreate or replace PACKAGE BODY "FOO" AS
PROCEDURE BAR_ONE (p_cursor OUT ref cursor)
AS
BEGIN
-- proper syntax for querying table in other db via DB link
open p_cursor for
select COL1 from ALL_RECOGNITION@BETA_LINK where rownum = 1;
END BAR;
PROCEDURE BAR_TWO (p_cursor OUT ref cursor)
AS
BEGIN
-- code missing @BETA_LINK
-- this code was found to be in the valid, COMPILED proc (before the issue with the dblink password)
open p_cursor for
select COL1 from ALL_RECOGNITION where rownum = 2;
END BAR;
END FOO;
Here's what happened.
1. Password used for BETA_LINK changed without notification. Package FOO remained valid and accessible despite bad DB link password. NOTE: Unknown if procedure BAR_ONE or BAR_TWO failed during this time (but likely it would have if it had been executed).
2. A separate proc in package FOO was updated and a compile was attempted. COMPILE failed with "table or view does not exist" in BAR_ONE.
3. Password for BETA_LINK was corrected.
4. Compile of package FOO attempted again. "table or view does not exist" happens in BAR_TWO (expected since "@BETA_LINK" is not present).
Quandry...I see how the package remained valid after the DB Link password was changed. The issue with the password was observed when the package was compiled.
What I cannot figure out is how BAR_TWO didn't cause compile errors in the first place? ALL_RECOGNITION
DOES NOT exist in the local schema as a table, view, synonym, anything...
I hope this makes sense and that there is a simple explanation. BAR_TWO originally had the "@BETA_LINK" text. It must be true that someone removed the "@BETA_LINK" code from BAR_TWO, but I cannot figure out how they got the proc to compile.
This happened in a production system, so the invalid package would have been observed within seconds.
I can't think how the package could have remained valid if it referred to a non-existent object. And without a test case it's going to be difficult to get to the bottom of this.
Of course, the obvious explanation is that BAR_TWO did have the DB link in. And when the other procedure changed, the code in BAR_TWO changed at the same time to remove the DB link.
Or perhaps a synonym/view was dropped during the release that compiled FOO (or shortly after).
Are you 100% certain neither of these are possible?