I have to read (loop) a structure similar to below, where I have to read and write some data in a table, but I have two setbacks, I can't change the original structure and I can't create a unique key in the table that will receive some of this data, so I would like to know if there is any way to copy this structure to another , so that it does not have duplicate lines I use all fields in the record, with the exception of the item
DECLARE
TYPE TY_REGBASE IS RECORD(
item INTEGER,
type_item VARCHAR2(20),
BTW_ORDER VARCHAR2(4000),
OBS_ORDER VARCHAR2(4000),
date_ini date,
date_end date);
TYPE LIST_REGBASE IS TABLE OF TY_REGBASE INDEX BY BINARY_INTEGER;
ARR_LIST LIST_REGBASE;
m number := 0;
BEGIN
ARR_LIST(m).item := 1001;
ARR_LIST(m).type_item := 'ABC';
ARR_LIST(m).BTW_ORDER := 'XPTO';
ARR_LIST(m).OBS_ORDER := NULL;
ARR_LIST(m).date_ini := TO_DATE('22/04/2022', 'DD/MM/YYYY');
ARR_LIST(m).date_end := NULL;
m := m + 1;
ARR_LIST(m).item := 1002;
ARR_LIST(m).type_item := 'EFG';
ARR_LIST(m).BTW_ORDER := 'NEW XPTO';
ARR_LIST(m).OBS_ORDER := NULL;
ARR_LIST(m).date_ini := TO_DATE('22/04/2022', 'DD/MM/YYYY');
ARR_LIST(m).date_end := NULL;
m := m + 1;
ARR_LIST(m).item := 1003;
ARR_LIST(m).type_item := 'HIJ';
ARR_LIST(m).BTW_ORDER := 'XPTO NEW';
ARR_LIST(m).OBS_ORDER := NULL;
ARR_LIST(m).date_ini := TO_DATE('22/04/2022', 'DD/MM/YYYY');
ARR_LIST(m).date_end := NULL;
m := m + 1;
ARR_LIST(m).item := 1004;
ARR_LIST(m).type_item := 'ABC';
ARR_LIST(m).BTW_ORDER := 'XPTO';
ARR_LIST(m).OBS_ORDER := NULL;
ARR_LIST(m).date_ini := TO_DATE('22/04/2022', 'DD/MM/YYYY');
ARR_LIST(m).date_end := NULL;
for a in ARR_LIST.first .. ARR_LIST.last loop
dbms_output.put_line(' ARR_LIST(a).item ' || ARR_LIST(a).item);
dbms_output.put_line(' ARR_LIST(a).type_item ' || ARR_LIST(a)
.type_item);
dbms_output.put_line(' ARR_LIST(a).BTW_ORDER ' || ARR_LIST(a)
.BTW_ORDER);
dbms_output.put_line(' ARR_LIST(a).OBS_ORDER ' || ARR_LIST(a)
.OBS_ORDER);
dbms_output.put_line(' ARR_LIST(a).date_ini ' || ARR_LIST(a).date_ini);
dbms_output.put_line(' ARR_LIST(a).date_end ' || ARR_LIST(a).date_end);
dbms_output.put_line('--------------------------------------------------');
end loop;
END;
ARR_LIST(a).item 1001
ARR_LIST(a).type_item ABC
ARR_LIST(a).BTW_ORDER XPTO
ARR_LIST(a).OBS_ORDER
ARR_LIST(a).date_ini 22/04/22
ARR_LIST(a).date_end
--------------------------------------------------
ARR_LIST(a).item 1002
ARR_LIST(a).type_item EFG
ARR_LIST(a).BTW_ORDER NEW XPTO
ARR_LIST(a).OBS_ORDER
ARR_LIST(a).date_ini 22/04/22
ARR_LIST(a).date_end
--------------------------------------------------
ARR_LIST(a).item 1003
ARR_LIST(a).type_item HIJ
ARR_LIST(a).BTW_ORDER XPTO NEW
ARR_LIST(a).OBS_ORDER
ARR_LIST(a).date_ini 22/04/22
ARR_LIST(a).date_end
--------------------------------------------------
ARR_LIST(a).item 1004
ARR_LIST(a).type_item ABC
ARR_LIST(a).BTW_ORDER XPTO
ARR_LIST(a).OBS_ORDER
ARR_LIST(a).date_ini 22/04/22
ARR_LIST(a).date_end
--------------------------------------------------
note that items 1001 and 1004 are the same, as all fields, with the exception of the item, are identical, in this case as they are the same, I would like to discard and leave only one line
using:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production