Oracle sql select data from table where attribute is nested table

Question

I have a objects:

create type t_history_rec is object
(
  date_from date,
  current float
);

create type t_history is table of t_history_rec;

and table defined:

create table person
(
  id integer primary key,
  name varchar2(30),
  history t_history

);

and I want to get select name, history.date_from, history.current like this:

name1 date1 current1
name1 date2 current2
name2 date3 current3
...

How to do this?

Answer 1

You have some errors. current is reserved

create or replace type t_history_rec is object
(
  date_from date,
  curr float
);
/
create type t_history is table of t_history_rec;
/

Table definition needs store as

create table person
(
  id integer primary key,
  name varchar2(30),
  history t_history
) NESTED TABLE history STORE AS col1_tab;

insert into person (id, name, history) values (1, 'aa', t_history(t_history_rec(sysdate, 1)));
insert into person (id, name, history) values (2, 'aa', t_history(t_history_rec(sysdate, 1), t_history_rec(sysdate, 1)));

Then select is:

SELECT t1.name, t2.date_from, t2.curr FROM person t1, TABLE(t1.history) t2;