create or replace type list_of_names_t is 
   table of varchar2(100); 

declare 
   big_brained_species list_of_names_t := list_of_names_t( 
      'Human', 
      'Bonobo', 
      'Gorilla', 
      'Octopus', 
      'Raven', 
      'Whale', 
      'Dolphin' 
   ); 
begin 
   for rec in ( 
      select column_value species_name 
        from table ( big_brained_species ) 
       order by species_name 
   ) loop 
      dbms_output.put_line(rec.species_name); 
   end loop; 
end; 

declare 
   big_brained_species list_of_names_t := list_of_names_t( 
      'Human', 
      'Bonobo', 
      'Gorilla', 
      'Octopus', 
      'Raven', 
      'Whale', 
      'Dolphin' 
   ); 
begin 
   for rec in ( 
      select column_value species_name 
        from table (big_brained_species) 
       order by species_name 
   ) loop 
      dbms_output.put_line(rec.species_name); 
   end loop; 
end; 

declare 
   big_brained_species list_of_names_t := list_of_names_t( 
      'Human', 
      'Bonobo', 
      'Gorilla', 
      'Octopus', 
      'Raven', 
      'Whale', 
      'Dolphin' 
   ); 
begin 
   for rec in ( 
      select column_value species_name 
        from table (big_brained_species) 
       where column_value like '%o%' 
   ) loop 
      dbms_output.put_line(rec.species_name); 
   end loop; 
end; 

declare  
   big_brained_species list_of_names_t := list_of_names_t(  
      'Human',  
      'Bonobo',  
      'Gorilla',  
      'Octopus',  
      'Raven',  
      'Whale',  
      'Dolphin'  
   );  
begin  
   for rec in (  
      select column_value || '-' || e.last_name name  
        from table (big_brained_species) bbs, hr.employees e  
       where substr (bbs.column_value, 1, 1) = substr (e.last_name, 1, 1)  
   ) loop  
      dbms_output.put_line(rec.name);  
   end loop;  
end; 

create or replace package brainiacs is 
   big_brained_species list_of_names_t := list_of_names_t( 
      'Human', 
      'Bonobo', 
      'Gorilla', 
      'Octopus', 
      'Raven', 
      'Whale', 
      'Dolphin' 
   ); 
end; 

select column_value species_name 
  from table ( brainiacs.big_brained_species ) 
 order by species_name 

begin 
   for rec in ( 
      select column_value species_name 
        from table ( brainiacs.big_brained_species ) 
       order by species_name 
   ) loop 
      dbms_output.put_line(rec.species_name); 
   end loop; 
end; 

create or replace function big_brained_species return list_of_names_t is 
begin 
   return list_of_names_t( 
      'Human', 
      'Bonobo', 
      'Gorilla', 
      'Octopus', 
      'Raven', 
      'Whale', 
      'Dolphin' 
   ); 
end; 

select column_value species_name 
  from table ( big_brained_species ) 
 order by species_name 

select column_value species_name 
  from big_brained_species 
 order by species_name 

select column_value species_name 
  from big_brained_species () 
 order by species_name 

create or replace package brainiacs  
is 
   type species_rt is record (name varchar2(100), average_size varchar2(100)); 
   type species_aat is table of species_rt index by pls_integer; 
    
   big_brained_species species_aat := species_aat( 
      -10000 => species_rt ('Human', 'Medium'), 
      1000 => species_rt ('Bonobo', 'Medium'), 
      2000 => species_rt ('Gorilla', 'Large'), 
      3000 => species_rt ('Octopus', 'Small'), 
      4000 => species_rt ('Raven', 'Very Small'), 
      5000 => species_rt ('Whale', 'Very Large'), 
      6000 => species_rt ('Dolphin', 'Medium') 
   ); 
end; 

begin 
   for rec in ( 
      select name, average_size 
        from table ( brainiacs.big_brained_species ) 
       order by name desc 
   ) loop 
      dbms_output.put_line(rec.average_size || ' ' || rec.name); 
   end loop; 
end; 

create or replace package brainiacs  
is 
   type species_rt is record (name varchar2(100), average_size varchar2(100)); 
   type species_aat is table of species_rt index by pls_integer; 
    
   function big_brained_species (p_like in varchar2 default '%') return species_aat; 
end; 

create or replace package body brainiacs  
is 
   function big_brained_species ( 
      p_like in varchar2 default '%' 
   ) return species_aat is 
   begin 
      return species_aat( 
         -10000   => species_rt('Human', 'Medium'), 
         1000     => species_rt('Bonobo', 'Medium'), 
         2000     => species_rt('Gorilla', 'Large'), 
         3000     => species_rt('Octopus', 'Small'), 
         4000     => species_rt('Raven', 'Very Small'), 
         5000     => species_rt('Whale', 'Very Large'), 
         6000     => species_rt('Dolphin', 'Medium') 
      ); 
   end; 
end;    

begin  
   for rec in (  
      select name, average_size  
        from table ( brainiacs.big_brained_species() )  
       order by name desc  
   ) loop  
      dbms_output.put_line(rec.average_size || ' ' || rec.name);  
   end loop;  
end;     

declare 
   l_species brainiacs.species_aat := brainiacs.big_brained_species(); 
begin 
   for rec in ( 
      select name, average_size 
        from table ( l_species ) 
       order by name desc 
   ) loop 
      dbms_output.put_line(rec.average_size || ' ' || rec.name); 
   end loop; 
end;