Transformation Using Table Functions
Oracle9i’s table functions provide the support for pipelined and parallel execution
of transformations implemented in PL/SQL, C, or Java. Scenarios as mentioned
earlier can be done without requiring the use of intermediate staging tables, which
interrupt the data flow through various transformations steps.
What is a Table Function?
A table function is defined as a function that can produce a set of rows as output.
Additionally, table functions can take a set of rows as input. Prior to Oracle9i,
n Could not take cursors as input
n Could not be parallelized or pipelined
Starting with Oracle9i, functions are not limited in these ways. Table functions
extend database functionality by allowing:
n Multiple rows to be returned from a function
n Results of SQL subqueries (that select multiple rows) to be passed directly to
n Functions take cursors as input
n Functions can be parallelized
n Returning result sets incrementally for further processing as soon as they are
created. This is called incremental pipelining
Table functions can be defined in PL/SQL using a native PL/SQL interface, or in
Java or C using the Oracle Data Cartridge Interface (ODCI).
See Also: PL/SQL User’s Guide and Reference for further
information and Oracle9i Data Cartridge Developer’s Guide
Oh I see. You said "Function Tables" not "Table Functions", but then I had not heard them called that before anyway! But, in any case: no, I don't see how these are going to help with what you are trying to do, which is insert data from one table into another. Whether you SELECT the data direct, or get it via a function call that returns many rows of data, you are still going to have to INSERT the data and COMMIT it at some point, and the same rollback space issues will apply.
What about our previous suggestions - have you tried any of those? I'm not even sure whether you actually have a problem running the INSERT..SELECT statement, or whether you just fear that you might. The simplest, fastest and best way to do it is with a single INSERT..SELECT followed by a COMMIT, with a rollback segment big enough for the job.