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 · Accepted Answer · 2018-04-11 09:38:36Z

3

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 |

edited Apr 11, 2018 at 9:38

answered Apr 11, 2018 at 9:35

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

MT0 Over a year ago

@Nina - updated. You need to use a table name/alias when you reference the object attributes in the query.

Nina Over a year ago

Currently, you cannot define object types in a PL/SQL block, subprogram, or package.

Kaushik Nayak Over a year ago

1+ for "The Meaning of Life, The Universe and Everything"

MT0 Over a year ago

@Nina You need to define the object type in the SQL scope (not in PL/SQL) if they are going to be used in SQL queries (in Oracle 11g and below - Oracle 12.something supports PL/SQL declared types in SQL).

Boneist Over a year ago

@Nina why do you "need" the object type to be within the package? By creating the type at the schema level (i.e. create or replace object ...) you lose nothing and gain everything.

|

Collectives™ on Stack Overflow

Oracle: split function result into multiple columns

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related