Table functions are functions that produce a collection or rows (either a nested table or a varray) that can be queried like a physical database table. You use a table function like the name of a database table, in the FROM clause of a query. The difference with a physical database table in the FROM clause of the query is that you need to use the TABLE() operator to tell the SQL engine that it should treat whatever comes out of the function as if it were a normal table.

Table function concepts

There a couple of steps to take when you are working  with table functions. Like when you are working with normal tables you  have to describe the way the records of the collection are set up. the steps are: Define Record Type, Define Nested Table type, Define function returning Nested Table type, Query function using the TABLE() operator, Nesting functions.

Define Record Type

First of all you define a record type. This is pretty  much the same records are defined in a database table, only difference is that you are now defining an object type instead of a table.record_type

CREATE TYPE script_line AS OBJECT   (line NUMBER   ,text VARCHAR2(32767)) /

This creates a type that can hold the contents of a single record. Compare this to a record in a physical table.

You can read my full article on Using Table Functions at: http://bar-solutions.com/weblog/?p=532

Tags: , , , ,