PL/SQL Add Multiple Columns to Query with Single Function

Question

Is there any way to accomplish something like this in PL/SQL...

select a.col1, a.col2, a.col3, myFunc(a.id) from myTable a;

and the result be more than 4 columns? So basically, is there a way for a function to return or pipe more than one column back? The number needed is known and set in stone, it's 3. So this query would return 6 columns. I know I could call the myFunc() 3 separate times but the amount of processing would be tripled.

I've been playing around with pipeline functions but it doesn't appear they can be used to do this.

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit

Production PL/SQL Release 11.2.0.2.0 - Production

Thanks!

Answer 1

Here are ways to do it in various Oracle versions. I use DBA_OBJECTS only as a substitute for your real table.

CREATE OR REPLACE TYPE my_func_rec IS OBJECT
(
  mf_col1 NUMBER,
  mf_col2 NUMBER,
  mf_col3 NUMBER
);

CREATE OR REPLACE TYPE my_func_tab IS TABLE OF my_func_rec;

CREATE OR REPLACE FUNCTION my_func (id NUMBER)
  RETURN my_func_tab IS
  l_result   my_func_tab;
BEGIN
  SELECT my_func_rec (id + 100, id + 101, id + 102)
  BULK   COLLECT INTO l_result
  FROM   DUAL;

  RETURN l_result;
END my_func;

12c

In 12c, it's pretty simple using CROSS APPLY.

SELECT object_id,
       object_type,
       status,
       mf_col1,
       mf_col2,
       mf_col3
FROM   dba_objects o
       CROSS APPLY (SELECT mf_col1,
                           mf_col2,
                           mf_col3
                    FROM   TABLE (my_func (o.object_id)) odet);

11g

In 11g, you do not have access to CROSS APPLY so you need to select the function results as an object and then TREAT it as an object to get access to the individual fields.

SELECT object_id,
       object_type,
       status,
       TREAT (val AS my_func_rec).mf_col1,
       TREAT (val AS my_func_rec).mf_col2,
       TREAT (val AS my_func_rec).mf_col3
FROM   (SELECT object_id,
               object_type,
               status,
               (SELECT my_func_rec (mf_col1, mf_col2, mf_col3)
                FROM   TABLE (my_func (o.object_id)) mf)
                 val
        FROM   dba_objects o)  

NOTE: I created the 11g answer after the 12c answer. The 11g answer can be further simplified by having my_func return a my_func_rec instead of a my_func_tab. In this case, it would simplify to:

SELECT object_id,
       object_type,
       status,
       TREAT (val AS my_func_rec).mf_col1,
       TREAT (val AS my_func_rec).mf_col2,
       TREAT (val AS my_func_rec).mf_col3
FROM   (SELECT object_id,
               object_type,
               status,
               my_func (o.object_id) val
        FROM   dba_objects o)