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.
Table 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.
Listing 1.5
shows a modified version of the same function I created in Listing 1.3, and Listing 1.6
shows the same examples as in Listing 1.4, but with the CAST pseudo-function
now removed.
Conclusion
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
