Title
ORA-00904 when trying to select a type attribute from a table

Date
2008-06-05

Summary
When selecting a type attribute from a table, the table name must be aliased and referenced in the SELECT statement, otherwise an ORA-00904 is returned.

Details
1. Create objects to demonstrate the example. A type called MYTYPE is created and a table called MYTABLE is created with two columns; one of object MYTYPE. 
CREATE TYPE mytype as OBJECT (
  MSG_ID    VARCHAR2(10),
  PAYLOAD   VARCHAR2(10)
);

CREATE TABLE mytable (
  firstname VARCHAR2(10),
  message   mytype
);

2. Insert a record into the table. The output appears as shown: 
SQL> INSERT INTO mytable VALUES ('Ahmed', mytype('1','hello'));

1 row created.

3. If you SELECT the entire object from the table, the data is returned as follows: 
SQL> SELECT message FROM mytable;

MESSAGE(MSG_ID, PAYLOAD)
---------------------------------
MYTYPE('1', 'hello')

4. However, if you only want to SELECT a specific attribute, you will get an ORA-00904 as shown: 
SQL> SELECT message.payload FROM mytable;
SELECT message.payload FROM mytable
       *
ERROR at line 1:
ORA-00904: "MESSAGE"."PAYLOAD": invalid identifier

5. SOLUTION: 
SQL> SELECT a.message.payload FROM mytable a;

MESSAGE.PAYLOAD
---------------
hello

Applicable Versions
Oracle Database 9i
Oracle Database 10g
Ahmed Aboulnaga
.com .com