This test case is a bit more comprehensive. I have got a table, similar to this one:
create table t1
(id number,
ref_1 number,
ref_2 number,
d_year number,
val_1_jan number,
val_1_feb number,
val_1_mrz number,
val_1_apr number,
val_1_may number,
val_1_jun number,
val_1_jul number,
val_1_aug number,
val_1_sep number,
val_1_oct number,
val_1_nov number,
val_1_dec number,
val_2_jan number,
val_2_feb number,
val_2_mrz number,
val_2_apr number,
val_2_may number,
val_2_jun number,
val_2_jul number,
val_2_aug number,
val_2_sep number,
val_2_oct number,
val_2_nov number,
val_2_dec number);
and I wrote a procedure to transform the data into a table similar to this:
create table t2
( id number generated always as identity(start with 1 increment by 1),
ref_1 number,
ref_2 number,
d_month date,
val_id number,
d_value number,
idate date,
udate date);
So, when I have this two lines in my first table, with 24 “val_1” and “val_2” values each:
insert into t1 values(1,101,22,2025,23,25,19,29,14,31,28,27,26,26,23,32,26,22,20,30,19,29,28,26,27,25,23,30);
insert into t1 values(2,101,23,2025,17,34,11,26,18,27,25,25,17,17,24,31,29,20,18,39,23,40,41,16,20,27,24,29);
they will be transformed into 48 rows in my second table using this procedure:
-- first a small help table for some logging
create table load_log
( load_date date,
rows_merged number,
rows_deleted number);
------------------------------------------------------------------------------------
create or replace procedure data_load is
type val_list is table of number index by pls_integer;
type year_values is table of val_list index by pls_integer;
val year_values;
i_month number;
i_val number;
num_rows_merged number;
num_rows_deleted number;
cursor c is
select
id ,
ref_1 ,
ref_2 ,
d_year ,
val_1_jan ,
val_1_feb ,
val_1_mrz ,
val_1_apr ,
val_1_may ,
val_1_jun ,
val_1_jul ,
val_1_aug ,
val_1_sep ,
val_1_oct ,
val_1_nov ,
val_1_dec ,
val_2_jan ,
val_2_feb ,
val_2_mrz ,
val_2_apr ,
val_2_may ,
val_2_jun ,
val_2_jul ,
val_2_aug ,
val_2_sep ,
val_2_oct ,
val_2_nov ,
val_2_dec
from t1;
begin
num_rows_merged :=0;
num_rows_deleted :=0;
for c_rec in c loop
val(1)( 1) := c_rec.val_1_jan;
val(1)( 2) := c_rec.val_1_feb;
val(1)( 3) := c_rec.val_1_mrz;
val(1)( 4) := c_rec.val_1_apr;
val(1)( 5) := c_rec.val_1_may;
val(1)( 6) := c_rec.val_1_jun;
val(1)( 7) := c_rec.val_1_jul;
val(1)( 8) := c_rec.val_1_aug;
val(1)( 9) := c_rec.val_1_sep;
val(1)(10) := c_rec.val_1_oct;
val(1)(11) := c_rec.val_1_nov;
val(1)(12) := c_rec.val_1_dec;
val(2)( 1) := c_rec.val_2_jan;
val(2)( 2) := c_rec.val_2_feb;
val(2)( 3) := c_rec.val_2_mrz;
val(2)( 4) := c_rec.val_2_apr;
val(2)( 5) := c_rec.val_2_may;
val(2)( 6) := c_rec.val_2_jun;
val(2)( 7) := c_rec.val_2_jul;
val(2)( 8) := c_rec.val_2_aug;
val(2)( 9) := c_rec.val_2_sep;
val(2)(10) := c_rec.val_2_oct;
val(2)(11) := c_rec.val_2_nov;
val(2)(12) := c_rec.val_2_dec;
for i_month in 1..12 loop
for i_val in 1..2 loop
merge into t2
using (
select c_rec.d_year as s_year,
i_month as s_month,
c_rec.ref_1 as s_ref_1,
c_rec.ref_2 as s_ref_2,
to_date('01'||'.'||trim(to_char(i_month,'00'))||
'.'||trim(to_char(c_rec.d_year,'0000')),'dd.mm.yyyy') as s_date,
i_val as s_val_id,
val(i_val)(i_month) as s_val -- add „from dual“ here
) src -- for a database before 23ai
on ( extract(year from t2.d_month) = src.s_year
and extract(month from t2.d_month) = src.s_month
and t2.ref_1 = src.s_ref_1
and t2.ref_2 = src.s_ref_2
and t2.val_id = src.s_val_id)
when matched then
update set d_value = src.s_val,
udate = sysdate
where d_value <> src.s_val
when not matched then
insert (ref_1, ref_2, d_month, val_id, d_value, idate, udate )
values(src.s_ref_1,src.s_ref_2, src.s_date, src.s_val_id, src.s_val, sysdate, sysdate );
num_rows_merged := num_rows_merged + sql%rowcount;
end loop;
end loop;
commit;
end loop;
delete from t2 where (extract(year from d_month),ref_1, ref_2) not in (select d_year, ref_1, ref_2 from t1);
num_rows_deleted := num_rows_deleted + sql%rowcount;
insert into load_log values(sysdate, num_rows_merged, num_rows_deleted);
commit;
end;
/
Some test:
exec data_load;
select * from load_log;
LOAD_DATE ROWS_MERGED ROWS_DELETED
---------------- ----------- ------------
14.02.2025 19:48 48 0
Please note that only one row is merged into T2 and gets a new „udate“, when only one value in the table T1 is modified:
update t1 set val_1_jun = 15 where ref_1 = 101 and ref_2 = 22;
exec data_load;
select * from load_log;
LOAD_DATE ROWS_MERGED ROWS_DELETED
---------------- ----------- ------------
14.02.2025 19:48 48 0
14.02.2025 19:49 1 0
So far so good! But I then I thought that this three nested loops are not very performant and that the merge statement only inserts or updates one single row, each time it is called. I tried to modify my procedure, to handle all 24 values of the input table with one merge statement and I tried this:
create or replace procedure data_load_v2 is
type val_list is table of number index by pls_integer;
type year_values is table of val_list index by pls_integer;
val year_values;
num_rows_merged number;
num_rows_deleted number;
cursor c is
select
id ,
ref_1 ,
ref_2 ,
d_year ,
val_1_jan ,
val_1_feb ,
val_1_mrz ,
val_1_apr ,
val_1_may ,
val_1_jun ,
val_1_jul ,
val_1_aug ,
val_1_sep ,
val_1_oct ,
val_1_nov ,
val_1_dec ,
val_2_jan ,
val_2_feb ,
val_2_mrz ,
val_2_apr ,
val_2_may ,
val_2_jun ,
val_2_jul ,
val_2_aug ,
val_2_sep ,
val_2_oct ,
val_2_nov ,
val_2_dec
from t1;
begin
num_rows_merged :=0;
num_rows_deleted :=0;
for c_rec in c loop
val(1)( 1) := c_rec.val_1_jan;
val(1)( 2) := c_rec.val_1_feb;
val(1)( 3) := c_rec.val_1_mrz;
val(1)( 4) := c_rec.val_1_apr;
val(1)( 5) := c_rec.val_1_may;
val(1)( 6) := c_rec.val_1_jun;
val(1)( 7) := c_rec.val_1_jul;
val(1)( 8) := c_rec.val_1_aug;
val(1)( 9) := c_rec.val_1_sep;
val(1)(10) := c_rec.val_1_oct;
val(1)(11) := c_rec.val_1_nov;
val(1)(12) := c_rec.val_1_dec;
val(2)( 1) := c_rec.val_2_jan;
val(2)( 2) := c_rec.val_2_feb;
val(2)( 3) := c_rec.val_2_mrz;
val(2)( 4) := c_rec.val_2_apr;
val(2)( 5) := c_rec.val_2_may;
val(2)( 6) := c_rec.val_2_jun;
val(2)( 7) := c_rec.val_2_jul;
val(2)( 8) := c_rec.val_2_aug;
val(2)( 9) := c_rec.val_2_sep;
val(2)(10) := c_rec.val_2_oct;
val(2)(11) := c_rec.val_2_nov;
val(2)(12) := c_rec.val_2_dec;
merge into t2
using (
select c_rec.d_year as s_year,
i_month as s_month,
c_rec.ref_1 as s_ref_1,
c_rec.ref_2 as s_ref_2,
to_date('01'||'.'||trim(to_char(i_month,'00'))||
'.'||trim(to_char(c_rec.d_year,'0000')),'dd.mm.yyyy') as s_date,
i_val as s_val_id,
val(i_val)(i_month) as s_val
from (select level as i_val connect by level < 3) -- this is my approach
cross join (select level as i_month connect by level < 13) -- to replace two loops
) src
on ( extract(year from t2.d_month) = src.s_year
and extract(month from t2.d_month) = src.s_month
and t2.ref_1 = src.s_ref_1
and t2.ref_2 = src.s_ref_2
and t2.val_id = src.s_val_id)
when matched then
update set d_value = src.s_val,
udate = sysdate
where d_value <> src.s_val
when not matched then
insert (ref_1, ref_2, d_month, val_id, d_value, idate, udate )
values(src.s_ref_1,src.s_ref_2, src.s_date, src.s_val_id, src.s_val, sysdate, sysdate );
num_rows_merged := num_rows_merged + sql%rowcount;
commit;
end loop;
delete from t2 where (extract(year from d_month),ref_1, ref_2) not in (select d_year, ref_1, ref_2 from t1);
num_rows_deleted := num_rows_deleted + sql%rowcount;
insert into load_log values(sysdate, num_rows_merged, num_rows_deleted);
commit;
end;
/
But compiling this procedure gives an error:
LINE/COL ERROR
-------- -----------------------------------------------------------------
74/10 PL/SQL: SQL Statement ignored
83/28 PLS-00201: identifier 'I_VAL' must be declared
83/34 PL/SQL: ORA-03066: invalid PL/SQL expression
The problem here is only the reference of the array “val(i_val)(i_month)”. In the lines before it is possible to reference the columns “i_val” and “i_month” that are created by the two subqueries. How can I solve this problem? Is it possible to reference the array via the columns of the “connect by”-subqueries? Any suggestions for a completely different approach to the problem are also welcome.