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!

If the size of the tuple to be returned by the function is "known and set in stone," then why not write the query to include the three columns? — Kirby, Aug 09 '17 at 19:54
@Kirby the columns returned by the function are from other tables. A join may work but it would be a lengthy and elaborate join that would need to be used in many different queries so I don't want to have to keep re-writing that join. So then I say, well make a view and have all the queries be based off the view, then I won't have to keep re-writing that join. That may work, but currently the code is in a function and it is elaborate, I'm not sure if it all can be done in SQL. Going through it all now. — gfrobenius, Aug 09 '17 at 20:14

Matthew McPeak · Answer 1 · 2017-08-10T14:41:36.013

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)

You can only keep the full answer part as answer as the earlier section was bit confusing when i started looking at from beginning. — XING, Aug 10 '17 at 07:31
You are right, @XING. I've reorganized it for (hopefully) better clarity. Thank you. — Matthew McPeak, Aug 10 '17 at 14:42

score 0 · Answer 2 · answered Aug 10 '17 at 03:28

A very simplied illustration. Hope it helps.

--The best way here is to make a table type function
--as mentioned below


--Create object type
CREATE OR REPLACE TYPE OBJ_DUM
IS
  OBJECT
  (
    COL1 NUMBER,
    COL2 NUMBER,
    COL3 VARCHAR2(100) );
    /

--Create table type    
CREATE OR REPLACE TYPE TAB_DUM
IS
  TABLE OF OBJ_DUM;
  /


--Create dummy function. This canbe a pipelined function also 
CREATE OR REPLACE
FUNCTION SO_DUM
  RETURN TAB_DUM
AS
  lv_tab tab_dum:=tab_dum(NULL,NULL,NULL);
BEGIN
  SELECT obj_dum(level,level+1,'AVRAJIT+'
    ||LEVEL) BULK COLLECT
  INTO lv_tab
  FROM DUAL
    CONNECT BY LEVEL < 2;
  RETURN lv_tab;
END;
/


--To check the output
SELECT TAB.*,OBJ.* FROM USER_OBJECTS OBJ,TABLE(SO_DUM) TAB;

PL/SQL Add Multiple Columns to Query with Single Function

2 Answers2

12c

11g