Exploring table functions for Feuertip #22

View All Scripts Login to Run Script

Script Name Exploring table functions for Feuertip #22
Description In which we use a table about animal species to explore the flexibility of the from clause of a query - not just tables and views, but also collections and functions!
Area PL/SQL General / PL/SQL Procedures, Functions, Packages
Contributor Steven Feuerstein
Created Tuesday August 17, 2021

Statement 1

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

Type created.

Statement 2

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;

Statement processed.
Bonobo
Dolphin
Gorilla
Human
Octopus
Raven
Whale

Statement 3

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;

Statement processed.
Bonobo
Dolphin
Gorilla
Human
Octopus
Raven
Whale

Statement 4

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;

Statement processed.
Bonobo
Gorilla
Octopus
Dolphin

Statement 5

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;

Statement processed.
Bonobo-Baer
Bonobo-Baida
Bonobo-Banda
Bonobo-Bates
Bonobo-Bell
Bonobo-Bernstein
Bonobo-Bissot
Bonobo-Bloom
Bonobo-Bull
Dolphin-Davies
Dolphin-De Haan
Dolphin-Dellinger
Dolphin-Dilly
Dolphin-Doran
Gorilla-Gates
Gorilla-Gee
Gorilla-Geoni
Gorilla-Gietz
Gorilla-Grant
Gorilla-Grant
Gorilla-Greenberg
Gorilla-Greene
Human-Hall
Human-Hartstein
Human-Higgins
Human-Himuro
Human-Hunold
Human-Hutton
Octopus-OConnell
Octopus-Olsen
Octopus-Olson
Octopus-Ozer
Raven-Rajs
Raven-Raphaely
Raven-Rogers
Raven-Russell
Whale-Walsh
Whale-Weiss
Whale-Whalen

Statement 6

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;

Package created.

Statement 7

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

ORA-06553: PLS-221: 'BIG_BRAINED_SPECIES' is not a procedure or is undefined

More Details: https://docs.oracle.com/error-help/db/ora-06553

Statement 8

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;

Statement processed.
Bonobo
Dolphin
Gorilla
Human
Octopus
Raven
Whale

Statement 9

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;

Function created.

Statement 10

select column_value species_name 
  from table ( big_brained_species ) 
 order by species_name


SPECIES_NAME
Bonobo
Dolphin
Gorilla
Human
Octopus
Raven
Whale

7 rows selected.

SPECIES_NAME
Bonobo
Dolphin
Gorilla
Human
Octopus
Raven
Whale

Statement 11

select column_value species_name 
  from big_brained_species 
 order by species_name

ORA-04044: procedure, function, package, or type is not allowed here

More Details: https://docs.oracle.com/error-help/db/ora-04044

Statement 12

select column_value species_name 
  from big_brained_species () 
 order by species_name


SPECIES_NAME
Bonobo
Dolphin
Gorilla
Human
Octopus
Raven
Whale

7 rows selected.

SPECIES_NAME
Bonobo
Dolphin
Gorilla
Human
Octopus
Raven
Whale

Statement 13

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;

Package created.

Statement 14

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;

Statement processed.
Very Large Whale
Very Small Raven
Small Octopus
Medium Human
Large Gorilla
Medium Dolphin
Medium Bonobo

Statement 15

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;

Package created.

Statement 16

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;

Package Body created.

Statement 17

Cannot directly invoke function returning associative array inside table operator!

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;

ORA-06550: line 4, column 32:
PLS-00382: expression is of wrong type

More Details: https://docs.oracle.com/error-help/db/ora-06550

Statement 18

Populate variable with call to function, then use that inside the query. This is only an issue when you are executing the query inside PL/SQL.

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;

Statement processed.
Very Large Whale
Very Small Raven
Small Octopus
Medium Human
Large Gorilla
Medium Dolphin
Medium Bonobo

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database