A table function is a PL/SQL function which will behave like a row source when queried. So you can perform transformations to the data before it is returned in the result set.

To write a table function, first we need to define an object as the row structure:

Because our function will return a table, we’ll also define a type:

This table will be used to return the rows from the our function. Here’s the sample table function which will accept department id as parameter and return the employees in the chosen department:

Let’s call this function:

Oracle provides PIPELINED option. This option tells Oracle to return the results of the function as soon as they are processed. So the table function requires less memory to produce even large result sets.

Here’s the pipelined version of the above function:

Leave a Reply

Your email address will not be published. Required fields are marked *