Pipelined functions and PL/SQL Tables

Pipelined 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 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 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 function is the only way to read a table of record structures in a SQL statement. Pipelined functions are also limited to SQL scope only. Any attempt to read the aggregate table from a pipelined 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 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;
/