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?
Cannot verify this, but you could try something like this:
select p.name, pp.date_from, pp.current
from person p, table(p.history) pp;
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;
