Making Use of Table Functions -- Part I

Having in my practice found many useful applications for table functions I am a big fan of this feature. Unfortunately, I don’t think it always gets the attention it deserves. Oracle documentation does a decent job of explaining how to code table functions, but the use cases they provide can lead one to believe this feature has no universal application and is meant to solve just a few specific kinds of problems.

In addition to Oracle documentation, many good articles have been written to explain how to code table functions. In that respect I have hardly anything to add to the existing literature. Instead, as the title of this article suggests, I will focus on showing the kinds of situations where I found table functions most useful.

I will first discuss the advantages of returning result sets from the code stored in the database, as opposed to embedded SQL. Next, I will compare the features available in views, stored procedures that return record sets, and table functions.
In Part II, I will give examples of solutions that utilize table functions.

From Views to Ref Cursors to Table Functions

Returning data to users from code stored in the database offers many advantages over SQL embedded in applications. The more commonly cited ones tend to fall into the general categories of maintainability and security.

If you haven’t already adopted the practice of implementing your Data Access Layer inside the database, consider how much easier it would be to:

1. Locate code by utilizing the power of the Data Dictionary search or just browsing a single repository.
2. Support the transformation of data to the Presentation Layer.
3. Pinpoint application code that causes performance bottlenecks.
4. Analyze change impact and to browse object dependencies when they get automatically recorded in the Data Dictionary.
5. Secure data access by granting privileges to stored code instead of database tables.

In the early versions of Oracle views were your only option to return record sets from code stored in the database. While views were adequate for most purposes, they did impose some serious limitations:

1. Oracle views do not accept parameters.

   Applying additional WHERE conditions when querying a view provides some substitute but not a very good one. With deeply nested logic your SQL can become complex and inefficient.

2. Views cannot utilize PL/SQL

   As the rule of thumb I prefer to take advantage of the power of SQL set processing, as opposed to PL/SQL row-by-row operations, but for some tasks using SQL alone can be either impossible or impractical, requiring the flexibility of PL/SQL.
   

Suppose your application requests all customer transactions for the given calendar date. Further suppose all recent transactions are stored in the TRANSACTION ODS table and are periodically moved to the TRANSACTION_HISTORY Data Warehouse table. For the optimal performance you would want to first evaluate the WHERE clause, and then determine which table to query. With PL/SQL you can code two cursors and open them conditionally, depending on the value of the Transaction Date parameter. Alternatively, you can use Native Dynamic SQL to assemble the FROM clause on the fly. With SQL alone you have to either read both tables or to embed data access logic in the application.

These limitations were addressed with the introduction of stored procedures returning collections. This feature allowed utilizing the full flexibility of PL/SQL but it came with some limitations of its own:

1. No SQL operations (like joining or filtering) can be performed on result sets returned by procedures.
2. Many third party client applications do not support Oracle collections.

With table functions, first introduced in Oracle 9i, you can have the best of both worlds. The record sets returned by table functions can be filtered with a WHERE clause, grouped, and joined to either database tables or even result sets of other table functions.
Since table functions are invoked in SELECT statements, no special support is required on the client side.

In the next section we will see table functions in action and I will demonstrate several examples of using this feature as an integral part of the overall solution architecture.