How Do I Get the Attribute of My Object Type in SQL?

This question found its way into my inbox yesterday:

I have a table with an object type column. I want to way to get the value of an attribute of that object type in my query. But Oracle keeps telling me "ORA-00904: invalid identifier". What am I doing wrong?

Almost certainly what you are doing wrong is forgetting to use a table alias. Yeah, it's that simple.

Don't forget the table alias.

Let's take a look.

I create an object type, use that object type as a column in a table, and insert a couple of rows:

CREATE TYPE food_t AS OBJECT (
   NAME         VARCHAR2 (100)
 , food_group   VARCHAR2 (100)
 , grown_in     VARCHAR2 (100)
)
/

CREATE TABLE food_table (id number primary key, my_food food_t)
/

BEGIN
   INSERT INTO food_table
        VALUES (1, NEW food_t ('Mutter Paneer', 'Curry', 'India'));

   INSERT INTO food_table
        VALUES (2, NEW food_t ('Cantaloupe', 'Fruit', 'Backyard'));

   COMMIT;
END;
/

OK, let's query some data. The following output is what you see in SQLcl:

SQL> SELECT * FROM food_table;

        ID
   ID MY_FOOD(NAME, FOOD_GROUP, GROWN_IN)                                             
----- ------------------------------------------
    1 FOOD_T('Mutter Paneer', 'Curry', 'India')                                       
    2 FOOD_T('Cantaloupe', 'Fruit', 'Backyard') 

In SQL Developer, I see:

While the output format varies, the basic idea is the same: I have asked for all columns and one of those columns is an object type, so I see the instance of that type.

What I want, though, is to select the individual attributes, such as the same. OK, here goes:

SQL> SELECT name FROM food_table;

Error starting at line : 1 in command -
SELECT name FROM food_table
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00904: "NAME": invalid identifier

What? No NAME column? Oh, that's right. It's not a column — it's an attribute of a column. OK, let's try that again:

SQL> SELECT my_food.name FROM food_table;
Error starting at line : 1 in command -
SELECT my_food.name FROM food_table
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00904: "MY_FOOD"."NAME": invalid identifier

Still no good. But, but. OK, so here's the deal: you must provide an alias to the table, prefix the object type column name with the alias, and then you are good to go. You can even reference the attribute in the WHERE clause:

SQL> SELECT ft.my_food.name FROM food_table ft;

MY_FOOD.NAME                                                                    
-----------------------------------------------
Mutter Paneer                                                                   
Cantaloupe                                                                      

SQL> SELECT ft.my_food.name FROM food_table ft
  2   WHERE ft.my_food.name LIKE 'C%';

MY_FOOD.NAME                                                                    
-----------------------------------------------
Cantaloupe         

For more information on working with object types in SQL and PL/SQL, check out the Object-Relational Developer's Guide.