Bug 445628

This was tested on an Oracle 9 database. Oracle logged this as bug 445628 in 1997. Then they closed it saying it was not a bug. Either way, it's interesting so here is a worked example. First create two schemas, one to create a stored procedure and another to own a table:

SQL> conn / as sysdba
Connected.
SQL> create user code_owner identified by code_owner
  2  /

User created.

SQL> grant create session, create procedure to code_owner
  2  /

Grant succeeded.

SQL> create user table_owner identified by table_owner
  2  default tablespace user_data
  3  quota 1m on user_data
  4  /

User created.

SQL> grant create session, create table to table_owner
  2  /

Grant succeeded.

SQL>

Now login as code_owner, create a stored procedure and allow table_owner to execute it:

SQL> conn code_owner/code_owner
Connected.
SQL> create procedure do_nothing as
  2  begin
  3  null;
  4  end;
  5  /

Procedure created.

SQL> grant execute on do_nothing to table_owner
  2  /

Grant succeeded.

SQL>

Now login as table_owner and execute code_owner's stored procedure:

SQL> conn table_owner/table_owner
Connected.
SQL> exec code_owner.do_nothing();

PL/SQL procedure successfully completed.

SQL>

So far so good but if table_owner creates a table with the same name as the schema whose code he wants to execute, that code stops working. I guess this is because Oracle thinks that he is trying to execute his own table:

SQL> create table code_owner (col1 number)
  2  /

Table created.

SQL> exec code_owner.do_nothing();
BEGIN code_owner.do_nothing(); END;

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

SQL>

The solution is to drop the table:

SQL> drop table code_owner
  2  /

Table dropped.

SQL>

Then the code starts to work again:

SQL> exec code_owner.do_nothing();

PL/SQL procedure successfully completed.

SQL>