An Interesting PL/SQL Bug about References

I have struggled a PL/SQL problem a few days ago. The problem was, as Pl/SQL Compiler says, "PLS-00225: subprogram or cursor 'string' reference is out of scope". It was not as easy as Compiler mentions.
Let me show it by an example:

Suppose that you have 2 packages, PACK1 and PACK2. In PACK2, you have a procedure in PACK1 named PACK1 and another procedure that named PACK1 in PACK2 package. When the namings are established like that you will get "PLS-00225: subprogram or cursor 'string' reference is out of scope" error.

Example below CMP.COMPARE and P_TEST.COMPARE exists. In P_TEST, there is a reference for COMPARE package. It Works fine.

SQL> CREATE OR REPLACE PACKAGE CMP IS
2 TYPE t_HashTable IS TABLE OF VARCHAR2(512) INDEX BY VARCHAR2(32);
3
4 TYPE t_SQL IS RECORD(
5 SQL_STATEMENT VARCHAR2(4000),
6 BIND_LIST t_HashTable
7 );
8
9 END CMP;
10 /

Package created

SQL>
SQL> CREATE OR REPLACE PACKAGE BODY CMP IS
2
3 PROCEDURE Compare IS
4 BEGIN
5 NULL;
6 END Compare;
7
8 END CMP;
9 /

Package body created

SQL>
SQL> CREATE OR REPLACE PACKAGE p_test IS END p_test;
2 /

Package created

SQL> CREATE OR REPLACE PACKAGE BODY p_test IS
2
3 TYPE t_SQLList IS TABLE OF CMP.t_SQL INDEX BY VARCHAR2(16);
4
5 PROCEDURE Compare IS
6 BEGIN
7 NULL;
8 END Compare;
9
10 END p_test;
11 /

Package body created

SQL>



Example2 below COMPARE.COMPARE and P_TEST.COMPARE exists. In P_TEST, there is a reference for COMPARE package. Altough there won't seem any errors, PL/SQL Compiler is angry with code.


SQL> CREATE OR REPLACE PACKAGE COMPARE IS
2 TYPE t_HashTable IS TABLE OF VARCHAR2(512) INDEX BY VARCHAR2(32);
3
4 TYPE t_SQL IS RECORD(
5 SQL_STATEMENT VARCHAR2(4000),
6 BIND_LIST t_HashTable
7 );
8
9 END COMPARE;
10 /

Package created

SQL> CREATE OR REPLACE PACKAGE BODY COMPARE IS
2
3 PROCEDURE Compare IS
4 BEGIN
5 NULL;
6 END Compare;
7
8 END COMPARE;
9 /

Package body created

SQL> CREATE OR REPLACE PACKAGE p_test IS END p_test;
2 /

Package created

SQL> CREATE OR REPLACE PACKAGE BODY p_test IS
2
3 TYPE t_SQLList IS TABLE OF COMPARE.t_SQL INDEX BY VARCHAR2(16);
4
5 PROCEDURE Compare IS
6 BEGIN
7 NULL;
8 END Compare;
9
10 END p_test;
11 /

Warning: Package body created with compilation errors

SQL> show errors;
Errors for PACKAGE BODY SYSADM.P_TEST:

LINE/COL ERROR
-------- -------------------------------------------------------------------
3/30 PLS-00225: subprogram or cursor 'COMPARE' reference is out of scope
3/3 PL/SQL: Item ignored

SQL>



Example3 below CMP.COMPARE and P_TEST.COMPARE2 exists.(i changed the name of COMPARE to COMPARE2 in P_TEST) In P_TEST, there is a reference for COMPARE package. It Works fine.

SQL> CREATE OR REPLACE PACKAGE COMPARE IS
2 TYPE t_HashTable IS TABLE OF VARCHAR2(512) INDEX BY VARCHAR2(32);
3
4 TYPE t_SQL IS RECORD(
5 SQL_STATEMENT VARCHAR2(4000),
6 BIND_LIST t_HashTable
7 );
8
9 END COMPARE;
10 /

Package created

SQL> CREATE OR REPLACE PACKAGE BODY COMPARE IS
2
3 PROCEDURE Compare IS
4 BEGIN
5 NULL;
6 END Compare;
7
8 END COMPARE;
9 /

Package body created

SQL> CREATE OR REPLACE PACKAGE p_test IS END p_test;
2 /

Package created

SQL> CREATE OR REPLACE PACKAGE BODY p_test IS
2
3 TYPE t_SQLList IS TABLE OF COMPARE.t_SQL INDEX BY VARCHAR2(16);
4
5 PROCEDURE Compare2 IS
6 BEGIN
7 NULL;
8 END Compare2;
9
10 END p_test;
11 /

Package body created

SQ