Name Resolution Finds Local Schema Table Before Schema and Results in PLS-302 (Doc ID 1174526.1)

Last updated on JULY 05, 2017

Applies to:

PL/SQL - Version: 10.2.0.4 to 11.2.0.1 - Release: 10.2 to 11.2
Information in this document applies to any platform.

Symptoms

The following code throws a PLS-302: component 'TAB1' must be declared error because the local schema (USER2) has a table name (USER1) which has the same name as another schema (USER1).

CONNECT user1/user1

CREATE TABLE tab1(ID NUMBER(5));

GRANT SELECT ON tab1 TO user2;

CONNECT user2/user2

DECLARE
  TYPE type_tab1 IS TABLE OF user1.tab1.id%TYPE;
BEGIN
  null;
END;
/


This works successfully.

When a table is created in the local USER2 schema called USER1, the PL/SQL block on fails with PLS-302: component 'TAB1' must be declared

CONNECT user2/user2

CREATE TABLE user1(ID NUMBER(5));

DECLARE
  TYPE type_tab1 IS TABLE OF user1.tab1.id%TYPE;
BEGIN
  null;
END;
/

ERROR at line 2:
ORA-06550: line 2, column 36:
PLS-00302: component 'TAB1' must be declared
ORA-06550: line 2, column 3:
PL/SQL: Item ignored

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms