How to SELECT from object type column in Oracle 11g?

Question

Hy guys, I have following two Oracle objects:

CREATE OR REPLACE TYPE car AS OBJECT( 
name VARCHAR( 80 ) 
) NOT FINAL;

And also, there is another object:

CREATE OR REPLACE TYPE truck UNDER car ( 
doors NUMBER,
seats NUMBER 
);

There is also following table:

CREATE TABLE vehicles (
id NUMBER NOT NULL,
vehicle car,
PRIMARY KEY (id)
);

Here is some data:

INSERT INTO vehicles ( id, vehicle ) VALUES ( 1, truck( 'ford', 4, 4 ) );
INSERT INTO vehicles ( id, vehicle ) VALUES ( 2, truck( 'toyota', 4, 5 ) );

Finally, my question is: How to select only number of doors and number of seats from vehicle table column?

I tried following but it does not work:

SELECT v.vehicle.doors AS doors AS seats FROM vehicles v;

I got following error:

ORA-00904: "V"."VEHICLE"."DOORS": invalid identifier

Only parameter that i can get without any error is one from car object.

FYI, I am using Oracle 11g on CentOS 6.2

Cheers, Bojan

Answer 1

You need to use the TREAT function to get the database engine to treat VEHICLE as a TRUCK, as in:

SELECT ID, TREAT(vehicle AS TRUCK).DOORS FROM VEHICLES

Share and enjoy.