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?
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. 🙂
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!
This blog series formed the basis for the Dev Gym class and associated LiveSQL tutorials.
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.