Thursday, 26 May 2016

A generator for pipelined functions

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 in a package with a query based on the table definition
  • the view to encapsulate the pipelined function

In answer to the comment of Denes the separate types have been replaced by types declared within a package

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.

5 comments:

Denes Kubicek said...

Dick,

why not using definition of the type in the package. Creating standalone types is fine but you have objects out there you often forget.

Denes

Dick Dral said...

Hi Denes,

that is a very good idea. I will incorporate it in the next version.

Dick

Gaspar said...

Dick,
This is one great idea! Good job!
I always hated all the typing..

Thanks
Gaspar

Patrick Cimolini said...

Hi Dick,

Very sweet tool. Nicely done.

Cheers,
Patrick Cimolini
Insum

Anonymous said...

It's a great tool. It would be nice to improvise this to parallel pipelined function and the return value is table type. They have more use cases where it needs massive performance improvement