Oracle Table Functions

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:

Please share
  •  
  •  
  •  
  •  
  •  
  •  

AWS Big Data Specialist. Oracle Certified Professional (OCP) for EBS R12, Oracle 10g and 11g. Co-author of "Expert Oracle Enterprise Manager 12c" book published by Apress. Awarded as Oracle ACE (in 2011) and Oracle ACE Director (in 2016) for the continuous contributions to the Oracle users community. Founding member, and vice president of Turkish Oracle User Group (TROUG). Presented at various international conferences including Oracle Open World.

Leave Comment

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.