Oracle Pipelined Function Generator

On this page you can generate a skeleton for a pipelined function in a package. The necessary types are declared in the package. From the names and the table definition at the left side of the page the following objects are created:

• Skeleton for pipelined function
• View on pipelined function

For more information see this blogpost.

Usage

Start by entering the name of the table (or view). The names for the other objects are deducted from this name, but can be altered by editing. Next enter the column definion in the field Column Descriptions. The content of the field should contain of fieldnames and datatypes on separate lines like:

DEPTNO NUMBER
DNAME VARCHAR2(14)
LOC VARCHAR2(10)

Preceding or trailing comma's are no problem.
For existing tables or views this definition can be obtained from:

• SQL Developer: Execute a describe of the table, i.e. DESC EMP. Cut the output and paste in the Column Definition field.
• SQL Workshop: Go to the Object Browser and choose the desired table or view. Download the column list to Excel by pressing the Download link. Open this download in Excel and cut the Column and Datatype columns. Paste these in the Column Definition Field
• SQL Plus: Perform a describe of the table or view, like DESC EMP. Cut the column definitions and paste in the Column Definition field.

Creation of the script

You can create the script by pushing the button Create Script. The resulting script can copied to the clipboard using the button and then pasted into SQL*PLus, SQL Workshop or SQL Developer. If you choose to use SQL Workshop you can use Create Script to create a script and run it.

The code for the pipelined function is a skeleton that should be customized to your needs. If the table (or view) on which the scripts are based exist the pipelined function should compile as is. In that case you can run the scripts and create all the objects. The changes on the function can be made afterward.