2

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?

  • What are you calling the SQL from? C/C++/Java/a GUI? – Philᵀᴹ Jun 8 '16 at 7:47
  • @Phil Oracle SQL Developer – Zolbayar Jun 8 '16 at 7:56
1

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.

0

If you don't want call a function several times, you may wrap it into subselect and then treat its result as my_address_detail_t several times

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

BTW, a believe even you write:

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;

Oracle does not call the function four times. Additionally you may make the function DETEMINISTIC.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.