Using an object type in a select statement in Oracle

Question

I have a function that returns an object that has 3 values. Is there a way to call that function from a select statement and have each value be a different column? I could break it into 3 functions, but the values are related so I wanted to keep it as one for performance reasons. (So oracle doesn't have to call 3 very similar complex functions for every row in the query.)

So for:

create type test_obj is object ( 
 a   NUMBER, 
 b   NUMBER,
 c   NUMBER);

    create or replace function test_func (
     pinput   NUMBER)
     return test_obj
    as
    begin
     return test_obj(0, 0, 0);
    end test_func;

I'd like to be able to call test_func from a select statement, but have a, b, and c be different columns, without calling the function multiple times. I thought maybe something like this, but it doesn't work:

select
    iv.col1,
    iv.col2,
    iv.func_data.a,
    iv.func_data.b,
    iv.func_data.c
from
    (select
     mt.col1,
     mt.col2,
     test_func(mt.input) as func_data
    from
     my_table mt) iv

Is there a way to do anything like this in Oracle 10g, or is there a better way to solve this problem?

Answer 1

The select statement in the question will work. It was failing because I didn't include an alias for the inline view.

For some reason this will work:

select
    iv.func_data.a,
    iv.func_data.b,
    iv.func_data.c
from
    (select
     test_func(mt.input) as func_data
    from
     my_table mt) iv

But this won't:

select
    func_data.a,
    func_data.b,
    func_data.c
from
    (select
     test_func(mt.input) as func_data
    from
     my_table mt)