Oracle SQL, is it possible to use a user-defined datatype property in a nested select?

Question

I have a PL/SQL function that returns a datatype with a property start_date:

create or replace FUNCTION RETURN_OBJ
RETURN my_obj
IS
  obj my_obj;
BEGIN
   obj := my_obj(SYSDATE);
   RETURN obj;
END;

create or replace TYPE my_obj
AS OBJECT (
start_date DATE
);

I can make use of the property in a simple SELECT statement e.g.

select RETURN_OBJ().start_date FROM DUAL

However when I try to use a virtual table (e.g. to avoid multiple function calls) I get an error:

select obj.start_date from (select RETURN_OBJ() AS obj FROM DUAL)
ORA-00904: "OBJ"."START_DATE": invalid identifier

Am I using the wrong syntax, or is this just not possible? (By the way I'm using Oracle 11 although the customer is still on 9)

Thanks very much.

Answer 1

Brackets :-)

select (obj).start_date from (select RETURN_OBJ() AS obj FROM DUAL)

Answer 2

You must use a table alias when you like to access functions from OBJECT Types. Try this one:

SELECT t.obj.start_date 
FROM (SELECT RETURN_OBJ() AS obj FROM DUAL) t;