create table my_diary (
   my_diary_id   number generated always as identity,
   what_happened varchar2(100),
   details       varchar2(1000),
   location      varchar2(100),
   happened_on   date,
   very_exciting varchar2(1),
   never_again   varchar2(1)
)

create table favorite_activity (
   favorite_activity_id number generated always as identity,
   title       varchar2(100),
   description varchar2(1000)
)

create table avoid_at_all_costs (
   avoid_at_all_costs_id number generated always as identity,
   location varchar2(100),
   reason   varchar2(1000)
)

create table itinerary (
   itinerary_id number generated always as identity,
   location varchar2(100),
   variation_no integer,
   description varchar2(1000)
)

insert into favorite_activity (
   title,
   description
) values (
   'Be Outside',
   'Outside is always better than inside.'
)

insert into favorite_activity (
   title,
   description
)
   select what_happened,
          details
     from my_diary
    where very_exciting = 'Y'
      and extract(year from happened_on) = 2021

   insert all 
   into my_diary (
      what_happened,
      details,
      location,
      happened_on,
      very_exciting,
      never_again
   ) values (
      'Had fun',
      'Sorry, confidential',
      'Carolina North Forest',
      sysdate,
      'Y',
      'N'
   ) 
   into my_diary (
      what_happened,
      details,
      location,
      happened_on,
      very_exciting,
      never_again
   ) values (
      'Swam in the ocean',
      'It was salty',
      'Ocracoke Island',
      sysdate - 30,
      'Y',
      'N'
   ) select *
       from dual

insert
   all when very_exciting = 'Y' then
        into favorite_activity (
         title,
         description
      )
      values (
         what_happened,
         details
      )
      when never_again = 'Y' then
           into avoid_at_all_costs (
            location,
            reason
         )
         values (
            location,
            details
         )
select what_happened,
       location,
       details,
       never_again,
       very_exciting
  from my_diary
 where extract(year from happened_on) = 2021

create or replace trigger itinerary_bi before
   insert on itinerary
   for each row
begin
   select nvl(max(variation_no),0) + 1
     into :new.variation_no
     from itinerary
    where location = :new.location;
end;

insert into itinerary (
   location,
   description
)
select location, details
  from my_diary
  where rownum < 2

insert into itinerary (
   location,
   description
) values (
   'Yellowstone',
   'Lovely place'
)

begin
   insert into itinerary (
      location,
      description
   ) values (
      'Chicago',
      'Home of my son'
   );
   insert into itinerary (
      location,
      description
   ) values (
      'South Bend',
      'Home of my grandkids'
   );
end;

create or replace procedure add_itinerary (
   p_location     in varchar2,
   p_description  in varchar2,
   p_itinerary_id out number
) authid definer
is
begin
   insert into itinerary (
      location,
      description
   ) values (
      p_location,
      p_description
   ) returning itinerary_id into p_itinerary_id;
end;

drop trigger itinerary_bi

create or replace type itinerary_ot is object (
   location varchar2(100),
   description varchar2(1000))

create or replace type itineraries_nt is table of itinerary_ot

create or replace type itinerary_ids_nt is table of number

create or replace procedure add_itineraries (
   p_itineraries   in itineraries_nt,
   p_itinerary_ids out itinerary_ids_nt
) authid definer
is
begin
   forall indx in p_itineraries.first..p_itineraries.last
      insert into itinerary (
         location,
         description
      ) values (
         p_itineraries(indx).location,
         p_itineraries(indx).description
      ) returning itinerary_id
      bulk collect into p_itinerary_ids;
end;

declare
   l_itineraries   itineraries_nt := itineraries_nt(
                                                 itinerary_ot(
                                                             'Lake',
                                                             'Watery'
                                                 ),
                                                 itinerary_ot(
                                                             'Ocean',
                                                             'Salty'
                                                 )
                                   );
   l_itinerary_ids itinerary_ids_nt := itinerary_ids_nt();
begin
   add_itineraries(
                  p_itineraries => l_itineraries,
                  p_itinerary_ids => l_itinerary_ids
   );
   dbms_output.put_line(l_itinerary_ids.count);
end;

declare
   l_itinerary_ids itinerary_ids_nt := itinerary_ids_nt();
begin
   insert into itinerary (
      location,
      description
   )
      select 'Lake',
             'Watery'
        from dual
      union all
      select 'Ocean',
             'Salty'
        from dual
   returning bulk collect into l_itinerary_ids;
end;