When Is a Function Like a Table? When It’s a Table Function!

Combine the power of SQL with the procedural control of PL/SQL.

By Steven Feuerstein

July/August 2018

What does a PL/SQL function do? A function returns a value. That value can be a scalar, such as a string, as in this function:

CREATE OR REPLACE FUNCTION longer_string (
   string_in IN VARCHAR2, to_length_in IN INTEGER)
   RETURN VARCHAR2
   AUTHID DEFINER
IS
BEGIN
   RETURN RPAD (string_in, to_length_in, 'x');
END;
/

A function can also return a more complex data type, such as a record or even a collection. To demonstrate that, I first declare a schema-level nested table type:

CREATE OR REPLACE TYPE strings_t IS TABLE OF VARCHAR2 (100)
/

Then I define a function that returns a collection of randomly generated strings of that type:

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;
/

This article takes a closer look at functions that return collections and how they can be used as table functions, which means that you can query the contents of the value returned as if it were a relational table.

Use Cases for Table Functions

There are several scenarios in which table functions come in handy.

Merge session-specific data with data from tables. You’ve got data—lots of it—sitting in tables. But in your session (and not in any tables), you have some data, and you need to “merge” these two datasources together in a SQL statement. In other words, you need the set-oriented power of SQL to get some answers. With the TABLE clause and table functions, you can accomplish precisely that.

Programmatically construct a dataset to be passed as rows and columns to the host environment. Your web page needs to display some data in a nice, neat report. That data is, however, far from neat. In fact, you need to execute procedural code to construct the dataset. Sure, you could construct the data, insert it into a table, and then do a SELECT from the table. But with a table function, you can deliver that data immediately to the web page, without any need for nonquery data manipulation language (DML).

Emulate a parameterized view. Oracle Database does not support true parameterized views, as in

CREATE OR REPLACE VIEW my_view (param1_in  IN INTEGER) ...

You can achieve a similar effect with system contexts, in which the WHERE clause calls the SYS_CONTEXT function to obtain a value from your session. But you can also use a table function, which certainly does accept parameters.

Restrict developer access to tables. If you follow strictly the “Smart Database” or “Thick Database” paradigm, neither users nor developers are ever given direct access to tables—not even with the SELECT privilege! For nonquery DML (inserts, updates, deletes), the recommendation is to provide PL/SQL packaged procedures to perform those operations. For queries, you can achieve complete control over access to underlying tables by making the data available only through a table function. Instead of granting the SELECT privilege on a table, you grant the EXECUTE privilege on the package containing the table function.

Perform data warehouse transformations. A common requirement in data warehouse environments is to perform transformations—and usually multiple such transformations—from one table to another, as shown in Figure 1.

Figure 1: Table transformations on the way to a data warehouse

A special type of table function, called a streaming table function, supports these transformations elegantly and with high performance.

Exploring Table Functions

Now let’s see how to use the random_strings function, both as a “normal” function in a PL/SQL block and as a table function. In the following block, I call the random_strings function and then display the resulting strings using DBMS_OUTPUT.PUT_LINE:

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;
/

And here’s an example of the output you might see (but likely not, because these values are produced by a sophisticated pseudorandom generator):

KKEMEEIPES
QOZKUGIFWF
CDYPAOTORD
PHRPCHZQIT
LOUVRRMWBX

And now I forgo the PL/SQL block, along with the declaration of a local variable, and simply call the function in a query:

SELECT COLUMN_VALUE my_string
  FROM TABLE (random_strings (5))
/

And I see

MY_STRING
——————————
PGITKKZYAV
IYXWISVYAC
ZTTQATVKLC
MJVAXASRNC
CHOFPTGQTR

And there it is: a table function.

As we have all come to expect with Oracle Database, the SQL engine does most of the heavy lifting for us when it comes to table functions. When you call your function inside the TABLE clause of a SELECT statement, the SQL engine transforms the set of data returned by the function into a relational result set. This result set can then be manipulated like a result set from a table or a view.

When each element of the collection type is a scalar value, as is the case with strings_t above, the name of the single column for that result set is automatically set to COLUMN_VALUE. You can change it, of course, with a column alias, as I showed above.

When you are returning a collection of object types (possible since Oracle9i Database) or of records (possible in Oracle Database 12.2 and higher), the names of the object type attributes or record fields can be referenced as individual columns. Here’s an example:

CREATE OR REPLACE TYPE two_strings_ot
   AUTHID DEFINER IS OBJECT
(
   string1 VARCHAR2 (10),
   string2 VARCHAR2 (10)
)
/

CREATE OR REPLACE TYPE two_strings_nt IS TABLE OF two_strings_ot
/

CREATE OR REPLACE FUNCTION three_pairs
   RETURN two_strings_nt
   AUTHID DEFINER
IS
   l_strings   two_strings_nt;
BEGIN
   RETURN two_strings_nt (two_strings_ot ('a', 'b'),
                          two_strings_ot ('c', 'd'),
                          two_strings_ot ('e', 'f'));
END;
/

SELECT string1, string2 FROM TABLE (three_pairs ())
/

STRING1 STRING2
——————— ———————
a       b
c       d
e       f

How Can I Use the Function’s Result Set?

Once you’ve nestled your function call inside the TABLE clause, you can use the result set just as you would the result set from a table or an inline view. You can join to that set, you can use columns in WHERE clauses, and you can use set operators. Here are some examples using the three_pairs function, plus this simple table:

CREATE TABLE string_pairs
(
   string1   VARCHAR2 (10),
   string2   VARCHAR2 (10)
)
/

BEGIN
   INSERT INTO string_pairs
        VALUES ('a', 'bb');

   INSERT INTO string_pairs
        VALUES ('cc', 'dd');

   COMMIT;
END;
/

Now I use the UNION ALL set operator, combining data from the table and the table function:

SELECT * FROM string_pairs
UNION ALL
SELECT * FROM TABLE (three_pairs ())
/

STRING1    STRING2
———————    ———————
a          bb
cc         dd
a          b
c          d
e          f

Next, I join the table and the table function together:

  SELECT sp.string1,
         sp.string2 sp_string2,
         p3.string2 ps_string2
    FROM string_pairs sp, TABLE (three_pairs ()) p3
   WHERE sp.string1 = p3.string1
ORDER BY string1
/

STRING1    SP_STRING2 PS_STRING2
—————————— —————————— ——————————
a          bb         b

I could even hide the fact that I am using a table function, by putting it inside a view:

CREATE OR REPLACE VIEW three_pairs_v
AS
   SELECT * FROM TABLE (three_pairs ())
/

SELECT * FROM string_pairs
UNION ALL
SELECT * FROM three_pairs_v
/

STRING1    STRING2
———————    ———————
a          bb
cc         dd
a          b
c          d
e          f

What Collection Types Can Be Used?

You can use nested table and varray types defined at the schema level with a CREATE OR REPLACE TYPE statement, as shown earlier for two_strings_nt.

You can also use nested table and varray types defined in the specification of a package, but only for pipelined table functions (a special kind of table function).

Here is a very simple example of a pipelined table function based on a package-specified collection type:

CREATE OR REPLACE PACKAGE my_pkg AUTHID DEFINER
AS
   TYPE names_t IS TABLE OF VARCHAR2 (100);
   FUNCTION my_names RETURN names_t PIPELINED;
END;
/

CREATE OR REPLACE PACKAGE BODY my_pkg
AS
   FUNCTION my_names RETURN names_t PIPELINED
   IS
   BEGIN
      PIPE ROW ('Loey');
      PIPE ROW ('Juna');
      PIPE ROW ('Grandpa Steven');
      RETURN;
   END;
END;
/

SELECT COLUMN_VALUE a_name FROM TABLE (my_pkg.my_names)
/

A_NAME
——————————————
Loey
Juna
Grandpa Steven

You’ll see more about pipelined table functions in future articles.

Note that if you try to use a collection type that is defined locally, you will get an error:

DECLARE
   TYPE numbers_t IS TABLE OF NUMBER;

   l_numbers   numbers_t := numbers_t ();
   l_count     INTEGER;
BEGIN
   SELECT COUNT (*) INTO l_count FROM TABLE (l_numbers);
END;
/

ORA-06550: line 7, column 46:
PLS-00642: local collection types not allowed in SQL statements

Flexibility and Power: Table Functions to the Rescue

Table functions give database developers an incredible amount of flexibility and power. You can use table functions to combine the set-oriented, declarative power of SQL with the procedural control of PL/SQL to meet a variety of challenges.

Future articles will explore table function use cases, streaming table functions, and pipelined table functions.

Next Steps

LEARN more about Table Functions at the Oracle Dev Gym.

LEARN more about PL/SQL.

EXPLORE the code used in this article at Oracle’s Live SQL.