Oracle: split function result into multiple columns

Question

I have a package in oracle. In the package i have a procedure which performs an (insert into ..select.. ) statement

which is like this:

insert into some_table(col1 , col2 , col3, col4)

select col1 , col2, my_func(col3) as new_col3 , col4

from some_other_table

my_func(col3) does some logic to return a value.

now i need to to return two values instead of one, using the same logic.

i can simply write another function to do the same logic and return the second value, but that would be expensive because the function selects from a large history table.

i can't do a join with the history table because the function doesn't perform a simple select.

is there a way to get two columns by calling this function only once?

MT0 MT0 · Accepted Answer · 2018-04-11T09:35:17

Create an OBJECT type with two attributes and return that from your function. Something like:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TYPE my_func_type IS OBJECT(
  value1 NUMBER,
  value2 VARCHAR2(4000)
);
/

CREATE FUNCTION my_func
RETURN my_func_type
IS
  value my_func_type;
BEGIN
  value := my_func_type( 42, 'The Meaning of Life, The Universe and Everything' );
  RETURN value;
END;
/

CREATE TABLE table1 (col1, col2, col5 ) AS
  SELECT 1, 2, 5 FROM DUAL
/

Query 1:

SELECT col1,
       col2,
       t.my_func_value.value1 AS col3,
       t.my_func_value.value2 AS col4,
       col5
FROM   (
  SELECT col1,
         col2,
         my_func() AS my_func_value,
         col5
  FROM   table1
) t

Results:

| COL1 | COL2 | COL3 |                                             COL4 | COL5 |
|------|------|------|--------------------------------------------------|------|
|    1 |    2 |   42 | The Meaning of Life, The Universe and Everything |    5 |

Oracle: split function result into multiple columns

1 Answers