CREATE OR REPLACE TYPE AFESD2."AMOUNT2"  AS OBJECT
(
  "AMT1" NUMBER(15,3),
  "AMT2" NUMBER(15,3)
);

CREATE OR REPLACE FUNCTION F_try(Req_No in number, exch_rate_date in char default 'TODAY')   
Return Amount2 As  
Trans_Amts Amount2;
   
Amt1 Number;    
Amt2 Number; 
STAT NUMBER;   

 begin

SELECT 5,6
  INTO AMT1,
       AMT2
  FROM DUAL;
	Trans_Amts := AMOUNT2(AMT1,AMT2);
	RETURN (Trans_Amts);

END;
/

select 1, f_try(3) from dual;

orclz> select 1, f_try(3).amt1,f_try(3).amt2 from dual;

         1 F_TRY(3).AMT1 F_TRY(3).AMT2
---------- ------------- -------------
         1             5             6

orclz>

CREATE OR REPLACE FUNCTION F_try(Req_No in number, exch_rate_date in char default 'TODAY')   
Return Amount2 As  
Trans_Amts Amount2;
   
Amt1 Number;    
Amt2 Number; 
STAT NUMBER;   

 begin

SELECT 5,6
  INTO AMT1,
       AMT2
  FROM DUAL;

DBMS_OUTPUT.PUT_LINE('One run');

	Trans_Amts := AMOUNT2(AMT1,AMT2);
	RETURN (Trans_Amts);

END;

select 1, f_try(3).AMT1, F_TRY(3).AMT2, f_try(3).AMT1 from dual;

One run
One run
One run

select t.id,
       t.val.AMT1,
       t.val.AMT2
from (select 1 id, 
             f_try(3) val 
      from dual) t;

orclz> select t.id,
  2         t.val.AMT1,
  3         t.val.AMT2
  4  from (select 1 id,
  5               f_try(3) val
  6        from dual) t;

        ID   VAL.AMT1   VAL.AMT2
---------- ---------- ----------
         1          5          6

One run
One run
orclz>

orclz> ed
Wrote file afiedt.buf

  1  CREATE OR REPLACE FUNCTION F_try(Req_No in number, exch_rate_date in char default 'TODAY')
  2  Return Amount2
  3  deterministic
  4  As
  5  Trans_Amts Amount2;
  6  Amt1 Number;
  7  Amt2 Number;
  8  STAT NUMBER;
  9   begin
 10  SELECT 5,6
 11    INTO AMT1,
 12         AMT2
 13    FROM DUAL;
 14  DBMS_OUTPUT.PUT_LINE('One run');
 15     Trans_Amts := AMOUNT2(AMT1,AMT2);
 16     RETURN (Trans_Amts);
 17* END;
orclz> /

Function created.

select t.id,
       t.val.AMT1,
       t.val.AMT2,
       t.val.AMT1 AMT1_2,
       t.val.AMT2 AMT2_2
from (select 1 id, 
             (select f_try(3) from dual) val 
      from dual) t;

orclz>
orclz> select t.id,
  2         t.val.AMT1,
  3         t.val.AMT2,
  4         t.val.AMT1 AMT1_2,
  5         t.val.AMT2 AMT2_2
  6  from (select 1 id,
  7               (select f_try(3) from dual) val
  8        from dual) t;

        ID   VAL.AMT1   VAL.AMT2     AMT1_2     AMT2_2
---------- ---------- ---------- ---------- ----------
         1          5          6          5          6

One run
orclz>

SQL> CREATE OR REPLACE TYPE AMOUNT2  AS OBJECT
  2  (
  3    "AMT1" NUMBER(15,3),
  4    "AMT2" NUMBER(15,3)
  5  )
  6  /

Type created.

SQL> CREATE OR REPLACE
  2    TYPE AMOUNT2_TBL
  3      AS TABLE OF AMOUNT2
  4  /

Type created.

SQL> CREATE OR REPLACE FUNCTION F_try(Req_No in number, exch_rate_date in char default 'TODAY')   
  2  Return Amount2 As  
  3  Trans_Amts Amount2;
  4     
  5  Amt1 Number;    
  6  Amt2 Number; 
  7  STAT NUMBER;   
  8  
  9   begin
 10  
 11  SELECT 5,6
 12    INTO AMT1,
 13         AMT2
 14    FROM DUAL;
 15   Trans_Amts := AMOUNT2(AMT1,AMT2);
 16  DBMS_OUTPUT.PUT_LINE('Fire!');
 17   RETURN (Trans_Amts);
 18  
 19  END;
 20  /

Function created.

SQL> SELECT  1,
  2          p.*
  3    FROM  TABLE(AMOUNT2_TBL(f_try(3))) p
  4  /

         1       AMT1       AMT2
---------- ---------- ----------
         1          5          6

Fire!
SQL> 

Errors for FUNCTION F_TRY:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      PL/SQL: Compilation unit analysis terminated
2/9      PLS-00999: implementation restriction (may be temporary)
         RESULT_CACHE is disallowed on subprograms with RETURN parameter
         of (or containing) object type

orclz>

--test table
create table t_try1 (v1 number, v2 number, v3 number);

INSERT ALL
  INTO t_try1 VALUES (1, 15, 67)
  INTO t_try1 VALUES (2, 16, 3)
  INTO t_try1 VALUES (11, 85, 49)
  INTO t_try1 VALUES (22, 56, 13)
  INTO t_try1 VALUES (13, 25, 7)
  INTO t_try1 VALUES (42, 156, 31)
  INTO t_try1 VALUES (51, 35, 47)
  INTO t_try1 VALUES (2, 656, 43)
  INTO t_try1 VALUES (10, 95, 647)
  INTO t_try1 VALUES (20, 596, 443)
SELECT * FROM DUAL;


--function approach
 CREATE OR REPLACE FUNCTION F_try(Req_No in number, exch_rate_date in char default 'TODAY')   
    Return Amount2 As  
    Trans_Amts Amount2;
       
    Amt1 Number;    
    Amt2 Number; 
    STAT NUMBER;   
   
    begin
   
   SELECT V2 * 6,V1 *8
     INTO AMT1,
          AMT2
     FROM T_TRY1 
    where V3 = Req_No ; --the where clause
    Trans_Amts := AMOUNT2(AMT1,AMT2);
   DBMS_OUTPUT.PUT_LINE('Fire!');
    RETURN (Trans_Amts);
   
   END;


 SELECT  1,
           p.*
      FROM  TABLE(AMOUNT2_TBL(f_try(49))) p;



create or replace  procedure p_try
  ( param1 in number, CUR_REF     OUT SYS_REFCURSOR) as
  begin
  OPEN CUR_REF FOR SELECT 1, 
                          p.amt1 zz, p.*
  FROM TABLE (AMOUNT2_TBL(f_try(param1))) p;
 END;

--view approach
create or replace view v_try
  as
  (
      SELECT V2 * 6 vv2,V1 *8 vv1, V3
     FROM T_TRY1 
  );


create or replace  procedure p_try2
  ( param1 in number, CUR_REF     OUT SYS_REFCURSOR) as
  begin
  OPEN CUR_REF FOR SELECT 1, 
                          p.vv1 zz, p.vv1, p.vv2
  FROM v_try p
  where v3 = param1; --where clause 
 END;