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 warehouseA 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.
LEARN more about Table Functions at the Oracle Dev Gym.
LEARN more about PL/SQL.
Photography by Jantine Doornbos, Unsplash
Steven Feuerstein is Oracle Corporation's Developer Advocate for PL/SQL, and an expert on the Oracle PL/SQL language, having written ten books on PL/SQL, including Oracle PL/SQL Programming and Oracle PL/SQL Best Practices (all published by O'Reilly Media). Steven has been developing software since 1980, spent five years with Oracle back in the "old days" (1987-1992), and was PL/SQL Evangelist for Quest Software (and then Dell) from January 2001 to February 2014 - at which point he returned joyfully to Oracle Corporation. He was one of the original Oracle ACE Directors and writes regularly for Oracle Magazine, which named him the PL/SQL Developer of the Year in both 2002 and 2006. He is also the first recipient of ODTUG's Lifetime Achievement Award (2009).