Including Single Row Multiple Column Subquery (PIPELINED Function) Results in the Result Set

In this article, we will explore how to include the results of a pipelined function in a SQL query that returns multiple columns. The pipelined function allows us to execute a PL/SQL block as a subquery, but it has limitations when it comes to joining with other tables.

Introduction to Pipelined Functions

A pipelined function is a type of stored procedure that returns a table-like result set. It is similar to a regular stored procedure, but instead of returning a single value or a collection of values, it returns a full table row for each row executed by the procedure. This allows us to use pipelined functions in queries that require multiple rows and columns.

The pipelined function we are working with, MY_PACK.runPipe, takes an ID parameter and returns a record containing three columns: surname, telephone, and place of birth.

The Challenge

We have two tables: test_data and the result set returned by MY_PACK.runPipe. The test_data table has one row per each ID value in the range 1 to 3. We want to join these two tables on the ID column and return all rows from test_data along with the corresponding values from the pipelined function.

The Issue with JOIN

Unfortunately, we cannot simply use a regular JOIN with MY_PACK.runPipe because it is a pipelined function that returns multiple columns per row. When we try to join it with test_data, Oracle treats the pipelined function as if it were returning only one row, which means the JOIN condition will always be false.

The Solution

To solve this problem, we can use a LEFT JOIN with table(MY_PACK.runPipe(td.id)). This allows us to match each row in test_data with any row returned by the pipelined function. If no matching rows are found (i.e., the ID value is not present in the pipelined function’s result set), the resulting row will be NULL.

The Correct Query

Here is the correct query:

with test_data as (
    select 1 as id, 'John' as name from dual 
        union all
    select 2 as id, 'Jack' as name from dual
        union all
    select 3 as id, 'Peter' as name from dual
)
select td.*, s.*
from test_data td
LEFT JOIN  table(MY_PACK.runPipe(td.id)) s ON 1 = 1;

How It Works

Here’s what happens when we execute this query:

1. The Common Table Expression (CTE) test_data is created, which selects the three ID values and corresponding names.
2. The main query joins each row in test_data with any row returned by table(MY_PACK.runPipe(td.id)).
3. For each row in test_data, Oracle checks if there is a matching row in the pipelined function’s result set (i.e., if the ID value is present).
4. If a match is found, Oracle returns all columns from both tables for that particular row.
5. If no match is found, Oracle returns NULL values for all columns.

The Expected Results

When we execute this query, we get the following results:

1 John  Smih    139289289   Paris
2 Jack  Lock    1888888888  London
3 Peter May     99999999999 Berlin

As you can see, all rows from test_data are returned along with the corresponding values from the pipelined function.

Conclusion

In this article, we explored how to include the results of a pipelined function in a SQL query that returns multiple columns. We discussed the challenges and limitations of using pipelined functions and provided a solution using a LEFT JOIN. By understanding how pipelined functions work and how to use them effectively with joins, you can write more powerful and efficient queries in Oracle.

Last modified on 2023-11-19