Example 6. Table function to "view" the contents of the relational table as a nested table.

create or replace function nested_training_logs_fn
  ( p_reln_training_logs  my_types.cur_t )
  return my_types.nested_training_logs_tab_t
  /* 
    The algorithm depends on assuming that it receives 
    rows ordered by first_name, week, then run, and that 
    all the rows for a particular runner go consecutively 
    to the same slave. These declarations ensure this and 
    remove the need for an ORDER BY clause in the SELECT 
    that's used to invoke this function.
  */
  order p_reln_training_logs by ( first_name, week, run )
  parallel_enable ( 
    partition p_reln_training_logs by range ( first_name ) )
  pipelined
is
  g_in_row              my_types.reln_training_log_row_t;
  g_out_row             my_types.nested_training_log_row_t;
  g_weeks_running       weeks_running_t;
  g_training_log        training_log_t;
  g_first_time          boolean := true;
  g_got_a_row           boolean;
  g_new_week            boolean;
  g_new_runner          boolean;
  g_current_first_name  reln_training_logs.first_name%type;
  g_prev_first_name     reln_training_logs.first_name%type;
  g_current_week        reln_training_logs.week%type;
  g_prev_week           reln_training_logs.week%type;

  procedure get_next_row is begin
    fetch p_reln_training_logs into g_in_row;
    g_got_a_row := not p_reln_training_logs%notfound;
    if g_got_a_row
    then
      case g_first_time
        when true then
          g_first_time := false;
          g_new_runner := false;
          g_new_week   := false;
        else
          g_new_runner := 
            g_prev_first_name <> g_in_row.first_name;
          g_new_week := case g_new_runner
                          when true then true
                          else           
                            g_prev_week <> g_in_row.week
                        end;
      end case;
      g_prev_first_name := g_in_row.first_name;
      g_prev_week       := g_in_row.week;
    end if;
    return;
  end get_next_row;

  function got_next_runner return boolean is begin
    g_current_first_name := g_in_row.first_name;
    g_new_runner := false;
    return g_got_a_row;
  end got_next_runner;

  function got_next_week return boolean is begin
    g_current_week := g_in_row.week;
    g_new_week := false;
    return ( not g_new_runner ) and g_got_a_row;
  end got_next_week;

  function got_next_run return boolean is begin
    return ( not g_new_week ) and g_got_a_row;
  end got_next_run;

  procedure new_training_log is begin
    g_training_log := null;
  end new_training_log;

  procedure new_weeks_running is begin
    g_weeks_running := null;
  end new_weeks_running;

  procedure store_this_run is begin
    if g_weeks_running is null
    then
      g_weeks_running := 
        weeks_running_t ( run_t ( 0, 0 ) );
    else
      g_weeks_running.extend;
    end if;
    g_weeks_running ( g_in_row.run ):=
      run_t ( g_in_row.distance, g_in_row.pace );
  end store_this_run;

  procedure store_this_weeks_running is begin
    if g_training_log is null
    then
      g_training_log := 
        training_log_t ( 
        weeks_running_t ( run_t ( 0, 0 ) ) );
    else
      g_training_log.extend;
    end if;
    g_training_log ( g_current_week ):= g_weeks_running;
  end store_this_weeks_running;

  procedure output_this_runner is begin
    g_out_row.first_name   := g_current_first_name;
    g_out_row.training_log := g_training_log;
  end output_this_runner;

begin
  get_next_row();
  while got_next_runner()
  loop
    new_training_log;
    while got_next_week()
    loop
      new_weeks_running;
      while got_next_run()
      loop
        store_this_run;
        get_next_row();
      end loop;
      store_this_weeks_running;
    end loop;
    output_this_runner; pipe row ( g_out_row ); 
  end loop;
  close p_reln_training_logs;
  return;
end nested_training_logs_fn;

In Summary

Example 7. An "end-to-end" test.

truncate table nested_training_logs;
execute populate_nested_training_logs

truncate table reln_training_logs;
insert into reln_training_logs
  (
    select *
      from table
        (
          reln_training_logs_fn 
           (
             cursor
               (
                 select first_name, training_log 
                   from nested_training_logs
               )
           )
        )
  );

truncate table nested_training_logs_2;
insert into nested_training_logs_2
  (
    select *
      from table
        (
          nested_training_logs_fn
            (
              cursor ( select * from  reln_training_logs )
            )
        )
  );

truncate table reln_training_logs_2;
insert into reln_training_logs_2
  (
    select *
      from table
        (
          reln_training_logs_fn 
           (
             cursor
               (
                 select first_name, training_log 
                   from nested_training_logs_2
               )
           )
        )
  );

select *
  from reln_training_logs_2
minus
select *
  from reln_training_logs;

select *
  from reln_training_logs
minus
select *
  from reln_training_logs_2;