A Dev Gym Class

Get Started with PL/SQL Table Functions

A free Table Functions course

Table functions are functions that act like tables in a SELECT statement: you can query columns and rows from the array returned by the function! Table functions are a great way to programmatically construct data sets and also play a key role in data warehouses that need to perform multiple transformations of data within a single SQL statement. This class starts with table function fundamentals, then explores streaming table functions, and finishes up with a look at pipelined table functions.

Before starting this class, you should be comfortable with writing SQL SELECT statements and working with PL/SQL collections (primarily nested tables). When the course begins, you will find links at the bottom of this page to help you do just that.
Join 19,188 students    (358 ratings)

Information

Learn Table Functions

Oracle Account Text

You need an Oracle Account to take a class. Click here to sign up.
Already a member of the Oracle Dev Gym? Click here to get back to work.

Course Modules

Overview and Fundamentals of Table Functions

Table functions can be very simple, and very complicated. We start with the most basic example: a function that returns an array of scalar values. Watch the video, take the LiveSQL tutorial, and then take some quizzes to reinforce your knowledge.

Returning Multiple Columns from a Table Function

Suppose you want your table function to return multiple columns, just like most relational tables. Then what do you do? Well, you create an object type, an array of those object types, and construct data as needed to populate that array. Sound complicated? Not really! Watch our video, read a blog post, then finish up with quizzes to drive the points home.

Streaming Table Functions

A common usage of table functions is to stream data directly from one process or transformation to the next process without intermediate staging. Hence, a table function used in this way is called a streaming table function. This technique is most often used in data warehouses. Streaming table functions usually accept as parameters result sets (rows and columns returned by SELECTs), which then requires the use of the CURSOR expression. Watch our video, dive into more details with a blog post, and then answer some quizzes to be sure you've absorbed the material!

Pipelined Table Functions

Pipelined table functions are a specialized variation of table functions that can only be used in a SELECT statement (not within a PL/SQL block natively) and allow results to be "piped" directly back to that SELECT statement for immediate use (even before the function has finished all its work!). Pipelined table functions can offer a big performance boost and greatly reduce PGA consumption. Learn all about them from our video, blog post and quizzes!

Frequently Asked Questions

What are the prerequisites for this class?

You should have working familiarity with SELECT statements and PL/SQL collections.

Do I get a certificate when I complete the course?

You sure do! Once you complete all modules, you will see a Print Certificate button on the class page. Click on it, and we will generate a PDF with the certificate. If your overall grade on all quizzes is >= 90%, then you get a special certificate of excellent.

When does the class start?

Whenever you're ready! This course is available on-demand. Start when you are ready, stop whenever you need to work on something else. There is no end date on the course, so take as long as you want.

Do I need access to an Oracle Database?

Yes and no. This is a fully online course. You do not need a database of your own. But each module includes a LiveSQL tutorial, and that website comes with its own database for you to use.

What is the format of this course?

Each module consists of (1) a video in which Steven Feuerstein explains and demonstrates the topics in that module; (2) a LiveSQL tutorial taking you step by step through the same content, allowing you to work with the code yourself; (3) 3 or more quizzes to reinforce the knowledge you've just gained.
Join Get Started with PL/SQL Table Functions

Instructor

Instructor

About Steven Feuerstein

Steven Feuerstein is a Senior Advisor at Insum Solutions (https://insum.ca), and is obsessed with the Oracle PL/SQL language, having written ten books on it (all published by O'Reilly Media). You will find an abundance of quizzes from Steven at the Dev Gym. Enjoy!

More from Steven Feuerstein