Pipelined table functions and PL/SQL Tables

The updated version of this entry with copiable code is here.

Pipelined table functions let you retrieve record structures from PL/SQL Tables as rows in SQL statements. PL/SQL tables are also known as associative arrays from 10g forward. While pipelined table functions are slower than other approaches, they are easy to implement. The alternative uses a complex query. The query is complex because it typically uses selective aggregation and nested CASE logic.

The following package creates a PL/SQL record structure, table, and weakly typed reference cursor. They’re provided to support the examples in the blog.

CREATE OR REPLACE PACKAGE utility_type IS
TYPE common_lookup_record IS RECORD
( common_lookup_id NUMBER
, common_lookup_type VARCHAR2(30)
, common_lookup_meaning VARCHAR2(255));
TYPE common_lookup_table IS TABLE OF common_lookup_record;
END utility_type;
/

This sample is trivial. The query on its own is preferable to all the overhead but it makes demonstrating a pipelined table function straightforward.

CREATE OR REPLACE FUNCTION get_common_lookup
( table_name VARCHAR2, column_name VARCHAR2 )
RETURN utility_type.common_lookup_table
PIPELINED IS
CURSOR c (table_name_in VARCHAR2, table_column_name_in VARCHAR2) IS
SELECT common_lookup_id, common_lookup_type, common_lookup_meaning
FROM common_lookup
WHERE common_lookup_table = UPPER(table_name_in)
AND common_lookup_column = UPPER(table_column_name_in);
counter NUMBER := 1;
list UTILITY_TYPE.COMMON_LOOKUP_TABLE := utility_type.common_lookup_table();
BEGIN
FOR i IN c(table_name, column_name) LOOP
list.EXTEND;
list(counter) := i;
PIPE ROW(list(counter));
counter := counter + 1;
END LOOP;
RETURN;
END get_common_lookup;
/

This returns an aggregate table, which can be read by using the TABLE function. Reading this type of structure differs from how you use the TABLE function to read a scalar array of variables. You don’t use the COLUMN_VALUE argument, which is limited to reading a scalar array list. The pipelined table lets you read the columns directly with the TABLE function, as shown:

COLUMN common_lookup_id FORMAT 999999
COLUMN common_lookup_type FORMAT A20
COLUMN common_lookup_meaning FORMAT A30

SELECT l.common_lookup_id, l.common_lookup_type, l.common_lookup_meaning
FROM TABLE(get_common_lookup('ITEM','ITEM_TYPE')) l;

The pipelined table function is the only way to read a table of record structures in a SQL statement. Pipelined table functions are also limited to SQL scope only. That’s because their real benefit lies in queries that are parallelized. Any attempt to read the aggregate table from a pipelined table function in another PL/SQL block raises a PLS-00653 error. While you can use a weakly typed cursor to mimic the same behavior through OCI8 in PHP, you can’t do so in SQL statements.

This function will work when called from a PHP program through OCI8, but won’t work in SQL:

CREATE OR REPLACE FUNCTION get_common_cursor
( table_name VARCHAR2, column_name VARCHAR2 ) RETURN SYS_REFCURSOR IS
lookups SYS_REFCURSOR;
BEGIN
OPEN lookups FOR
SELECT common_lookup_id, common_lookup_type, common_lookup_meaning
FROM common_lookup
WHERE common_lookup_table = UPPER(table_name)
AND common_lookup_column = UPPER(column_name);
RETURN lookups;
END get_common_cursor;
/

An attempt to read the get_common_cursor by the TABLE function yields an ORA-22905 error. The error says you can’t access rows from a non-nested table. You can read the cursor in SQL but it disallows using the result in another SQL statement. This queries the cursor, which then dumps the contents as a set of subsequent results:

SELECT get_common_cursor('ITEM','ITEM_TYPE') FROM dual;

If you’re using returning the cursor because you plan to use it through the OCI8, then you can wrap the function call in a pipelined table function for when you’ll use it in SQL. The following converts the system reference cursor to an aggregate table:

CREATE OR REPLACE FUNCTION get_common_lookup
( c SYS_REFCURSOR ) RETURN utility_type.common_lookup_table
PIPELINED IS
counter NUMBER := 1;
record UTILITY_TYPE.COMMON_LOOKUP_RECORD;
list UTILITY_TYPE.COMMON_LOOKUP_TABLE := utility_type.common_lookup_table();
BEGIN
LOOP
FETCH c INTO record;
EXIT WHEN c%NOTFOUND;
list.EXTEND;
list(counter) := record;
PIPE ROW(list(counter));
counter := counter + 1;
END LOOP;
RETURN;
END get_common_lookup;
/