Combining FORALL with BULK COLLECT

First create a few test tables along with some sample data;

create or replace type varchar2_ntt as table of varchar2(4000);
/

create or replace type number_ntt as table of number;
/

create type ty_Deal as object (Deal_ID number
                              ,Deal_Type varchar2(10)
                              ,Currency varchar2(10)
                              ,Convert_Rate   number
                              ,Converted_Amnt number);
/

create type tb_Deal as table of ty_Deal;
/

create table Trade_Deal
(Deal_ID      number          not null
,Deal_Type    varchar2(10)
,Deal_version integer
,Created      timestamp default current_timestamp
,Trade_Risk   integer check (Trade_Risk in (1,2,3,4,5,6,7,8,9))
,GBP_Amnt     number          not null
,USD_Amnt     number);

create table FX_Rate
(FX_Rate_ID   number        not null
,FX_Rate_Date timestamp     not null
,Deal_Type    varchar2(10)  not null
,FX_From_Cy   varchar2(3)   not null
,FX_To_Cy     varchar2(3)   not null
,FX_Rate      number        not null
);

insert all
 into Trade_Deal values(1,'SPOT',   1,current_timestamp,1,10,null)
 into Trade_Deal values(2,'FORWARD',1,current_timestamp,1,12,null)
  into Trade_Deal values(3,'DEPOSIT',1,current_timestamp,1,11,null)
  into Trade_Deal values(4,'SPOT',   1,current_timestamp,1,22,null)
select 1
from dual
/

insert all
 into FX_Rate values (1,current_timestamp,'SPOT','GBP','USD',1.5757)
 into FX_Rate values (2,current_timestamp,'SPOT','USD','GBP',0.6336)
select 1
from dual

commit;


The goal of the procedure is to update the USD_AMNT colunm
on the TRADE_DEAL table based on the latest foreign exchange
rate available in the FX_RATE table for the given DEAL_TYPE.

The output will be a collection of updated Deals, which could
then be passed further onto a separate PL/SQL procedure or Java
class as a parameter.

The benefit of using BULK COLLECT with
a FORALL is that other PL/SQL procedures or Java classes do not have
to query the database again to identify which deals were updated since
they're all in the output collection that was populated as a result
of the FORALL UPDATE. 
The output collection could be passed on as a parameter.

Create a procedure to illustrate the  point;

create or replace procedure Update_To_Currency
  (
   p_Deal_Type  in Trade_Deal.Deal_Type%type
  ,p_To_Cy      in FX_Rate.FX_To_Cy%type
  ,p_From_Cy    in FX_Rate.FX_From_Cy%type
  ,p_Latest     in timestamp
  ,p_tb_Deal    out tb_Deal
  ) as

  tb_Spot         tb_Deal := tb_Deal(); -- input  collection
  tb_Spot_Updated tb_Deal := tb_Deal(); -- output collection

  l_To_Cy         FX_Rate.FX_To_Cy%type     := p_To_Cy;
  l_From_Cy       FX_Rate.FX_To_Cy%type     := p_From_Cy;
  l_Deal_Type     Trade_Deal.Deal_Type%type := p_Deal_Type;
  l_Latest        timestamp                 := p_Latest;

begin

  select  ty_Deal(td.Deal_ID,td.Deal_Type,l_To_Cy,fr.FX_Rate,0)
  bulk    collect into tb_Spot
  from    Trade_Deal td
  inner join FX_Rate fr on td.Deal_Type     = fr.Deal_Type
                        and fr.FX_From_Cy   = l_From_Cy
                        and fr.FX_Rate_Date <= l_Latest
  where   td.Deal_Type = l_Deal_Type;

  -- The following FORALL is combined with a BULK COLLECT
  forall i in 1..tb_Spot.count
     update Trade_Deal td
     set    td.USD_Amnt      =  td.GBP_Amnt*tb_Spot(i).Convert_Rate
     where  td.Deal_Type     =  tb_Spot(i).Deal_Type
     and    td.Deal_ID       =  tb_Spot(i).Deal_ID
     returning ty_Deal(td.Deal_ID
                      ,td.Deal_Type
                      ,l_To_Cy
                      ,tb_Spot(i).Convert_Rate
                      ,td.USD_Amnt)
     bulk collect into tb_Spot_Updated;

  p_tb_Deal := tb_Spot_Updated;
end Update_To_Currency;
/

Now for a test;

SQL> set serveroutput on
SQL> declare
  2    tb_Deals_Updated tb_Deal := tb_Deal();
  3  begin
  4
  5    Update_To_Currency ('SPOT'
  6                       ,'USD'
  7                       ,'GBP'
  8                       ,current_timestamp
  9                       ,tb_Deals_Updated);
 10
 11    -- Verify that the Collection contains only updated Deals
 12
 13    for i in 1..tb_Deals_Updated.count loop
 14      dbms_output.put_line(tb_Deals_Updated(i).Deal_ID
 15                 ||' '|| tb_Deals_Updated(i).Deal_Type
 16                 ||' '|| tb_Deals_Updated(i).Currency
 17                 ||' '|| tb_Deals_Updated(i).Convert_Rate
 18                 ||' '|| tb_Deals_Updated(i).Converted_Amnt);
 19    end loop;
 20
 21    --call other procs that take tb_Deal as a parameter type
 22    -- and pass tb_Deals_Updated as a parameter
 23
 24  end;
 25  /
1 SPOT USD 1.5757 15.757
4 SPOT USD 1.5757 34.6654

PL/SQL procedure successfully completed.

Finally, verify the TRADE_DEAL table was updated as well.

SQL> select deal_id,deal_type,usd_amnt
  2  from Trade_Deal
  3  where Deal_type = 'SPOT';

   DEAL_ID DEAL_TYPE    USD_AMNT
---------- ---------- ----------
         1 SPOT           15.757
         4 SPOT          34.6654