%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>