-- once upon a time, all employees in Department 10 see their salary increased by 15%. The update emp set sal = sal * 1.15 where deptno = 10 / commit / -- next month, all CLERKs are made happy with a $200 pay raise. update emp set sal = sal + 200 where job = 'CLERK' / commit / -- Two months later, MANAGERs are cut back - salary decrease of 10%. update emp set sal = sal * 0.9 where job = 'MANAGER' / commit / -- And finally, in this brief history of time, everyone hired in 1981 is awarded 150 extra in their regular paycheck. update emp set sal = sal + 150 where extract( year from hiredate) = 1981 / commit / with points_in_time as ( select distinct versions_starttime starttime from emp versions between timestamp minvalue and maxvalue ) select * from points_in_time create type timestamp_tbl as table of timestamp create type num_and_time as object (number_value number, point_in_time timestamp) create type num_and_time_tbl as table of num_and_time with points_in_time as ( select distinct nvl(versions_starttime, versions_endtime) point_in_time from emp versions between timestamp minvalue and maxvalue ) select cast (multiset( select * from points_in_time) as timestamp_tbl) from dual create or replace function average_salary ( p_points_in_time timestamp_tbl ) return num_and_time_tbl is l_avg_sals num_and_time_tbl:= num_and_time_tbl(); l_index number; begin l_index := p_points_in_time.first; loop exit when l_index is null; l_avg_sals.extend; select num_and_time( avg(sal), p_points_in_time(l_index)) into l_avg_sals( l_avg_sals.last) from emp as of timestamp p_points_in_time(l_index) ; l_index:= p_points_in_time.next(l_index); end loop; return l_avg_sals; end average_salary; with points_in_time as ( select distinct nvl(versions_starttime, versions_endtime) point_in_time from emp versions between timestamp minvalue and maxvalue ) select * from table( average_salary (cast (multiset( select * from points_in_time) as timestamp_tbl)) ) -- if you want to see the current status as well, include systimestamp with points_in_time as ( select distinct nvl(versions_starttime, versions_endtime) point_in_time from emp versions between timestamp minvalue and maxvalue union select systimestamp from dual ) select round(number_value) avg_sal, point_in_time from table( average_salary (cast (multiset( select * from points_in_time) as timestamp_tbl))) order by point_in_time