CODEWITHSUNDEEP
sqloracle-databaseplsql
gfrobenius
gfrobenius

Reputation: 4067

PL/SQL Add Multiple Columns to Query with Single Function

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!

Upvotes: 4

Views: 1600

Answers (2)

Matthew McPeak
Matthew McPeak

Reputation: 17944

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)

Upvotes: 3

Avrajit Roy
Avrajit Roy

Reputation: 3303

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;

Upvotes: 0

Related Questions