How to select all attributes of object type I am running select query with a function in it. That function returns an object type with 4 attributes and I'd like to select all those attributes. 

When I try it int this way. It returns just the type object as a whole.

SELECT SERVICE_TYPE, FN_GET_ADDRESS_DETAIL(USER_ID) FROM REG_ADDRESS; 


It returns: 

SERVICE_TYPE | FN_GET_ADDRESS_DETAIL(USER_ID)
---------------------------------------------
0            | USERNAME.ADDRESS_DETAIL_T
0            | USERNAME.ADDRESS_DETAIL_T
0            | USERNAME.ADDRESS_DETAIL_T


My object type is like this: 

CREATE OR REPLACE TYPE ADDRESS_DETAIL_T AS OBJECT 
(
  apt_number        NUMBER,
  district_name     NVARCHAR2(40),
  zipcode           NUMBER,
  whatever          NVARCHAR2(40)
);


And it works if I specify attribute names after the function call. But in order to select all the attributes, I have to call the function 4 times! O_O

SELECT SERVICE_TYPE, FN_GET_ADDRESS_DETAIL(USER_ID).apt_number,
 FN_GET_ADDRESS_DETAIL(USER_ID).district_name,
 FN_GET_ADDRESS_DETAIL(USER_ID).zipcode,
 FN_GET_ADDRESS_DETAIL(USER_ID).whatever, FROM REG_ADDRESS;


Is there any other options?
I think this might be the kind of thing you need.

WITH get_detail
  AS (SELECT get_addr(col1) AS obj_type from my_test)
SELECT x.obj_type.district_name FROM get_detail x
/


This is what I created to test it out.

CREATE OR REPLACE TYPE my_ADDRESS_DETAIL_T AS OBJECT 
(
 apt_number        NUMBER,
 district_name     NVARCHAR2(40),
 zipcode           NUMBER
)
/
create table my_test
(col1 number,
 addr_detl my_address_detail_t)
/
INSERT INTO my_test
values(1, my_address_detail_t(1,'HOME',12345))
/
commit
/


And the function;

CREATE OR REPLACE FUNCTION my_get_addr(addr_id IN NUMBER)
RETURN my_address_detail_t IS
addr_t my_address_detail_t; 
BEGIN
SELECT addr_detl
  INTO addr_t
  FROM my_test
 WHERE col1 = addr_id;

RETURN addr_t;
END; 


You will obviously need to define a join somewhere for get_detail to have the correct col1 to retrieve data for. Im not really sure of the full requirement so haven't tried that bit but this should give you the basics.