Using an object type in a select statement in Oracle

oracle, plsql, sql

Solution

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)

Problem

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?

Original source