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

OUTER Join combined with INSERT ALL

The following is an example of an INSERT ALL. The requirement is that rows that exists in table A be inserted into table B, but only if they exist in the MASTER table. If not, then insert an error message into the LOG_MSG table.

create table a (
col1 number
,col2 varchar2(3)
,col3 varchar2(3)
)
/

create table b (
col1 number
,col2 varchar2(3)
,col3 varchar2(3)
)
/

create table master (
col1 varchar2(3)
,col2 number
)
/

create table log_msg
(msg varchar2(100)
,t timestamp default current_timestamp)
/


insert into a (col1,col2,col3)
  select 1 col1, 'AAA' col2,  'P01' col3 from dual union all
  select 2, 'BBB', 'Q01' from dual union all
  select 3, 'CCC', 'S05' from dual union all
  select 4, 'DDD', 'A02' from dual union all
  select 5, 'EEE', 'B01' from dual union all
  select 6, 'FFF', 'B03' from dual
/

insert into b (col1,col2,col3)
select 1 col1, 'AAA' col2,  'P01' col3 from dual union all
  select 2, 'BBB', 'Q01' from dual union all
  select 3, 'CCC', 'S05' from dual union all
  select 5, 'EEE', 'B01' from dual
/


insert into master (col1,col2)
select 'P01', 100 from dual union all
select 'P02', 400 from dual union all
select 'Q01', 900 from dual union all
select 'Q02', 100 from dual union all
select 'S01', 800 from dual union all
select 'S05', 500 from dual union all
select 'B01', 200 from dual union all
select 'B03', 900 from dual
/

insert all
  when Master_ID is not null then
    into b (col1, col2, col3) values (col1, col2, col3)
  when Master_ID is null then
    into log_msg (msg) values (col1 || ' ' || col2 || ' ' || col3 || ' does not exist on Master')
select m.col1 as Master_ID
      ,a.col1 as Col1
      ,a.col2 as Col2
      ,a.col3 as Col3
from (
      select a.col1, a.col2, a.col3
      from a
      left outer join b on  a.col2 = b.col2
                        and a.col3 = b.col3
      where b.col1 is null
    ) a
left outer join master m on a.col3 = m.col1
/

SQL> select * from b;

      COL1 COL COL
---------- --- ---
         1 AAA P01
         2 BBB Q01
         3 CCC S05
         5 EEE B01
         6 FFF FFF

SQL> select * from log_msg;

MSG                             T
------------------------------- -------------------------
4 DDD A02 does not exist Master 28-SEP-11 11.22.09.221000

NATURAL JOIN Explained

A NATURAL JOIN, allows for tables to be joined on common column names
without explicitly naming the columns.


For example, the following two tables; share  the STATISTIC# columns
SQL> desc v$sesstat;
 Name                  
 --------------------
 SID                   
 STATISTIC#            
 VALUE                 

SQL>
SQL> desc v$statname
 Name                  
 --------------------
 STATISTIC#            
 NAME                  
 CLASS                 
 STAT_ID               


So when the following SQL is run, the tables are joined on STATISTIC#;

select max(value)
from v$sesstat natural join v$statname
where name = 'session cursor cache count';



The same applies to the following statement;

select Amount
      ,Session_Cached_Cursors*30+30 Cached_Cursors_Rounded
from
    (
     select trunc(value/30)  as Session_Cached_Cursors
           ,count(*)         as Amount
     from  
            v$sesstat natural join v$statname
     where  name = 'session cursor cache count'
     group by trunc(value/30) order by 1
    )
/

Script to Alter varchar2 byte cols to char

The script below modifies all columns from VARCHAR2 BYTE to CHAR for all tables listed in the IN list. The script uses the USER_TAB_COLUMNS view. A log table has been created to record failures.

create table log_tbl (
  table_name varchar2(30)
, column_name varchar2(30)
, msg varchar2(200)
, error_flag varchar2(1) default 'P') -- P for Pass and F for Fail.
/ 

SQL> select table_name, column_name, char_used
  2  from user_tab_columns
  3  where table_name in ('T1','T2')
  4  / 
 
TABLE_NAME                     COLUMN_NAME                    C
------------------------------ ------------------------------ -
T1                             A                              B
T2                             A                              B
 
SQL> declare
  2    l_Err varchar2(200);
  3  begin
  4    for r in (select  atc.table_name, atc.column_name, atc.data_length
  5              from    user_tab_columns atc -- You would probably use ALL_
  6              left outer join Log_Tbl lt on (atc.Table_name   = lt.Table_Name
  7                                         and atc.Column_name = lt.Column_Name
  8                                         and lt.Error_Flag   = 'P')
  9              where   atc.data_type   = 'VARCHAR2'
 10              and     atc.char_used   = 'B'
 11              and     atc.Table_Name in ('T1', 'T2', 'T3')) loop
 12  
 13      begin 
 14        execute immediate 'alter table ' || r.table_name 
 15                                        || ' modify ' 
 16                                        || r.column_name 
 17                                        || ' varchar2('
 18                                        || r.data_length
 19                                        || ' char)';
 20      
 21        insert into Log_tbl (Table_Name, Column_Name) 
 22        values  (r.Table_Name, r.Column_Name);
 23        
 24        exception
 25          when others then
 26            l_Err := sqlerrm;
 27            insert into Log_tbl (Table_Name, Column_Name, Msg, Error_Flag) 
 28            values  (r.Table_Name, r.Column_Name, l_Err, 'F');
 29      end;
 30      
 31      commit;
 32      
 33    end loop;
 34    
 35  end;
 36  / 
 
PL/SQL procedure successfully completed.
 
SQL> select table_name, column_name, char_used
  2  from user_tab_columns
  3  where table_name in ('T1','T2', 'T3')
  4  / 
 
TABLE_NAME                     COLUMN_NAME                    C
------------------------------ ------------------------------ -
T1                             A                              C
T2                             A                              C
 
SQL> select table_name,column_name,error_flag
  2  from log_tbl;
 
TABLE_NAME      COLUMN_NAME     E
--------------- --------------- -
T1              A               P
T2              A               P
 
SQL> create table t3 (a varchar2(20) )
  2  / 
 
Table created.
 
SQL> insert into t3 (a) values ('Hello')
  2  / 
 
1 row created.

SQL> select table_name, column_name, char_used
  2  from user_tab_columns
  3  where table_name in ('T1','T2', 'T3');

TABLE_NAME                     COLUMN_NAME                    C
------------------------------ ------------------------------ -
T1                             A                              C
T2                             A                              C
T3                             A                              B 

Note the difference in the column char usage between T3 and the other tables given that T3 was created after the script was executed.

SQL> declare
  2    l_Err varchar2(200);
  3  begin
  4    for r in (select  atc.table_name, atc.column_name, atc.data_length
  5              from    user_tab_columns atc -- You would probably use ALL_
  6              left outer join Log_Tbl lt on (atc.Table_name   = lt.Table_Name
  7                                         and atc.Column_name = lt.Column_Name
  8                                         and lt.Error_Flag   = 'P')
  9              where   atc.data_type   = 'VARCHAR2'
 10              and     atc.char_used   = 'B'
 11              and     atc.Table_Name in ('T1', 'T2', 'T3')) loop
 12  
 13      begin 
 14        execute immediate 'alter table ' || r.table_name 
 15                                        || ' modify ' 
 16                                        || r.column_name 
 17                                        || ' varchar2('
 18                                        || r.data_length
 19                                        || ' char)';
 20      
 21        insert into Log_tbl (Table_Name, Column_Name) 
 22        values  (r.Table_Name, r.Column_Name);
 23        
 24        exception
 25          when others then
 26            l_Err := sqlerrm;
 27            insert into Log_tbl (Table_Name, Column_Name, Msg, Error_Flag) 
 28            values  (r.Table_Name, r.Column_Name, l_Err, 'F');
 29      end;
 30      
 31      commit;
 32      
 33    end loop;
 34    
 35  end;
 36  / 
 
PL/SQL procedure successfully completed.
 
SQL> select table_name, column_name, char_used
  2  from user_tab_columns
  3  where table_name in ('T1','T2', 'T3')
  4  / 
 
TABLE_NAME      COLUMN_NAME     C
--------------- --------------- -
T1              A               C
T2              A               C
T3              A               C

The script uses the USER_TAB_COLUMNS view. If you modify the script to use the ALL_TAB_COLUMNS or the DBA_TAB_COLUMNS view, then the script would look as follows:

undefine schema_name
declare
  l_Err varchar2(200);
begin
  for r in (select  atc.table_name, atc.column_name, atc.data_length
            from    all_tab_columns atc -- You would probably use ALL_
            left outer join Log_Tbl lt on (atc.Table_name   = lt.Table_Name
                                       and atc.Column_name = lt.Column_Name
                                       and lt.Error_Flag   = 'P')
            where   atc.data_type   = 'VARCHAR2'
            and     atc.char_used   = 'B'
            and     atc.Table_Name  in ('T1', 'T2', 'T3')
            and     atc.owner       = upper('&&schema_name')) loop

    begin
      execute immediate 'alter table '|| upper('&&schema_name')
                                      || '.'
                                      || r.table_name
                                      || ' modify '
                                      || r.column_name
                                      || ' varchar2('
                                      || r.data_length
                                      || ' char)';

      insert into Log_tbl (Table_Name, Column_Name)
      values  (r.Table_Name, r.Column_Name);

      exception
        when others then
          l_Err := sqlerrm;
          insert into Log_tbl (Table_Name, Column_Name, Msg, Error_Flag)
          values  (r.Table_Name, r.Column_Name, l_Err, 'F');
    end;

    commit;

  end loop;

end;
/
 

The default bhaviour, when the usage is not specified explicitly, is to set each column to BYTE. The default behaviour can be altered by setting NLS_LENGTH_SEMANTICS, for example:

SQL> ALTER SESSION SET NLS_LENGTH_SEMANTICS=BYTE;

Session altered.

SQL> DROP TABLE T1;

Table dropped.

SQL> DROP TABLE T2;

Table dropped.

SQL> ALTER SESSION SET NLS_LENGTH_SEMANTICS=CHAR;

Session altered.

SQL> create table t1 (a varchar2(10));

Table created.

SQL> select table_name,column_name,char_used from user_tab_columns
  2  where table_name = 'T1';

TABLE_NAME COLUMN_NAME C
---------- ----------- -
T1         A           C

SQL> ALTER SESSION SET NLS_LENGTH_SEMANTICS=BYTE;

Session altered.

SQL> create table t2 (a varchar2(10));

Table created.

SQL> select table_name,column_name,char_used from user_tab_columns
  2  where table_name IN ('T2', 'T1');

TABLE_NAME COLUMN_NAME C
---------- ----------- -
T1         A           C
T2         A           B