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>
