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)

Answer 2

The table alias places the result into a named result set context, which enables the result to work as an object instance. Without the alias, it fails because the casting doesn't handle object type instances without their own explicit reference, which is effectively what the table alias is.