Weird ORA-06550 PLS-00302 mistake.

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.