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, PL/SQL functions:
■ Could not take cursors as input.
■ Could not be parallelized or pipelined.
Now, functions are not limited in these ways. Table functions extend database functionality by allowing:
■ Multiple rows to be returned from a function.
■ Results of SQL subqueries (that select multiple rows) to be passed directly to functions.
■ Functions take cursors as input.
Transformation Mechanisms
14-10 Oracle Database Data Warehousing Guide
■ Functions can be parallelized.
■ 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).
Figure 14–3 illustrates a typical aggregation where you input a set of rows and output a set of rows, in that case, after performing a SUM operation.
Figure 14–3 Table Function Example
The pseudocode for this operation would be similar to:
INSERT INTO Out SELECT * FROM ("Table Function"(SELECT * FROM In));
The table function takes the result of the SELECT on In as input and delivers a set of records in a different format as output for a direct insertion into Out.
Additionally, a table function can fan out data within the scope of an atomic
transaction. This can be used for many occasions like an efficient logging mechanism or a fan out for other independent transformations. In such a scenario, a single staging table will be needed.
Figure 14–4 Pipelined Parallel Transformation with Fanout
The pseudocode for this would be similar to:
INSERT INTO target SELECT * FROM (tf2(SELECT * FROM (tf1(SELECT * FROM source))));
This will insert into target and, as part of tf1, into Stage Table 1 within the scope of an atomic transaction.
INSERT INTO target SELECT * FROM tf3(SELT * FROM stage_table1);
See Also: Oracle Database PL/SQL User's Guide and Reference for further information and Oracle Database Data Cartridge Developer's Guide
Transformation Mechanisms
Loading and Transformation 14-11 Example 14–6 Table Functions Fundamentals
The following examples demonstrate the fundamentals of table functions, without the usage of complex business rules implemented inside those functions. They are chosen for demonstration purposes only, and are all implemented in PL/SQL.
Table functions return sets of records and can take cursors as input. Besides the sh sample schema, you have to set up the following database objects before using the examples:
CREATE TYPE product_t AS OBJECT ( prod_id NUMBER(6) , prod_name VARCHAR2(50) , prod_desc VARCHAR2(4000) , prod_subcategory VARCHAR2(50) , prod_subcategory_desc VARCHAR2(2000) , prod_category VARCHAR2(50)
CREATE TYPE product_t_table AS TABLE OF product_t;
/ COMMIT;
CREATE OR REPLACE PACKAGE cursor_PKG AS TYPE product_t_rec IS RECORD (
prod_id NUMBER(6) , prod_name VARCHAR2(50) , prod_desc VARCHAR2(4000) , prod_subcategory VARCHAR2(50) , prod_subcategory_desc VARCHAR2(2000) , prod_category VARCHAR2(50)
TYPE product_t_rectab IS TABLE OF product_t_rec;
TYPE strong_refcur_t IS REF CURSOR RETURN product_t_rec;
TYPE refcur_t IS REF CURSOR;
END;
/
REM artificial help table, used later
CREATE TABLE obsolete_products_errors (prod_id NUMBER, msg VARCHAR2(2000));
The following example demonstrates a simple filtering; it shows all obsolete products except the prod_category Electronics. The table function returns the result set as a set of records and uses a weakly typed REFCURSOR as input.
CREATE OR REPLACE FUNCTION obsolete_products(cur cursor_pkg.refcur_t)
Transformation Mechanisms
14-12 Oracle Database Data Warehousing Guide RETURN product_t_table
prod_subcategory_desc VARCHAR2(2000);
prod_category VARCHAR2(50);
objset product_t_table := product_t_table();
i NUMBER := 0;
BEGIN LOOP
-- Fetch from cursor variable
FETCH cur INTO prod_id, prod_name, prod_desc, prod_subcategory,
prod_subcategory_desc, prod_category, prod_category_desc, prod_weight_class, prod_unit_of_measure, prod_pack_size, supplier_id, prod_status,
prod_list_price, prod_min_price;
EXIT WHEN cur%NOTFOUND; -- exit when last row is fetched
-- Category Electronics is not meant to be obsolete and will be suppressed IF prod_status='obsolete' AND prod_category != 'Electronics' THEN
-- append to collection i:=i+1;
objset.extend;
objset(i):=product_t( prod_id, prod_name, prod_desc, prod_subcategory, prod_subcategory_desc, prod_category, prod_category_desc,
prod_weight_class, prod_unit_of_measure, prod_pack_size, supplier_id, prod_status, prod_list_price, prod_min_price);
END IF;
You can use the table function in a SQL statement to show the results. Here we use additional SQL functionality for the output:
SELECT DISTINCT UPPER(prod_category), prod_status FROM TABLE(obsolete_products(
CURSOR(SELECT prod_id, prod_name, prod_desc, prod_subcategory,
prod_subcategory_desc, prod_category, prod_category_desc, prod_weight_class, prod_unit_of_measure, prod_pack_size,
supplier_id, prod_status, prod_list_price, prod_min_price FROM products)));
The following example implements the same filtering than the first one. The main differences between those two are:
■ This example uses a strong typed REFCURSOR as input and can be parallelized based on the objects of the strong typed cursor, as shown in one of the following examples.
Transformation Mechanisms
Loading and Transformation 14-13
■ The table function returns the result set incrementally as soon as records are created.
CREATE OR REPLACE FUNCTION
obsolete_products_pipe(cur cursor_pkg.strong_refcur_t) RETURN product_t_table PIPELINED
PARALLEL_ENABLE (PARTITION cur BY ANY) IS prod_id NUMBER(6);
prod_name VARCHAR2(50);
prod_desc VARCHAR2(4000);
prod_subcategory VARCHAR2(50);
prod_subcategory_desc VARCHAR2(2000);
prod_category VARCHAR2(50);
prod_category_desc VARCHAR2(2000);
prod_weight_class NUMBER(2);
prod_unit_of_measure VARCHAR2(20);
prod_pack_size VARCHAR2(30);
FETCH cur INTO prod_id, prod_name, prod_desc, prod_subcategory, prod_subcategory_desc, prod_category, prod_category_desc,
prod_weight_class, prod_unit_of_measure, prod_pack_size, supplier_id, prod_status, prod_list_price, prod_min_price;
EXIT WHEN cur%NOTFOUND; -- exit when last row is fetched
IF prod_status='obsolete' AND prod_category !='Electronics' THEN PIPE ROW (product_t( prod_id, prod_name, prod_desc, prod_subcategory, prod_subcategory_desc, prod_category, prod_category_desc, prod_weight_class, prod_unit_of_measure, prod_pack_size, supplier_id, prod_status,
prod_list_price, prod_min_price));
You can use the table function as follows:
SELECT DISTINCT prod_category,
DECODE(prod_status,'obsolete','NO LONGER AVAILABLE','N/A') FROM TABLE(obsolete_products_pipe(
CURSOR(SELECT prod_id, prod_name, prod_desc, prod_subcategory, prod_subcategory_desc, prod_category, prod_category_desc, prod_weight_class, prod_unit_of_measure, prod_pack_size, supplier_id, prod_status, prod_list_price, prod_min_price FROM products)));
We now change the degree of parallelism for the input table products and issue the same statement again:
ALTER TABLE products PARALLEL 4;
The session statistics show that the statement has been parallelized:
SELECT * FROM V$PQ_SESSTAT WHERE statistic='Queries Parallelized';
Transformation Mechanisms
14-14 Oracle Database Data Warehousing Guide
STATISTIC LAST_QUERY SESSION_TOTAL --- --- ---Queries Parallelized 1 3 1 row selected.
Table functions are also capable to fanout results into persistent table structures. This is demonstrated in the next example. The function filters returns all obsolete products except a those of a specific prod_category (default Electronics), which was set to status obsolete by error. The result set of the table function consists of all other obsolete product categories. The detected wrong prod_id IDs are stored in a separate table structure obsolete_products_error. Note that if a table function is part of an autonomous transaction, it must COMMIT or ROLLBACK before each PIPE ROW statement to avoid an error in the callings subprogram. Its result set consists of all other obsolete product categories. It furthermore demonstrates how normal variables can be used in conjunction with table functions:
CREATE OR REPLACE FUNCTION obsolete_products_dml(cur cursor_pkg.strong_refcur_t, prod_cat varchar2 DEFAULT 'Electronics') RETURN product_t_table
PIPELINED
PARALLEL_ENABLE (PARTITION cur BY ANY) IS PRAGMA AUTONOMOUS_TRANSACTION;
prod_id NUMBER(6);
prod_name VARCHAR2(50);
prod_desc VARCHAR2(4000);
prod_subcategory VARCHAR2(50);
prod_subcategory_desc VARCHAR2(2000);
prod_category VARCHAR2(50);
FETCH cur INTO prod_id, prod_name, prod_desc, prod_subcategory,
prod_subcategory_desc, prod_category, prod_category_desc, prod_weight_class, prod_unit_of_measure, prod_pack_size, supplier_id, prod_status,
prod_list_price, prod_min_price;
EXIT WHEN cur%NOTFOUND; -- exit when last row is fetched IF prod_status='obsolete' THEN
IF prod_category=prod_cat THEN
PIPE ROW (product_t( prod_id, prod_name, prod_desc, prod_subcategory, prod_subcategory_desc, prod_category, prod_category_desc, prod_weight_class, prod_unit_of_measure, prod_pack_size, supplier_id, prod_status,
prod_list_price, prod_min_price));
END IF;
END IF;
END LOOP;
CLOSE cur;