%TYPE Declaration Gives PLS-00302

A developer reported a problem with a %TYPE declaration which was returning PLS-00302 in an Oracle 10 database. The cause turned out to be a variation on a problem which I have already reported. However, as it took me some time to work out, I have reproduced it below. First I created a user, called USER1, who would own a table:

SQL> conn / as sysdba

Connected.

SQL> create user user1

  2  identified by user1

  3  default tablespace users

  4  quota 10m on users

  5  /

 

User created.

 

SQL> grant create session, create table

  2  to user1

  3  /

 

Grant succeeded.

 

SQL>

Then I created a user, called USER2, to declare a variable using %TYPE, basing it on a column in the table created by USER1:

SQL> create user user2 identified by user2

  2  /

 

User created.

 

SQL> grant create session, create synonym

  2  to user2

  3  /

 

Grant succeeded.

 

SQL> 

USER1 created a table called TAB1 and allowed USER2 to see it:

SQL> conn user1/user1

Connected.

SQL> create table tab1

  2  (col1 number)

  3  /

 

Table created.

 

SQL> grant select on tab1 to user2

  2  /

 

Grant succeeded.

 

SQL> 

USER2 declared a variable called BLAH using %TYPE to base it on column COL1 in table TAB1. This was successful:

SQL> conn user2/user2

Connected.

SQL> declare

  2  blah user1.tab1.col1%type;

  3  begin

  4  null;

  5  end;

  6  /

 

PL/SQL procedure successfully completed.

 

SQL> 

USER2 created a synonym called USER1. N.B. It is not a good idea for an object in one schema to have the same name as a schema elsewhere in the database.

SQL> create synonym user1 for user_tables

  2  /

 

Synonym created.

 

SQL> 

USER2 tried to declare a variable called BLAH as before. This time, Oracle probably thought that USER1 referred to the synonym created in the previous step rather than the username created at the start of the post. The declaration therefore failed:

SQL> declare

  2  blah user1.tab1.col1%type;

  3  begin

  4  null;

  5  end;

  6  /

blah user1.tab1.col1%type;

           *

ERROR at line 2:

ORA-06550: line 2, column 12:

PLS-00302: component 'TAB1' must be declared

ORA-06550: line 2, column 6:

PL/SQL: Item ignored

 

SQL>

The Curious Case of the Missing PL/SQL Procedure

I have to run files of SQL on databases for developers almost every day. These files often create or recreate PL/SQL packages. Any errors usually go back to the developers for correction as I am not a PL/SQL expert. Yesterday I ran some of these files then a tester asked me why a screen had stopped working. The screen was calling a PL/SQL package which I had just recreated for the developer. I will call it package_b. It had compilation errors and these were causing the screen to fail. The developer was on leave so I decided to have a look at the problem. I listed the compilation errors and could see that they happened when package_b tried to call a new procedure in another package. I will call the other package package_a and I will call the new procedure procedure_a2. I looked for compilation errors in package_a but there were none. I looked at the description for package_a but procedure_a2 was not mentioned. This was because the developer had provided a new body for package_a but no new header. The new header was found and package_a was recompiled. It was then possible to recompile package_b and the tester’s screen started to work again. I have reproduced this problem below on Oracle 11.2.0.2.7:

 

I created package_a and showed that its description contained procedure_a1:

 

SQL> create or replace package package_a is

  2  procedure procedure_a1;

  3  end package_a;

  4  /

 

Package created.

 

SQL> create or replace package body package_a is

  2  procedure procedure_a1 is

  3  begin

  4  null;

  5  end procedure_a1;

  6  end package_a;

  7  /

 

Package body created.

 

SQL> desc package_a

PROCEDURE PROCEDURE_A1

 

SQL>

 

I created package_b containing procedure_b1. This procedure called package_a.procedure_a1. Then I ran package_b.procedure_b1 successfully:

 

SQL> create or replace package package_b is

  2  procedure procedure_b1;

  3  end package_b;

  4  /

 

Package created.

 

SQL> create or replace package body package_b is

  2  procedure procedure_b1 is

  3  begin

  4  package_a.procedure_a1;

  5  end procedure_b1;

  6  end package_b;

  7  /

 

Package body created.

 

SQL> exec package_b.procedure_b1;

 

PL/SQL procedure successfully completed.

 

SQL>

 

I added procedure_a2 to package_a but did not change its header. Then I described it but could only see procedure_a1:

 

SQL> create or replace package body package_a is

  2  procedure procedure_a1 is

  3  begin

  4  null;

  5  end procedure_a1;

  6  procedure procedure_a2 is

  7  begin

  8  null;

  9  end procedure_a2;

10  end package_a;

11  /

 

Package body created.

 

SQL> desc package_a

PROCEDURE PROCEDURE_A1

 

SQL>

 

Then I changed package_b to call package_a.procedure_a2 but it failed to compile:

 

SQL> create or replace package body package_b is

  2  procedure procedure_b1 is

  3  begin

  4  package_a.procedure_a1;

  5  package_a.procedure_a2;

  6  end procedure_b1;

  7  end package_b;

  8  /

 

Warning: Package Body created with compilation errors.

 

SQL> show errors

Errors for PACKAGE BODY PACKAGE_B:

 

LINE/COL

-------------------------------------------------------

ERROR

-------------------------------------------------------

5/1

PL/SQL: Statement ignored

 

5/11

PLS-00302: component 'PROCEDURE_A2' must be declared

 

SQL>

 

I changed the header for package_a. Then its description included procedure_a2:

 

SQL> create or replace package package_a is

  2  procedure procedure_a1;

  3  procedure procedure_a2;

  4  end package_a;

  5  /

 

Package created.

 

SQL> desc package_a

PROCEDURE PROCEDURE_A1

PROCEDURE PROCEDURE_A2

 

SQL>

 

This allowed me to compile package_b and run package_b.procedure_b1 again:

 

SQL> alter package package_b compile

  2  /

 

Package altered.

 

SQL> exec package_b.procedure_b1;

 

PL/SQL procedure successfully completed.

 

SQL>

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>