Function returning more than one value

From: Björn Wächter <bwc_at_p3-solutionsKILL_SPAM.de>
Date: Wed, 13 Sep 2006 18:54:37 +0200
Message-ID: <4mqredF7ca0qU1@news.dfncis.de>

I'm working one a view at the moment. There are three columns in the view that get three value out of a stored function. I used three functions in the old version.

Example:

CREATE OR REPLACE VIEW VI_TEST
(VAL_1, VAL_2, VAL_3)

AS
SELECT

sf_get_value1(TASK_ID) VAL1,
sf_get_value2(TASK_ID) VAL2,
sf_get_value3(TASK_ID) VAL3

FROM ADM_TASK The three values are very much correlated. So it would be much faster if I could calculate the three values in one function. I tried to solve the problem by writing a function that returns an object with three members:

Example:

CREATE OR REPLACE
TYPE OBJ_VALUES AS OBJECT (

value1        VARCHAR2(255),
value2        NUMBER,
value3        NUMBER

)

CREATE OR REPLACE FUNCTION sf_get_values RETURN OBJ_VALUES
IS

   v_return OBJ_VALUES;
BEGIN     v_return := OBJ_VALUES('asdf',3,5);

    RETURN v_return;         

END sf_get_values;

CREATE OR REPLACE VIEW VI_TEST
(VAL_1, VAL_2, VAL_3)

AS
SELECT

sf_get_values(TASK_ID).value1 VAL1,
sf_get_values(TASK_ID).value2 VAL2,
sf_get_values(TASK_ID).value3 VAL3

FROM ADM_TASK But Oracle is not that clever that it executes the function only once for one row. It executes the function three times for each row. I tried to solve the problem by calling the function only once and split into the values later:

SELECT abc.value1, abc.value2, abc.value3 FROM
(

SELECT
sf_get_values(TASK_ID) abc
FROM ADM_TASK
)

But this is not working. Any ideas how I can solve this problem? I think I'm using the wrong syntax to access the members of the object.

Thanks Björn Received on Wed Sep 13 2006 - 11:54:37 CDT