A table function is a function that can be invoked inside the FROM clause of a SELECT statement. They return collections (usually nested tables or varrays), which can then be transformed with the TABLE clause into a dataset of rows and columns that can be processed in a SQL statement. Table functions come in very handy when you need to:
To call a function from within the FROM clause of a query, you need to:
In this tutorial, I will show you how to build and query from a very simply table function, one that returns an array of strings (and, more generally, scalars). The next tutorial will show you how to work with table functions that return collections of multiple columns.
Note: if you are not yet familiar with PL/SQL collections, I suggest you check out my "Working with Collections" YouTube playlist - the link's at the bottom of this tutorial.
First, let's create the nested table type to be returned by the function, then create a function that returns an array of random strings. Finally, demonstrate that the function works - inside a PL/SQL block.
CREATE OR REPLACE TYPE strings_t IS TABLE OF VARCHAR2 (100);
/
CREATE OR REPLACE FUNCTION random_strings (count_in IN INTEGER)
RETURN strings_t
AUTHID DEFINER
IS
l_strings strings_t := strings_t ();
BEGIN
l_strings.EXTEND (count_in);
FOR indx IN 1 .. count_in
LOOP
l_strings (indx) := DBMS_RANDOM.string ('u', 10);
END LOOP;
RETURN l_strings;
END;
/
DECLARE
l_strings strings_t := random_strings (5);
BEGIN
FOR indx IN 1 .. l_strings.COUNT
LOOP
DBMS_OUTPUT.put_line (l_strings (indx));
END LOOP;
END;
/
OK, so the function does its job inside a PL/SQL block. Now let's use it as a table function.
In the FROM clause of your query, right where you would have the table name, type in:
TABLE (your_function_name (parameter list))
You can (and should) give that TABLE clause a table alias. You can, starting in Oracle Database 12c, also use named notation when invoking the function. You can also call built-in functions for the value returned by the table function. All this is demonstrated below.
Notice that Oracle Database automatically uses the string "COLUMN_VALUE" as the name of the single column returned by the table function. You can, as shown, rename it using a column alias.
SELECT rs.COLUMN_VALUE my_string FROM TABLE (random_strings (5)) rs
/
SELECT COLUMN_VALUE my_string FROM TABLE (random_strings (count_in => 5))
/
SELECT SUM (LENGTH (COLUMN_VALUE)) total_length,
AVG (LENGTH (COLUMN_VALUE)) average_length
FROM TABLE (random_strings (5))
/
/* On 12.1 and higher, don't bother with the TABLE clause */
SELECT rs.COLUMN_VALUE no_table FROM random_strings (5) rs
/
Pretty cool, right?
Now that the data from my function can be treated as rows and columns, I can use it just as I would any other dataset in my SELECT statement. Namely, I can join to this "inline view", perform set operations like UNION and INTERSECT, and more. Here are some examples for you to explore:
SELECT e.last_name
FROM TABLE (random_strings (3)) rs, hr.employees e
WHERE LENGTH (e.last_name) <= LENGTH (COLUMN_VALUE)
/
SELECT COLUMN_VALUE last_name
FROM TABLE (random_strings (10)) rs
UNION ALL
SELECT e.last_name
FROM hr.employees e
WHERE e.department_id = 100
/
I can also call the table function inside a SELECT statement that is inside PL/SQL:
BEGIN
FOR rec IN (SELECT COLUMN_VALUE my_string FROM TABLE (random_strings (5)))
LOOP
DBMS_OUTPUT.put_line (rec.my_string);
END LOOP;
END;
/
A left correlation join occurs when you pass as an argument to your table function a column value from a table or view referenced to the left in the table clause. This technique is used with XMLTABLE and JSON_TABLE built-in functions, but also applied to your own table functions.
Here's the thing to remember: the function will be called for each row in the table/view that is providing the column to the function. Clearly, this could cause some performance issues, so be sure that is what you want and need to do. The following code demonstrates this behavior.
CREATE TABLE things
(
thing_id NUMBER,
thing_name VARCHAR2 (100)
)
/
BEGIN
INSERT INTO things VALUES (1, 'Thing 1');
INSERT INTO things VALUES (2, 'Thing 2');
COMMIT;
END;
/
CREATE OR REPLACE TYPE numbers_t IS TABLE OF NUMBER
/
CREATE OR REPLACE FUNCTION more_numbers (id_in IN NUMBER)
RETURN numbers_t
IS
l_numbers numbers_t := numbers_t ();
BEGIN
l_numbers.EXTEND (id_in * 5);
FOR indx IN 1 .. id_in * 5
LOOP
l_numbers (indx) := indx;
END LOOP;
DBMS_OUTPUT.put_line ('more numbers');
RETURN l_numbers;
END;
/
BEGIN
FOR rec IN (SELECT th.thing_name, t.COLUMN_VALUE thing_number
FROM things th, TABLE (more_numbers (th.thing_id)) t)
LOOP
DBMS_OUTPUT.put_line ('more numbers ' || rec.thing_number);
END LOOP;
END;
/
Here's an example of a package-based table function:
CREATE OR REPLACE TYPE strings_t IS TABLE OF VARCHAR2 (100);
/
CREATE OR REPLACE PACKAGE tf
IS
FUNCTION strings RETURN strings_t;
END;
/
CREATE OR REPLACE PACKAGE BODY tf
IS
FUNCTION strings RETURN strings_t
IS
BEGIN
RETURN strings_t ('abc');
END;
END;
/
SELECT COLUMN_VALUE my_string FROM TABLE (tf.strings)
/
But a reference to a nested or private subprogram cannot be resolved in the SQL layer, which is where of course the SELECT statement executes, so errors result, as shown below.
DECLARE
FUNCTION nested_strings (count_in IN INTEGER) RETURN strings_t
IS
BEGIN
RETURN strings_t ('abc');
END;
BEGIN
FOR rec IN (SELECT * FROM TABLE (nested_strings()))
LOOP
DBMS_OUTPUT.PUT_LINE (rec.COLUMN_VALUE);
END LOOP;
END;
/
PLS-00231: function 'NESTED_STRINGS' may not be used in SQL
New to 12.1, you can now use the WITH clause to define functions directly inside a SELECT statement. Such a function can also be used as a table function (warning: as of July 2018, this syntax is not yet supported in LiveSQL; it will work in SQL Developer, SQLcl or SQL*Plus):
WITH
FUNCTION strings RETURN strings_t
IS
BEGIN
RETURN strings_t ('abc');
END;
SELECT COLUMN_VALUE my_string FROM TABLE (strings)
/
There are, basically, two things to keep in mind when it comes to the collection type used in the RETURN clause of a table function:
The SQL engine generally can resolve references to types and PL/SQL programs if they are defined at the schema level or within the specification of a package. When it comes to table functions, however, types defined within a package specification can only be used with pipelined table functions (explored in Module 4 of this class). This is demonstrated in the following code. The strings_sl and strings_pl functions can be invoked successfully as table functions.
CREATE OR REPLACE TYPE sl_strings_t IS TABLE OF VARCHAR2 (100);
/
CREATE OR REPLACE PACKAGE tf
IS
TYPE strings_t IS TABLE OF VARCHAR2 (100);
FUNCTION strings RETURN strings_t;
FUNCTION strings_sl RETURN sl_strings_t;
FUNCTION strings_pl RETURN strings_t PIPELINED;
END;
/
CREATE OR REPLACE PACKAGE BODY tf
IS
FUNCTION strings RETURN strings_t
IS
BEGIN
RETURN strings_t ('abc');
END;
FUNCTION strings_sl RETURN sl_strings_t
IS
BEGIN
RETURN sl_strings_t ('abc');
END;
FUNCTION strings_pl RETURN strings_t PIPELINED
IS
BEGIN
PIPE ROW ('abc');
RETURN;
END;
END;
/
SELECT COLUMN_VALUE my_string FROM TABLE (tf.strings)
/
SELECT COLUMN_VALUE my_string FROM TABLE (tf.strings_sl)
/
SELECT COLUMN_VALUE my_string FROM TABLE (tf.strings_pl)
/
But when I try to use the strings function, I get the "ORA-00902: invalid datatype" error, since it relies on a package-defined nested table type and it is not pipelined.
SELECT COLUMN_VALUE my_string FROM TABLE (tf.strings)
/
You should now have a solid grounding in what constitutes a table function, how to build a table function that returns a collection of scalars, and how to invoke that table function inside a SELECT statement.
Just remember: