Oracle improbable errors, or should I say bugs?

SQL> create trigger DUSER.H_SASTAB_BI BEFORE INSERT ON DUSER.H_SASTAB FOR EACH ROW BEGIN   SELECT DUSER.H_SEQ.nextval INTO :new.h_node_id FROM dual; END; /   Warning: Trigger created with compilation errors.   SQL> show err   LINE/COL ERROR -------- ------------------------------------------------------ 3/15     PLS-00302: component 'H_SEQ' must be declared

 

PLS-00302: component "string" must be declared

Cause: In a reference to a component (for example, in the name "A.B", "B" is a component of "A"), the component has not been declared. The component might be misspelled, its declaration might be faulty, or the declaration might be placed incorrectly in the block structure.

Action: Check the spelling and declaration of the component. Also confirm that the declaration is placed correctly in the block structure.

 

Straightforward: H_SEQ does not exist, right?

 

SQL> select owner, object_name, object_type        from dba_objects       where object_name = 'H_SEQ';   OWNER   OBJECT_NAME      OBJECT_TYPE ------- ---------------- -------------------------------------DUSER   H_SEQ            SEQUENCE

 

As DUSER owns the sequence let us try without qualifying the sequence:

 

SQL> create trigger DUSER.H_SASTAB_BI BEFORE INSERT ON DUSER.H_SASTAB FOR EACH ROW BEGIN   SELECT H_SEQ.nextval INTO :new.h_node_id FROM dual; END; /   Trigger created.

 

Wow! My best guess? There is a table with the same name as the owner DUSER. Therefore

when the sequence was prefixed by the schema name, actually Oracle was checking for

a column named H_SEQ in the DUSER table. As such column does not exist we received the

error PLS-00302: component 'H_SEQ' must be declared.

 

SQL> select owner, object_name, object_type        from dba_objects       where object_name = 'DUSER';   OWNER   OBJECT_NAME      OBJECT_TYPE ------- ---------------- -------------------------------------DUSER   DUSER            TABLE

 

Why should the application have a table with the same name as the application owner?

Well, to tell you the truth I really don't want to know :o)

 

Anyway, it does not make sense, Oracle should be smarter than that – it should realize this is

a sequence. So, a new guess: there is a bug. Indeed, BUG: 1218383.

Checking it, it seems something that was fixed in the past, but is back. BTW, the database with

this issue was migrated from 10.2.0.4 to 11.2.0.3.

 

A Sequence Prefixed by Schema Name Fails with PLS-00302: component 'SEQUENCE_NAME'

must be declared [ID 1371201.1].