Hadden Uff Hadden Uff - 8 months ago 45
SQL Question

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

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

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

create or replace TYPE my_obj
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 Source

Brackets :-)

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