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?

Chris B · Accepted Answer · 2009-10-19 18:29:02Z

11

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)

answered Oct 19, 2009 at 18:29

Chris B

7472 gold badges8 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Falco Over a year ago

The problem is simply with name resolution. Oracle always interprets the first part before the dot in the SELECT statement as a table-alias or a Synonym/package in the database. It does not also check all columns. So you have to fully qualify the name with table_alias.column.attribute

Michael · Accepted Answer · 2015-08-21 23:37:59Z

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.

answered Aug 21, 2015 at 23:37

Michael

211 bronze badge

Collectives™ on Stack Overflow

Using an object type in a select statement in Oracle

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related