Today, I received a friend's help:
When a user creates a test library stored procedure, it is normal to execute the procedure when the stored procedure owner logs in, but if the process is executed with the schema name in the command, the error ORA-06550 PLS-00302.
Scene:
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>
Solution Ideas:
The first thing that comes to mind is the issue of permissions, and even if you execute your own procedure, you need to be explicitly empowered? So he gave himself the right to:
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>
Still error, then check John's system permissions:
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
The suspicion is that the stored procedure was created in the caller's way, resulting in the need to execute the user with execute any procedure permission, thus adding permissions to the John User:
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.
Try again:
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>
The situation remains.
Or doubt about what was created in the caller's way, and then try to create it the way it was created by default:
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>
It is basically possible to judge that it is irrelevant to the way the caller created it.
Start searching online to find similar articles:
Http://www.itpub.net/thread-1834519-1-1.html
Similarly, the problem is caused by the public role, where a synonym has the same name as the schema.
Then, start to view the synonyms for public:
Sql> Select Owner,synonym_name from dba_synonyms where synonym_name= ' JOHN ';
No rows selected
There is no such synonym. Here, the author basically guessed, there may be the same name space with the Schema_name object, so look at:
Sql> Select Owner,object_name,object_type from dba_objects where object_name= ' JOHN ';
OWNER object_name object_type
---------- ------------------------------ --------------------------------------
John John TABLE
Sure enough, there was a record of joy, like catching straws.
So rename (because it is a test environment):
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>
Sure enough, the reason was found.
Warning:
The object of the current network environment should be noted, do not have the same name as schema_name, otherwise it is easy to cause ambiguity, execution because of the name space problems, resulting in some stored procedures, functions and other execution will be problematic.
Extended test:
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>
This article is from "Master Wang" blog, please be sure to keep this source http://johnnywong.blog.51cto.com/1997751/1612024
Weird ORA-06550 PLS-00302 mistake.