CODEWITHSUNDEEP
oracle-databaseplsql
Belun
Belun

Reputation: 4169

How to access attributes of inner object during a select from table()?

I have tried using table functions, to better abstract my code, and put then together with objects. But my objects also have duplicate code (duplicate attributes for now) so I would like to use composition to reuse code.

But I do not know how to extract data from inner objects.

Here is an example :

create or replace type inner_obj force as object (
  some_attribute VARCHAR2(1 BYTE)
);

create or replace type outter_obj force as object (
  inner_value inner_obj,
  another_attribute VARCHAR2(1 BYTE)
);

create or replace type outter_tbl is table of outter_obj;

create or replace FUNCTION get_outters
  RETURN outter_tbl PIPELINED IS   

  inner_ inner_obj;
  outter_ outter_obj;
BEGIN  

  inner_ := inner_obj('A');    
  outter_ := outter_obj(inner_, 'X');
  pipe row (outter_);        
  return;
END get_outters;

select * from table(get_outters());
"INNER_VALUE"   "ANOTHER_ATTRIBUTE"
ZCDB.INNER_OBJ('A') "X"


select another_attribute from table(get_outters());
"ANOTHER_ATTRIBUTE"
"X"

select inner_.some_attribute from table(get_outters());
**NOPE**

How do I get some_attribute out of inner_ ?

Upvotes: 1

Views: 219

Answers (1)

peter.hrasko.sk
peter.hrasko.sk

Reputation: 4141

All you need is a table alias, because without it Oracle is not able to tell a difference between a table column and a table column's attribute...

select XY.inner_value.some_attribute
from table(get_outters()) XY;

Upvotes: 1

Related Questions