PLS-302 When Table Name And Schema Name Are Identical (Doc ID 567376.1)

Last updated on JULY 05, 2017

Applies to:

PL/SQL - Version 9.2.0.1 and later
Information in this document applies to any platform.

Symptoms

Attempting to run a SQL statement such as

SELECT scott.scottnr_seq.NEXTVAL into var from dual
in a pl/sql block, where the table and the schema have the same name, raises the error
PLS-00302: component 'SCOTTNR_SEQ' must be declared

The issue can be reproduced at will with the following steps:

CREATE TABLE scott.scott
(
scottnr NUMBER (2) NOT NULL,
scott_desc VARCHAR2 (25) NOT NULL,
CONSTRAINT scott_scottnr_pk
PRIMARY KEY (scottnr)
);


CREATE SEQUENCE scott.scottnr_seq
INCREMENT BY 1
START WITH 1
MINVALUE 1
-- MAXVALUE 99999
NOCYCLE
NOCACHE
/

declare
seqno number;
BEGIN
SELECT scott.scottnr_seq.NEXTVAL
INTO seqno
FROM DUAL;
END;
/



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