Archive for the ‘nested table’ tag
Result cache functions disallow nested table input parameters
If you implement street address as a nested table (or collection), the problem is printing an address book using only a SQL statement. The problem comes from matching up the multiple line return from the TABLE function with the rest of the row. That’s not a problem when you denormalized the list into known columns.
A PL/SQL function can convert the list into a scalar value. It’s an easy way to leverage a nested table. You can find the full code here. You might think that this is a neat opportunity to use a result cache function. I did, but the behavior isn’t presently supported. On compilation of a function with the RESULT_CACHE clause and a scalar collection input parameter, you raise a PLS-00999 error.
The message tells you:
RESULT_CACHE IS disallowed ON subprograms WITH IN parameter OF (OR containing) nested TABLE TYPE |
Subscribe via RSS