One of the options for solving performance issues is the use of pipelined functions. The performance of queries can in some cases be improved dramatically by the use of PL/SQL instead of pure SQL.
The setup of pipelined functions requires however the creation of two object types for the return values and a function in which these object type are filled with values. There is a lot of typing before you can start to create the actual logic.
For this purpose the Oracle Pipelined Function Generator is created. With this tool you can generate a skeleton for the pipelined function with the required types. You can start coding the logic immediately!
The basis of the generation is a table definition with column names and data types.
A script for the following objects is generated :

  • the pipelined function with a query based on the table definition
  • the type to be used in the PIPE ROW command
  • the type to be used as the return type of the function
  • the view to encapsulate the pipelined function

If your pipelined functions recieves parameters you can use the technique with package variables that is described in another blogpost.

The online generation of the objects is available here:

Happy coding,
Dick Dral

PS This generator is an overhauled version of my old generator. The formatting of the create script has been improved, the application’s theme is changed to UT and a Copy to Clipboard button has been added.

Source Article from http://dickdral.blogspot.com/2016/05/a-generator-for-pipelined-functions.html

Leave a Reply