Skip to Main Content
  • Questions
  • Proc Valid Despite Non-Existent Tables

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Mark.

Asked: August 16, 2017 - 6:11 pm UTC

Last updated: August 17, 2017 - 9:48 am UTC

Version: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit

Viewed 1000+ times

You Asked

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 package

create 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.

and Chris said...

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?



Is this answer out of date? If it is, please let us know via a Comment

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library