How do I get the attribute of my object type in SQL?

Question

I create an object type and a table with a column of that type:

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;
/

But when I try to get the names of my good, I get the ORA-904 error:

SELECT name FROM food_table;
ORA-00904: "NAME": invalid identifier

Same if I use the name of the object type column:

SELECT my_food.name FROM food_table;
ORA-00904: "NAME": invalid identifier

What am I doing wrong?

score 2 · Answer 1 · answered Apr 25 '18 at 13:29

Simple: don't forget to include a table alias. It is required in this situation:

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

If you are working with object types in Oracle, make sure to review the Object-Relational Developer's Guide.

How do I get the attribute of my object type in SQL?

1 Answers1