Hadden Uff Hadden Uff - 4 days ago 6
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
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

Brackets :-)

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