And there are different ways to do the same. PIPELINED functions come to mind. I am sure others know that one. Here is something that is lesser known (IMO).

SQL> create or replace function fun ( p_in_1  in number
2                                 , p_in_2 out number )
3                                 return number
4  is
5  begin
6    p_in_2 := 2*p_in_1;
7    return 0;
8  end;
9  /
 
Function created.
 
SQL> sho err
No errors.

Note: Created a function with one OUT VARIABLE and ONE RETURN VARIABLE. Lets execute the function.

 
SQL> var x number;
SQL> var y number;
SQL> exec : x :=0;
 
PL/SQL procedure successfully completed.
 
SQL> exec :y :=1;
 
PL/SQL procedure successfully completed.
 
SQL> select : x
2       , :y
3    from dual
4  /
 
: X        :Y
---------- ----------
0          1
 
SQL> exec :y := fun(10, : x);
 
PL/SQL procedure successfully completed.
 
SQL> select : x
2       , :y
3    from dual
4  /
 
: X        :Y
---------- ----------
20          0

when i run
select f from dual;

it results below, why different from example ?

[JOB.THREE_VALUES]

SQL> select x.c.val_1, x.c.val_2, x.c.val_3
  2  from
  3   (select f c from dual ) x;

You can also use a procedure that has more than one out parameter. This is a good solution if it's used in PL/SQL only.

And note that a function that returns an out parameter is considered bad practice and should be avoided.
From the Oracle documentation: "Do not use OUT and IN OUT for function parameters. Ideally, a function takes zero or more parameters and returns a single value. A function with IN OUT parameters returns multiple values and has side effects."

I have problem with method mentioned above. Problem is, that function is called multiple times, once for every column that I reference in select (in your example, it is three times). It is problem if function is not deterministic or is time consuming.
My example of problem:

create or replace
type three_values as object
( val_1 int,
val_2 int,
val_3 int
);
/
--package with one global variable
create package p_x as
l_x int;
end;
/
--non detrministic function
create or replace
function f return three_values is
begin
--every call increment global variable
if p.l_x is null then
p.l_x:=0;
else
p.l_x:=p.l_x+1;
end if;
--return one row with three same values
return three_values(p.l_x, p.l_x, p.l_x);
end;
/
--select return three different values, but my expectations was, that it should return three same values
select x.c.val_1, x.c.val_2, x.c.val_3
from
(select f c from dual ) x;
/

Is there any solution that forces oracle to call function only once, obtain one row and then access items in that one same row?

select x.c.val_1, x.c.val_2, x.c.val_3 
from 
(select ( select f from dual ) c 
 from dual ) x; 

create or replace type three_values as object
( val_1 number,
val_2 number,
val_3 number
);


with table_name as (
select 1 col1 , 'ITEM 1' item_name , 1999 purchase_amount from dual
union
select 2 col1 , 'ITEM 1' item_name, 500 purchase_amount from dual
union
select 3 col1 , 'ITEM 3' item_name, 2000 purchase_amount from dual
union
select 4 col1 , 'ITEM 4' item_name, 2780 purchase_amount from dual
union
select 5 col1 , 'ITEM 5' item_name, 4000 purchase_amount from dual
union
select 6 col1 , 'ITEM 6' item_name, 8000 purchase_amount from dual
)

select * from table_name ;


create or replace function get_final_price(p_item_name varchar2,p_purchase_amount number) return three_values is
v_col_1 number;
v_col_2 number;
v_col_3 number;
basic_rate number;
discount number;
begin

if p_item_name = 'ITEM 1' and p_purchase_amount <= 999 then
discount := 10;
elsif p_item_name = 'ITEM 1' and p_purchase_amount >= 1000 and p_purchase_amount <= 2000 then
discount := 25;
elsif p_item_name = 'ITEM 1' and p_purchase_amount >= 2001 and p_purchase_amount <= 3000 then
discount := 30;
elsif p_item_name = 'ITEM 2' then
discount := 20;
elsif p_item_name = 'ITEM 3' then
discount := 5;
elsif p_item_name = 'ITEM 4' then
discount := 7;
elsif p_item_name = 'ITEM 5' then
discount := 9;
elsif p_item_name = 'ITEM 6' then
discount := 25;
end if;

v_col_2 := ( p_purchase_amount * discount ) / 100;
v_col_1 := p_purchase_amount - v_col_2 ;

v_col_3 := v_col_1 - 100 ; -- give 100 Rs Flat Discount
return three_values(v_col_1, v_col_2, v_col_3);
end;





with table_name as (
select 1 col1 , 'ITEM 1' item_name , 1999 purchase_amount from dual
union
select 2 col1 , 'ITEM 1' item_name, 500 purchase_amount from dual
union
select 3 col1 , 'ITEM 3' item_name, 2000 purchase_amount from dual
union
select 4 col1 , 'ITEM 4' item_name, 2780 purchase_amount from dual
union
select 5 col1 , 'ITEM 5' item_name, 4000 purchase_amount from dual
union
select 6 col1 , 'ITEM 6' item_name, 8000 purchase_amount from dual
)

select t.* , get_final_price(p_item_name => item_name,
p_purchase_amount => purchase_amount) price from table_name t ;




Output is
Col1 ITEMNAME PURCHASE_AMOUNT PRICE
1 ITEM 1 1999 (1499.25, 499.75, 1399.25)
2 ITEM 1 500 (450, 50, 350)
3 ITEM 3 2000 (1900, 100, 1800)
4 ITEM 4 2780 (2585.4, 194.6, 2485.4)
5 ITEM 5 4000 (3640, 360, 3540)
6 ITEM 6 8000 (6000, 2000, 5900)


But I want to this

COL1 ITEM_NAME PURCHASE_AMOUNT PRICE.VAL_1 PRICE.VAL_2 PRICE.VAL_3
1 1 ITEM 1 1999 1499.25 499.75 1399.25
2 2 ITEM 1 500 450 50 350
3 3 ITEM 3 2000 1900 100 1800
4 4 ITEM 4 2780 2585.4 194.6 2485.4
5 5 ITEM 5 4000 3640 360 3540
6 6 ITEM 6 8000 6000 2000 5900

select t.* , 
     get_final_price(
       p_item_name => item_name,
       p_purchase_amount => purchase_amount) price 
  from table_name t ;

select col1 ,  item_name , purchase_amount,   -- cols from t
      val_1, val_2, val_3   -- cols from object
from
(
select t.* , 
     get_final_price(
       p_item_name => item_name,
       p_purchase_amount => purchase_amount) price 
  from table_name t 
)

(select f c from dual ) x;

what is this 'c' here ?
looks like c is instance of the function f ?

sorry for the question so naive.

In response to thread above "use with select another table"

version: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

following SQL

select col1 ,  item_name , purchase_amount,   -- cols from t
      val_1, val_2, val_3   -- cols from object
from
(
select t.* , 
     get_final_price(
       p_item_name => item_name,
       p_purchase_amount => purchase_amount) price 
  from table_name t 
)

returns

ORA-00904: "VAL_3": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:

Even original question (Asked by A reader) results differ

for e.g. for sql

with table_name as (
select 1 col1 , 'ITEM 1' item_name , 1999 purchase_amount from dual
union
select 2 col1 , 'ITEM 1' item_name, 500 purchase_amount from dual
union
select 3 col1 , 'ITEM 3' item_name, 2000 purchase_amount from dual
union
select 4 col1 , 'ITEM 4' item_name, 2780 purchase_amount from dual
union
select 5 col1 , 'ITEM 5' item_name, 4000 purchase_amount from dual
union
select 6 col1 , 'ITEM 6' item_name, 8000 purchase_amount from dual
)

select t.* , get_final_price(p_item_name => item_name,
p_purchase_amount => purchase_amount) price from table_name t ;

it returns

1 ITEM 1 1999 [ETL_RPT.THREE_VALUES]
2 ITEM 1 500 [ETL_RPT.THREE_VALUES]
3 ITEM 3 2000 [ETL_RPT.THREE_VALUES]
4 ITEM 4 2780 [ETL_RPT.THREE_VALUES]
5 ITEM 5 4000 [ETL_RPT.THREE_VALUES]
6 ITEM 6 8000 [ETL_RPT.THREE_VALUES]

I am trying to follow "A reader" e.g. as its without any change.

I set up a function like you have in this example, but I pass in an id to determine the three values returned by doing a lookup on several different tables.

I can get the call:

select x.c.val_1, x.c.val_2, x.c.val_3
from (select f(id) c from dual ) x;

To return the three values - but I have a SQL query that goes through several tables to pull other information. If the function returned just one value this would work:

Select t1.id, t1.name, t2.date, t3.order_id, f(t3.order_id)
From table_customer t1
join table_interactions t2 on t1.id = t2.id
join table_orders t3 on t1.id = t3.id
;

Because the function returns three values -the above select doesn't work. How do I get the three values returned in the above select?

Thank you.

Select t1.id, t1.name, t2.date, t3.order_id, f(t3.order_id)
From table_customer t1
join table_interactions t2 on t1.id = t2.id
join table_orders t3 on t1.id = t3.id

select id, name, order_id, result.val_1, result,val_2, result.val_3
from
(
Select t1.id, t1.name, t2.date, t3.order_id, f(t3.order_id) result
From table_customer t1
join table_interactions t2 on t1.id = t2.id
join table_orders t3 on t1.id = t3.id
)

This is correct query:

select x.col1 , x.item_name , x.purchase_amount, -- cols from t
x.price.val_1, x.price.val_2, x.price.val_3 -- cols from object
from
(
select c.* ,
get_final_price(
p_item_name => item_name,
p_purchase_amount => purchase_amount) price
from table_name c
) x