How to SELECT from object type column in Oracle 11g?
object, oracle, oracle11g, select
Solution
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.
Problem
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