SQL & PL/SQL

Hello,

I have the following problem.

I need to migrate from an old db schema to a new one. This means I ned to select the data from multiple old tables, join it and insert it in a new table. To do this, I use bulk collect into like this:

declare
type t_oun IS TABLE OF brpp.oepl_organizational_units % rowtype;
v_t_oun t_oun;
begin
SELECT distinct oe1.oe_cd oun_id,
1 t_version,
NULL oun_id_higher,
outy.out_id out_id,
oe1.oe_cd oun_cd,
oe1.nm_oe name,
oe1.verkort_oe name_short,
oe1.bgdt_oe start_date_bls,
oe1.enddt_oe end_date_bls bulk collect
INTO v_t_oun
FROM imp_bls_br_oe_oe oe1,
brpp.oepl_org_oun_types outy
WHERE oe1.oe_srt_cd = outy.out_cd;
BEGIN
forall j IN indices OF v_t_oun save exceptions INSERT
INTO brpp.oepl_organizational_units
VALUES v_t_oun(j);
EXCEPTION
WHEN bulk_errors THEN
FOR j IN 1 .. SQL % bulk_exceptions.COUNT
LOOP
migration_utils.log_msg(p_proc => 'migrate_organizational_units', p_table_name =>'imp_bls_br_oe_oe', record_id => v_t_oun(SQL % bulk_exceptions(j).error_index).oun_cd, p_ora_id => SQL % bulk_exceptions(j).error_code, p_ora_msg => sqlerrm(-SQL % bulk_exceptions(j).error_code), p_type => 'error', p_short => 'INSERT ERROR', p_desc => 'Something went wrong while inserting values into ' || v_table_name);
END LOOP;
END;
end;

However, something strange happens. My source table has duplicate names, which is a collumn that is designated unique in the new table. This unique constraint is triggered correctly, and my log table shows a line for each duplicate name. The query displayed above only yields duplicate names, every other field is as it should be. When I check the destination table I see that the OUN_ID collumn (which is my primary key collumn) contains loads of duplicates. The first record migrated is a single record, the second is a duplicate, the third is triple and so on.

This could be some error in the FORALL, but what I don't understand is, why didn't the primary key constraint catch this behaviour?

Mark