Joining to a pipelined table function and “left correlation”

Oracle 11.2.0.4

A pipelined table function may be called from regular SQL using the TABLE collection expression, e.g.

SELECT *
FROM   TABLE(my_pipelined_function('ABC','DEF'));

where ‘ABC’ and ‘DEF’ are the inputs to the function.

What if you want to call the function repeatedly for several sets of inputs, e.g. testing the function for a variety of values? If those inputs are stored in a table somewhere, it ought to be as easy as doing a cross join to the table.

SELECT *
FROM   TABLE(my_pipelined_function(a,b))
CROSS JOIN my_input_table;

However, this returns the error ORA-00904: invalid identifier. It’s as though the SQL engine is unable to resolve the relationship between the inputs to the function and the joining table.

Today I stumbled across the answer, buried deep in the Oracle 11g SQL Language Reference.

The collection_expression can reference columns of tables defined to its left in the FROM clause. This is called left correlation.

So it seems that by reversing the order of the join so that the joining table comes before the function (i.e. to the left of it in SQL parlance), the SQL engine is able to figure out the join.

SELECT *
FROM   my_input_table
CROSS JOIN TABLE(my_pipelined_function(a,b));

Who ever knew that join order mattered?

Worked example

-- Simple pipelined function to return a couple of records per input
SQL> CREATE OR REPLACE FUNCTION my_pipelined_function
  2  ( a VARCHAR2
  3  , b VARCHAR2
  4  )
  5  RETURN SYS.DBMS_DEBUG_VC2COLL PIPELINED
  6  IS
  7  BEGIN
  8    PIPE ROW(UPPER(a)||LOWER(b));
  9    PIPE ROW(LOWER(a)||UPPER(b));
 10    RETURN;
 11  END;
 12  /

-- Single call to the function
SQL> SELECT *
  2  FROM   TABLE(my_pipelined_function('ABC','DEF'));

COLUMN_VALUE
---------------
ABCdef
abcDEF

-- Join to an input rowset
SQL> WITH src AS (
  2    SELECT 'ABC' AS a, 'DEF' AS b FROM dual
  3    UNION ALL
  4    SELECT 'GHI' AS a, 'JKL' AS b FROM dual
  5    UNION ALL
  6    SELECT 'MNO' AS a, 'PQR' AS b FROM dual
  7  )
  8  SELECT *
  9  FROM   TABLE(my_pipelined_function(a,b))
 10  CROSS JOIN src;
CROSS JOIN src
             *
ERROR at line 10:
ORA-00904: "A": invalid identifier

-- Reverse the order of join
SQL> WITH src AS (
  2    SELECT 'ABC' AS a, 'DEF' AS b FROM dual
  3    UNION ALL
  4    SELECT 'GHI' AS a, 'JKL' AS b FROM dual
  5    UNION ALL
  6    SELECT 'MNO' AS a, 'PQR' AS b FROM dual
  7  )
  8  SELECT *
  9  FROM   src
 10  CROSS JOIN TABLE(my_pipelined_function(a,b));

A   B   COLUMN_VALUE
--- --- ---------------
ABC DEF ABCdef
ABC DEF abcDEF
GHI JKL GHIjkl
GHI JKL ghiJKL
MNO PQR MNOpqr
MNO PQR mnoPQR

References

http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_10002.htm#sthref6754

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s