Synopsis. Oracle 8i offered the CAST function, which allows a PL/SQL collection to be processed as if it was a normal table. When CAST is paired with table functions, it becomes even more powerful as a data manipulation toolset. This article provides a brief technical explanation of CAST and table functions as well as some practical, real-world examples of their use in concert.
I was assisting one of my developers with a problem with a rather complex stored procedure that is used extensively by our flagship data entry application to determine all eligible cost centers to which an employee can potentially charge his or her expenditures. Here is a brief summary of the business rules involved:
- An employee is part of a department, which in turn is part of a division.
- Each employee is assigned between one and five cost centers to which he can charge expenditures.
- Each department has up to five default cost centers to which expenditures may be charged.
- Each division has up to five default cost centers to which expenditures may be charged.
- Once the list of potential eligible employee, department default, and division default cost centers has been determined, only those cost centers that any employee can use are allowed to be retained in the list.
- Duplicate cost centers must be removed from the final list.
To make matters more interesting, the stored procedure that implements these business rules must return the answer set in the form of a reference cursor (REF CURSOR) because the application requires it. Unfortunately, the application cannot accept one of those neat Oracle collection types as an input parameter without significant alteration to an aging Powerbuilder code base.
When I opened up the stored procedure, I noticed that it dated back prior to the conversion of the company's original Sybase database to our current Oracle database. Sybase has some interesting features for storage of temporary data - essentially, a huge TEMP space that's readily available for use by any stored procedure - and whoever had converted the procedure to Oracle had decided to mimic that technique by using a GLOBAL TEMPORARY table (acronyzed to GTT) to retain the data.
GTTs certainly have their place in an Oracle database, but they do have some drawbacks. First, a GTT is still a table, and I have noticed that developers often forget to COMMIT changes written to a GTT. In addition, the overhead of creating and maintaining the schema of a GTT is often overkill in situations like this one - after all, the most records I'd ever return in this answer set is 15.
I have also run into issues when attempting to open a hot standby database in READ ONLY mode and then tried to run stored procedures that need to utilize GTTs. Because GTTs are owned by the SYSTEM tablespace, and since that tablespace is in read-only mode when the standby is opened in this fashion for reporting, the stored procedures used to create reports simply stop working. There are workarounds to this, but they are not very elegant.
Fortunately, Oracle has implemented some neat features that let me overcome the reliance on GTTs: the CAST function and the ability to write stored functions that return PL/SQL collection types, also known as table functions. When used in conjunction, these features form a powerful toolset that conquers GTTs that have been used in this manner. (Moreover, they work just fine even if you do not have any GTTs to conquer, by the way!)
The CAST Function
CAST is often termed a pseudo-table function because it lets me cast a variable - specifically, a PL/SQL collection - into another datatype: a table construct. The table construct can then be queried with standard SQL just like any other Oracle table with SQL.
Listing 1.1 shows an example of how the CAST function can be used inside an anonymous PL/SQL block to read from a PL/SQL collection defined by a declared TYPE. CAST is used here to sort the resulting rows in reverse alphabetical order. I could have created the TYPE as a true object, and built a sort function for the object - but CAST lets me use good old SQL to handle the sorting.
Listing 1.2 shows another example of CAST. This time, I am populating a PL/SQL collection with a set of random numbers. I then use CAST to gather the data from the collection and apply various group functions like SUM(), MIN(), and MAX() on the result set. Again, I could have declared an object type and written some special grouping functions for it - but again, I utilized CAST to do the work with normal SQL grouping functions.
A table function is a stored function that returns a PL/SQL collection as the result set, and this result set can then be read and manipulated with the CAST function. The example table function I have created, sf_gather_cost_centers, in Listing 1.3 uses a PL/SQL collection to store the gathered cost center values for the specified employee and return a list of all cost centers that the employee is eligible to use depending on that employee's relationship to her department and division.
Listing 1.4 shows three examples that gather the results of the table function that I have just created via the CAST table pseudo-function for later manipulation. The result set from these SQL statements could then be returned in a reference cursor generated from within the existing stored procedure with a few modifications as mandated by my original requirements.
PIPELINED Table Functions
Table functions were available as of in Oracle 8i, but they were enhanced in Oracle 9i so that result sets can be pipelined. Briefly, a pipelined table function does not require the CAST pseudo-table function to return a result set.
CAST and table functions are going to become a powerful set of tools on my PL/SQL development "tool belt," and hopefully I have whetted your appetite to experiment with these features as well. Should you wish to experiment with these examples, I have also provided the necessary DDL and DML statements to modify the standard HR demo schema appropriately (see Listing 2).
References and Additional Reading
While there is no substitute for direct experience, reading the manual is not a bad idea, either. I have drawn upon the following Oracle 9i Release 2 documentation for the deeper technical details of this article:
A96595-01 Oracle 9i Data Cartridge Developer's Guide, Chapter 12
A96624-01 Oracle 9i PL/SQL User's Guide and Reference, Chapter 8