Extreme Oracle... part 3

In the previous part I described how we use ref cursors for passing data from the Oracle layer into the PHP layer. Unfortuantely ref cursors are resticted to SQL SELECT statements. If you're retriving a set of data that is derived from something that is not clearly expressed as a SQL statement then we can easily find that we are breaking our XP rule of simplicity.
This can be especially true when producing reports:

For complex return sets, build PL/SQL table structures and cast into SQL tables.
From Oracle 9i onwards there is the ability to cast certain PL/SQL table structures into tables and select from them with standard SQL. As an example: given that the function sales_figures_by_day returns a PL/SQL table of data with its structure defined in a globally declared nested table, we can SELECT from this using the statement SELECT * FROM TABLE( sales_figures_by_day() )

By using this we can split our complex SQL statements into smaller, more clearly understood steps. Each step adds data to, or transforms data in our PL/SQL table structure. We can unit test each of these steps in isolation. Once we have completed building our PL/SQL table we can retrieve the results using SQL. Since we can use SQL to SELECT from the PL/SQL table we can use a ref cursor to pass this SELECT statement back to the PHP layer. The PHP layer is not aware that the PL/SQL table ever existed.

Alternatively,
For complex return sets, build in 'global temporary table' structures and SELECT from them instead.
From Oracle 8i onwards we can create temporary tables which store data only for the current session, hiding it from all other sessions and cleaning up either when the current session or transaction ends. In all other respects we treat the global temporary table as if it was a standard SQL table. We can build complex result sets in a similar way as with our PL/SQL tables and return the data to the PHP layer by selecting from the resulting temporary table.

Again, the task is split into smaller, clearer chunks of work and the result is a more clearly understood series of actions.

Whilst the temporary table option requires less work, I personally favour the PL/SQL table option for returning complex datasets. Since the global temporary table is dealt with in exactly the same way as a SQL table it is unclear when reading the code that the resulting data is not permanent. It has the potential to cause confusion. Whilst the PL/SQL table method does take a little more work it is obvious from the form of the code that a result set is being created.

Before returning complex result sets, remind yourself what you're hiding from the PHP layer
Whilst we hide the physical structure from the PHP layer, the logical structure is still very much the responsibility of the system as a whole.
By 'physical structure' I mean that fact that, say, products and sales are kept in seperate tables within the database. By 'logical structure' I mean that a sale must relate to a particular product as it makes no sense to have a sale without one.
If you find that you are writing a lot of functions that return complex return data, then there may be cause to take a step back. Ask yourself, would it be inappropriate for the PHP layer to call several distinct database functions to retrieve data and then build the more complex structures itself. Is the stucture you are attempting to hide a physical one, or a logical one? Is the fact that you need to return a complex structure pointing to a problem with the PHP object model, or the underlying database physical model?