Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

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

DZone's Guide to

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

If you keep getting the error ORA-00904: invalid identifier, you're more than likely forgetting to use a table alias.

· Database Zone ·
Free Resource

Slow SQL Server? These SentryOne resources share tips and tricks for not only troubleshooting SQL Server performance issues, but also preventing them before they hit your production environment.

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.

Database monitoring tools letting you down? See how SentryOne empowers Enterprises to go faster.

Topics:
database ,sql ,tutorial ,oracle

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}