When you’re using a relational database, you are almost certainly writing SQL to get the job done.
And the most commonly executed SQL statement is the select, which retrieves data from one or more datasets.
Those datasets are usually tables, views, and in-line views. But that’s not all you can stick in the from clause. You can also select from a collection! Here’s an example using a nested table.
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;
/
Bonobo
Dolphin
Gorilla
Human
Octopus
Raven
Whale
As you can above, you can use an order by clause with a collection. You can also apply a where clause, join with others tables, etc.
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;
/
Bonobo
Gorilla
Octopus
Dolphin
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, employees e
-- a silly join condition but you get the idea!
where substr (bbs.column_value, 1, 1) = substr (e.last_name, 1, 1)
) loop
dbms_output.put_line(rec.name);
end loop;
end;
/
Octopus-OConnell
Octopus-Olsen
Octopus-Olson
Octopus-Ozer
Raven-Rajs
Raven-Raphaely
Raven-Rogers
Raven-Russell
Whale-Walsh
Whale-Weiss
Whale-Whalen
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
A few observations:
-
- The collection must be based on a type that is known to the SQL engine. This means it must be defined at the schema (create or replace type, which works for nested tables and arrays) or within a package specification (which works for those, plus associative arrays).
- When you are querying from a collection of scalars (a list of strings, for example), then the name of the column in the query is always COLUMN_VALUE. You can, of course, change that with a column alias.
- When you return a collection of records or object types, then the names of attributes/fields become the names of columns for each row (see the following example).
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;
/
Very Large Whale
Very Small Raven
Small Octopus
Medium Human
Large Gorilla
Medium Dolphin
Medium Bonobo
Oracle Database is clearly smart enough to recognize that a collection is really no different from a table – a bunch of rows and columns (even if the terminology we use with collections is a bit different). But it’s waaaaay smarter than that. It also recognizes that a function that returns a collection is also really no different from a table.
And that brings us to the table function.
A table function is a function that acts like a table when it is invoked in the from clause of a query. Let’s switch that big_brained_species from a collection to a function:
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;
/
And now I can call the function from inside the from clause:
select column_value species_name from table ( big_brained_species ) order by species_name / SPECIES_NAME ------------ Bonobo Dolphin Gorilla Human Octopus Raven Whale
But if I leave off the table operator (which became possible in 12.1), I have to tell it that it’s a function by providing parentheses for the parameter list, even if there are no parameters:
select column_value species_name from big_brained_species order by species_name / ORA-04044: procedure, function, package, or type is not allowed here select column_value species_name from big_brained_species () order by species_name / SPECIES_NAME ------------ Bonobo Dolphin Gorilla Human Octopus Raven Whale
Of course, in the code you write, you are not going to be filling up the collection with static values like that. But it does drive home a point about one of the key use cases for table functions, a list of which I offer below.
Use table functions to….
Access via SQL session-specific data that is not in a table, usually to merge it with data from one or more tables
I showed you an example of this above, when I joined with the employees table. Rather than insert in-session data into a temporary table, simply build a function around it. That’ll be much more efficient (and you won’t have to worry about cleaning up that “temporary” table – by which I mean, many times we create a table fully believing it will be temporary, but then never remove it).
Programmatically construct (and query from) a data set that you are unable to obtain with SQL
Let’s face it: we can’t all be like Connor McDonald, Chris Saxon, Tim Hall, Kim Berg Hansen or Tom Kyte, when it comes to finding ways to do ANYTHING AND EVERYTHING with “pure” SQL.
Sometimes, we feel certain it is possible to get the job done with SQL, but just can’t figure it out. And the clock is ticking.
There’s no shame in “falling back” on PL/SQL and procedural logic to construct the data set you need.
There’s also no shame in asking for help with your SQL. 🙂
Implement the equivalent of a parameterized view
You cannot in Oracle Database create a view that accepts parameters, as in:
create view wouldn't_this_be_wonderful (p_employee_id in number) ....
But you can certainly have a parameter list on a table function (and almost all of them do).
Fully implement the SmartDB paradigm
SmartDB is an approach to building applications that fully leverages the underlying relational database to ensure maximum performance, security and maintainability of code.
Roughly speaking, with SmartDB, select, insert, update, delete, merge, commit, and rollback statements are issued only from database PL/SQL. So your application code would never contain an insert statement. Instead, it would call a procedure that does the insert. And instead of executing big, complex queries with multi-table joins, 15 lines of where clauses, etc., directly from your application (say, from an Interactive Report in Oracle APEX), you would select from a table function, which contains all the actual SQL logic.
Two downsides of table functions
There are two things to note about table functions, which should give you pause:
- The SQL engine has to stop and wait for the table function to execute and return its collection before it can continue. That’s to be expected, but what if you are executing a parallel query? It can wreak havoc by causing serialization.
- You build and return a collection. If you have lots of data, you have a big collection….which is stored in your session-specific memory, a.k.a., Process Global Area or PGA. These have limits. You could blow the limit and make users unhappy.
The way to get around these problems is to implement a specialized version of the table function, which is the pipelined table function.
In my next Feuertip, I’ll take a look at these very unusual functions. Why do I say unusual? Well, have you ever seen a function whose return statement looks like this?
function my_pipelined_function (...) return my_collection is begin ... you'll see this code next week ... return; end;
I mean, seriously, WHAT? I don’t return anything? How can that be?
Stay tuned….
Further study
You can execute and explore all the code in this post via my LiveSQL script.
Always a good place to start, and almost certainly not a good place to end. 🙂
ORACLE-BASE on table functions
Tim Hall does his usual fantastic job of presenting concepts and code in a simple, clear fashion that makes it easy for you to get up and running fast.
This link will bring you to a wide variety of scripts that demonstrate table functions, including two type I didn’t get to in this article: pipelined table functions and polymorphic table functions.
A five-part series on table functions. Best way to get up to speed on this functionality!
My blog series on table functions
This blog series formed the basis for the Dev Gym class and associated LiveSQL tutorials.
Bryn Llewellyn’s Series on PostgreSQL table functions
I bet a lot of you remember Bryn; he was the PL/SQL product manager for a loooooong time. Left for YugabyteDB in 2019. He wrote a series about table functions for PostgreSQL that you might find interesting.
