Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table tst_cri_sdb (icri number, datesitu date, curr varchar2(3), ctmstm varchar2(10));
insert into TST_CRI_SDB values (100, to_date('13032019','ddmmyyyy'), 'EUR', 'STM');
1 rows affected

insert into TST_CRI_SDB values (101, to_date('14032019','ddmmyyyy'), 'GBP', 'CTM');
1 rows affected

insert into TST_CRI_SDB values (102, to_date('15032019','ddmmyyyy'), 'USD', 'STM');
1 rows affected

insert into TST_CRI_SDB values (103, to_date('16032019','ddmmyyyy'), 'INR', 'CTM');
1 rows affected

insert into TST_CRI_SDB values (104, to_date('17032019','ddmmyyyy'), 'EUR', 'STM');
1 rows affected

create type tst_rec as object (icri number, datesitu date, curr varchar2(3), ctmstm varchar2(10));
create type tst_table_rec as table of tst_rec;
create table sdb_gpcs (curr varchar2(3), ctmstm varchar2(5), goca number, cust_grp varchar2(30));
insert into sdb_gpcs values ('EUR','CTM', 100345 ,'A1105');
1 rows affected

insert into sdb_gpcs values ('EUR','CTM', 200345 ,'A4405');
1 rows affected

insert into sdb_gpcs values ('EUR','STM', 300345 ,'A3305');
1 rows affected

insert into sdb_gpcs values ('USD','CTM', 500345 ,'A5505');
1 rows affected

insert into sdb_gpcs values ('USD','STM', 600345 ,'A6605');
1 rows affected

insert into sdb_gpcs values ('USD','STM', 700345 ,'A7705');
1 rows affected

select * from sdb_gpcs where curr = 'EUR' and ctmstm = 'CTM';


CURR
CTMSTM
GOCA
CUST_GRP


EUR
CTM
100345
A1105

EUR
CTM
200345
A4405


create table tst_cri_plus_sdb (deal_id number, datesitu date, acc_code number, acca_cust_grp varchar2(10), curr varchar2(3), ctmstm varchar2(5));
create type tst_plus_rec as object(deal_id number, datesitu date, acc_code number, acca_cust_grp varchar2(10), curr varchar2(3), ctmstm varchar2(5));
create type tst_plus_table_rec as table of tst_plus_rec;
create or replace function get_plus_sdb_w (p_tab in tst_table_rec)
return tst_plus_table_rec pipelined
is
  l_rec tst_plus_rec;
begin
  for i in 1..p_tab.count
  loop
    for j in (select * from sdb_gpcs)
    loop
      l_rec := tst_plus_rec(p_tab(i).icri, p_tab(i).datesitu, j.goca, j.cust_grp ,p_tab(i).curr, p_tab(i).ctmstm);
      pipe row (l_rec);
    end loop;
  end loop;
end;
/
create or replace procedure tst_insert
is
  cursor c1 is select * from tst_cri_sdb;
  l_tab tst_table_rec := tst_table_rec();
-- no need to initialise this one as bulk collect will replace it
--  l_tab_plus tst_plus_table_rec := tst_plus_table_rec();
  l_tab_plus tst_plus_table_rec;
begin
  for i in c1
  loop
    l_tab.extend;
    l_tab(l_tab.last) := tst_rec(i.icri, i.datesitu, i.curr, i.ctmstm);
  end loop;

  select tst_plus_rec(deal_id, datesitu, acc_code, acca_cust_grp, curr, ctmstm)
  bulk collect into l_tab_plus
  from table(get_plus_sdb_w(l_tab));

  forall idx IN INDICES OF l_tab_plus
  insert into tst_cri_plus_sdb (deal_id, datesitu, acc_code, acca_cust_grp, curr, ctmstm)
  values (l_tab_plus(idx).deal_id, l_tab_plus(idx).datesitu, l_tab_plus(idx).acc_code,
    l_tab_plus(idx).acca_cust_grp, l_tab_plus(idx).curr, l_tab_plus(idx).ctmstm);
end;
/
begin
  tst_insert;
end;
/
1 rows affected

select * from tst_cri_plus_sdb;


DEAL_ID
DATESITU
ACC_CODE
ACCA_CUST_GRP
CURR
CTMSTM


100
13-MAR-19
100345
A1105
EUR
STM

100
13-MAR-19
200345
A4405
EUR
STM

100
13-MAR-19
300345
A3305
EUR
STM

100
13-MAR-19
500345
A5505
EUR
STM

100
13-MAR-19
600345
A6605
EUR
STM

100
13-MAR-19
700345
A7705
EUR
STM

101
14-MAR-19
100345
A1105
GBP
CTM

101
14-MAR-19
200345
A4405
GBP
CTM

101
14-MAR-19
300345
A3305
GBP
CTM

101
14-MAR-19
500345
A5505
GBP
CTM

101
14-MAR-19
600345
A6605
GBP
CTM

101
14-MAR-19
700345
A7705
GBP
CTM

102
15-MAR-19
100345
A1105
USD
STM

102
15-MAR-19
200345
A4405
USD
STM

102
15-MAR-19
300345
A3305
USD
STM

102
15-MAR-19
500345
A5505
USD
STM

102
15-MAR-19
600345
A6605
USD
STM

102
15-MAR-19
700345
A7705
USD
STM

103
16-MAR-19
100345
A1105
INR
CTM

103
16-MAR-19
200345
A4405
INR
CTM

103
16-MAR-19
300345
A3305
INR
CTM

103
16-MAR-19
500345
A5505
INR
CTM

103
16-MAR-19
600345
A6605
INR
CTM

103
16-MAR-19
700345
A7705
INR
CTM

104
17-MAR-19
100345
A1105
EUR
STM

104
17-MAR-19
200345
A4405
EUR
STM

104
17-MAR-19
300345
A3305
EUR
STM

104
17-MAR-19
500345
A5505
EUR
STM

104
17-MAR-19
600345
A6605
EUR
STM

104
17-MAR-19
700345
A7705
EUR
STM


rollback;
create or replace procedure tst_insert
is
  cursor c1 is select * from tst_cri_sdb;
  l_tab tst_table_rec := tst_table_rec();
-- you don't need this variable at all now
--  l_tab_plus tst_plus_table_rec;
begin
  for i in c1
  loop
    l_tab.extend;
    l_tab(l_tab.last) := tst_rec(i.icri, i.datesitu, i.curr, i.ctmstm);
  end loop;

  insert into tst_cri_plus_sdb
  select *
  from table(get_plus_sdb_w(l_tab));
end;
/
begin
  tst_insert;
end;
/
1 rows affected

select * from tst_cri_plus_sdb;


DEAL_ID
DATESITU
ACC_CODE
ACCA_CUST_GRP
CURR
CTMSTM


100
13-MAR-19
100345
A1105
EUR
STM

100
13-MAR-19
200345
A4405
EUR
STM

100
13-MAR-19
300345
A3305
EUR
STM

100
13-MAR-19
500345
A5505
EUR
STM

100
13-MAR-19
600345
A6605
EUR
STM

100
13-MAR-19
700345
A7705
EUR
STM

101
14-MAR-19
100345
A1105
GBP
CTM

101
14-MAR-19
200345
A4405
GBP
CTM

101
14-MAR-19
300345
A3305
GBP
CTM

101
14-MAR-19
500345
A5505
GBP
CTM

101
14-MAR-19
600345
A6605
GBP
CTM

101
14-MAR-19
700345
A7705
GBP
CTM

102
15-MAR-19
100345
A1105
USD
STM

102
15-MAR-19
200345
A4405
USD
STM

102
15-MAR-19
300345
A3305
USD
STM

102
15-MAR-19
500345
A5505
USD
STM

102
15-MAR-19
600345
A6605
USD
STM

102
15-MAR-19
700345
A7705
USD
STM

103
16-MAR-19
100345
A1105
INR
CTM

103
16-MAR-19
200345
A4405
INR
CTM

103
16-MAR-19
300345
A3305
INR
CTM

103
16-MAR-19
500345
A5505
INR
CTM

103
16-MAR-19
600345
A6605
INR
CTM

103
16-MAR-19
700345
A7705
INR
CTM

104
17-MAR-19
100345
A1105
EUR
STM

104
17-MAR-19
200345
A4405
EUR
STM

104
17-MAR-19
300345
A3305
EUR
STM

104
17-MAR-19
500345
A5505
EUR
STM

104
17-MAR-19
600345
A6605
EUR
STM

104
17-MAR-19
700345
A7705
EUR
STM


rollback;
create or replace procedure tst_insert
is
  l_tab tst_table_rec;
begin
  select tst_rec(icri, datesitu, curr, ctmstm)
  bulk collect into l_tab
  from tst_cri_sdb;

  insert into tst_cri_plus_sdb
  select *
  from table(get_plus_sdb_w(l_tab));
end;
/
begin
  tst_insert;
end;
/
1 rows affected

select * from tst_cri_plus_sdb;


DEAL_ID
DATESITU
ACC_CODE
ACCA_CUST_GRP
CURR
CTMSTM


100
13-MAR-19
100345
A1105
EUR
STM

100
13-MAR-19
200345
A4405
EUR
STM

100
13-MAR-19
300345
A3305
EUR
STM

100
13-MAR-19
500345
A5505
EUR
STM

100
13-MAR-19
600345
A6605
EUR
STM

100
13-MAR-19
700345
A7705
EUR
STM

101
14-MAR-19
100345
A1105
GBP
CTM

101
14-MAR-19
200345
A4405
GBP
CTM

101
14-MAR-19
300345
A3305
GBP
CTM

101
14-MAR-19
500345
A5505
GBP
CTM

101
14-MAR-19
600345
A6605
GBP
CTM

101
14-MAR-19
700345
A7705
GBP
CTM

102
15-MAR-19
100345
A1105
USD
STM

102
15-MAR-19
200345
A4405
USD
STM

102
15-MAR-19
300345
A3305
USD
STM

102
15-MAR-19
500345
A5505
USD
STM

102
15-MAR-19
600345
A6605
USD
STM

102
15-MAR-19
700345
A7705
USD
STM

103
16-MAR-19
100345
A1105
INR
CTM

103
16-MAR-19
200345
A4405
INR
CTM

103
16-MAR-19
300345
A3305
INR
CTM

103
16-MAR-19
500345
A5505
INR
CTM

103
16-MAR-19
600345
A6605
INR
CTM

103
16-MAR-19
700345
A7705
INR
CTM

104
17-MAR-19
100345
A1105
EUR
STM

104
17-MAR-19
200345
A4405
EUR
STM

104
17-MAR-19
300345
A3305
EUR
STM

104
17-MAR-19
500345
A5505
EUR
STM

104
17-MAR-19
600345
A6605
EUR
STM

104
17-MAR-19
700345
A7705
EUR
STM


rollback;

DEAL_ID	DATESITU	ACC_CODE	ACCA_CUST_GRP	CURR	CTMSTM
100	13-MAR-19	100345	A1105	EUR	STM
100	13-MAR-19	200345	A4405	EUR	STM
100	13-MAR-19	300345	A3305	EUR	STM
100	13-MAR-19	500345	A5505	EUR	STM
100	13-MAR-19	600345	A6605	EUR	STM
100	13-MAR-19	700345	A7705	EUR	STM
101	14-MAR-19	100345	A1105	GBP	CTM
101	14-MAR-19	200345	A4405	GBP	CTM
101	14-MAR-19	300345	A3305	GBP	CTM
101	14-MAR-19	500345	A5505	GBP	CTM
101	14-MAR-19	600345	A6605	GBP	CTM
101	14-MAR-19	700345	A7705	GBP	CTM
102	15-MAR-19	100345	A1105	USD	STM
102	15-MAR-19	200345	A4405	USD	STM
102	15-MAR-19	300345	A3305	USD	STM
102	15-MAR-19	500345	A5505	USD	STM
102	15-MAR-19	600345	A6605	USD	STM
102	15-MAR-19	700345	A7705	USD	STM
103	16-MAR-19	100345	A1105	INR	CTM
103	16-MAR-19	200345	A4405	INR	CTM
103	16-MAR-19	300345	A3305	INR	CTM
103	16-MAR-19	500345	A5505	INR	CTM
103	16-MAR-19	600345	A6605	INR	CTM
103	16-MAR-19	700345	A7705	INR	CTM
104	17-MAR-19	100345	A1105	EUR	STM
104	17-MAR-19	200345	A4405	EUR	STM
104	17-MAR-19	300345	A3305	EUR	STM
104	17-MAR-19	500345	A5505	EUR	STM
104	17-MAR-19	600345	A6605	EUR	STM
104	17-MAR-19	700345	A7705	EUR	STM