诡异的 ORA-06550 PLS-00302错误

21321

今天接到一个朋友的求助：一个用户创建测试库存储过程时，以存储过程所有者登录后，执行过程时正常，但是如果执行过程的命令中带上schema名后，则报错 ORA-06550 PLS-00302错误。
场景：
SQL> show user
USER is "JOHN"
SQL> create or replace procedure p1
  2  authid current_user
  3  as
  4  begin
  5    null;
  6  end p1;
  7  /

Procedure created.
SQL>
SQL> exec p1;

PL/SQL procedure successfully completed.

SQL> exec john.p1;
BEGIN john.p1; END;

           *
ERROR at line 1:
ORA-06550: line 1, column 12:
PLS-00302: component 'P1' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


SQL>
解决思路：

   第一个想到的就是权限问题，即使是执行自己的procedure，也需要显式赋权？于是给自己赋权了：
SQL> grant execute on p1 to john;

Grant succeeded.

SQL> exec john.p1;
BEGIN john.p1; END;

           *
ERROR at line 1:
ORA-06550: line 1, column 12:
PLS-00302: component 'P1' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


SQL> exec p1;

PL/SQL procedure successfully completed.

SQL>
依旧报错，于是查看john的系统权限：
SQL> select * from dba_sys_privs where grantee='JOHN';

GRANTEE                        PRIVILEGE                      ADMIN_OPTION
------------------------------ ------------------------------ -----------------------------
JOHN                           UNLIMITED TABLESPACE               NO
SQL> select * from dba_role_privs where grantee='JOHN';

GRANTEE    GRANTED_ROLE                   ADMIN_OPTION                   DEFAUL
---------- ------------------------------ ------------------------------ ------
JOHN       CONNECT                        NO                             YES
JOHN       RESOURCE                       NO                             YES
怀疑是不是因为存储过程是以调用者方式创建的，导致需要执行用户有execute any procedure权限，于是给john用户增加权限：

SQL> select * from dba_role_privs where grantee='JOHN';

GRANTEE    GRANTED_ROLE                   ADMIN_OPTION                   DEFAUL
---------- ------------------------------ ------------------------------ ------
JOHN       CONNECT                        NO                             YES
JOHN       RESOURCE                       NO                             YES

SQL> select * from dba_sys_privs where grantee='JOHN';

GRANTEE    PRIVILEGE                      ADMIN_OPTION
---------- ------------------------------ ------------------------------
JOHN       CREATE TRIGGER                 NO
JOHN       EXECUTE ANY PROCEDURE          NO
JOHN       CREATE VIEW                    NO
JOHN       ALTER ANY PROCEDURE            NO
JOHN       CREATE TABLE                   NO
JOHN       SELECT ANY DICTIONARY          NO
JOHN       CREATE ANY PROCEDURE           NO

7 rows selected.
再次尝试：

SQL> exec p1;

PL/SQL procedure successfully completed.

SQL> exec john.p1;
BEGIN john.p1; END;

           *
ERROR at line 1:
ORA-06550: line 1, column 12:
PLS-00302: component 'P1' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


SQL>
情况依旧。

还是怀疑跟以调用者方式创建的有关，于是尝试默认创建的方式创建：
SQL> create or replace procedure p2 as begin null;end p2;
  2  /

Procedure created.

SQL> exec p2;

PL/SQL procedure successfully completed.

SQL> exec john.p2;
BEGIN john.p2; END;

           *
ERROR at line 1:
ORA-06550: line 1, column 12:
PLS-00302: component 'P2' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


SQL>
到这里基本可以判断，跟以调用者创建的方式是无关的了。
开始网上查一把，查到类似文章：
http://www.itpub.net/thread-1834519-1-1.html
情况类似，它产生问题的原因是PUBLIC角色中，有一个同义词跟schema同名了。
于是，开始查看PUBLIC的同义词：
SQL> select owner,synonym_name from dba_synonyms where synonym_name='JOHN';

no rows selected
没有类似同义词。到这里，笔者基本猜到了，有可能相同的名字空间中有跟schema_name同名的对象，于是查看之：
SQL> select owner,object_name,object_type from dba_objects where object_name='JOHN';

OWNER      OBJECT_NAME                    OBJECT_TYPE
---------- ------------------------------ --------------------------------------
JOHN       JOHN                           TABLE
果然有一条记录，欣喜，像是抓到救命稻草。
于是rename之（因为是测试环境）：
SQL> alter table john rename to john_bak;

Table altered.

SQL> exec p1;

PL/SQL procedure successfully completed.

SQL> exec john.p1;

PL/SQL procedure successfully completed.

SQL>
果然，原因找到了。

警示：
   现网环境的对象命名时需要注意，不要跟schema_name同名，否则很容易引起歧义，执行时因为名字空间的问题，导致一些存储过程、函数等执行起来会有问题。
延伸测试：

SQL> show user

USER is "JOHN"

VIEW:

SQL> select owner,object_name,object_type from dba_objects where object_name='JOHN';


OWNER      OBJECT_NAME                    OBJECT_TYPE
---------- ------------------------------ --------------------------------------
JOHN       JOHN                           VIEW

SQL> exec p1;

PL/SQL procedure successfully completed.

SQL> exec john.p1;
BEGIN john.p1; END;

           *
ERROR at line 1:
ORA-06550: line 1, column 12:
PLS-00302: component 'P1' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
SQL> drop view john;

SYNONYM:
SQL> create synonym john for test;
SQL>  select owner,object_name,object_type from dba_objects where object_name='JOHN';

OWNER      OBJECT_NAME                    OBJECT_TYPE
---------- ------------------------------ --------------------------------------
JOHN       JOHN                           SYNONYM

SQL> exec p1;

PL/SQL procedure successfully completed.

SQL> exec john.p1;
BEGIN john.p1; END;

           *
ERROR at line 1:
ORA-06550: line 1, column 12:
PLS-00302: component 'P1' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


SQL>