Re: Function returning more than one value

Björn Wächter wrote:
> 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

Use a stored procedure with three OUT parameters.

-- 
Daniel Morgan
Puget Sound Oracle Users Group