Hi All,
Following example demonstrates the issue in oracle 11G ( may find same issue in other versions too ).
SQL> show user
USER is "SCOTT"
SQL> create or replace
2 procedure p1
3 is
4 begin
5 null;
6 end;
7 /
Procedure created.
SQL> create or replace
2 package scott
3 is
4 procedure p2;
5 end;
6 /
Package created.
SQL> create or replace
2 package body scott
3 is
4 procedure p2
5 is
6 begin
7 null;
8 end;
9 end;
10 /
Package body created.
SQL> exec p1();
PL/SQL procedure successfully completed.
SQL> exec scott.p1();
BEGIN scott.p1(); END;
*
ERROR at line 1:
ORA-06550: line 1, column 13:
PLS-00302: component 'P1' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Similar incident was reported by one of our ERP customer and part of ERP system had been failed for few days due to this.
I don't understand why this was allowed by Oracle. Please review.
Ref :Schema Owner couldn't access own objects with schema prefix but works fine without schema.
Thank you,
Rohana.