Hi
I am trying to find the best way to include data from an array within two tables (TABLE01 and TABLE02), in the TABLE02 table there is an FK with the TABLE01 table, and there is a constraint in the TABLE01 table so that the records do not repeat, in this way I want to find the best way to loop and include the records, both tables have a sequence to generate the PK
CREATE TABLE TABLE01
(
ID NUMBER,
CODE_SITE NUMBER(3),
SEQUENCEY NUMBER(15),
STATUS VARCHAR2(1 BYTE) DEFAULT '0',
QTD_ERRORS NUMBER,
TOTAL_DIFFERENCE NUMBER(19,2)
);
CREATE UNIQUE INDEX TABLE01_PK ON TABLE01 (ID);
CREATE UNIQUE INDEX TABLE01_U01 ON TABLE01
(CODE_SITE, SEQUENCEY, STATUS)
NOLOGGING;
ALTER TABLE TABLE01 ADD (
CONSTRAINT TABLE01_PK
PRIMARY KEY
(ID)
USING INDEX TABLE01_PK
ENABLE VALIDATE,
CONSTRAINT TABLE01_U01
UNIQUE (CODE_SITE, SEQUENCEY, STATUS)
USING INDEX TABLE01_U01
ENABLE VALIDATE);
CREATE SEQUENCE TABLE01_SEQ MINVALUE 1 MAXVALUE 100000000000000000 START WITH 1 INCREMENT BY 1 CACHE 20;
CREATE TABLE TABLE02
(
ID NUMBER,
ID_TABLE01 NUMBER,
CODE_SITE NUMBER(3),
SEQUENCEY NUMBER(15),
SEQ_ITEM NUMBER(6),
ID_COLUMN NUMBER,
VALUE_ORIGINAL NUMBER(19,4),
VALUE_CALCULATE NUMBER(19,4)
)
CREATE UNIQUE INDEX TABLE02_PK ON TABLE02(ID);
ALTER TABLE TABLE02 ADD (
CONSTRAINT TABLE02_PK
PRIMARY KEY
(ID)
USING INDEX TABLE02_PK
ENABLE VALIDATE);
ALTER TABLE TABLE02 ADD (
CONSTRAINT TABLE02_R01
FOREIGN KEY (ID_TABLE01)
REFERENCES TABLE01 (ID)
ENABLE VALIDATE);
CREATE SEQUENCE TABLE02_SEQ MINVALUE 1 MAXVALUE 100000000000000000 START WITH 1 INCREMENT BY 1 CACHE 20;
the array is something as
declare
TYPE TYPE_DIFFERENCE IS RECORD (
CODE_SITE NUMBER(3),
SEQUENCEY NUMBER(15),
SEQ_ITEM NUMBER(6),
ID_COLUMN NUMBER ,
NAME_COLUMN VARCHAR2(30),
VALUE_CALCULATE NUMBER(19,4),
VALUE_ORIGINAL NUMBER(19,4),
TOTAL_DIFFERENCE NUMBER(19,4),
QTD_ERRORS NUMBER
);
TYPE ARR_DIFFERENCE IS TABLE OF TYPE_DIFFERENCE;
TBL_DIFFERENCE ARR_DIFFERENCE:= new ARR_DIFFERENCE() ;
v_id number;
begin
TBL_DIFFERENCE.extend;
TBL_DIFFERENCE(TBL_DIFFERENCE.COUNT) := TYPE_DIFFERENCE (1,82650936, 2, 17,'XPTO', 5,0,-610,6);
TBL_DIFFERENCE.extend;
TBL_DIFFERENCE(TBL_DIFFERENCE.COUNT) :=TYPE_DIFFERENCE (1,82650936, 2, 48,'ABCD', 100,1000,-610,6);
TBL_DIFFERENCE.extend;
TBL_DIFFERENCE(TBL_DIFFERENCE.COUNT) := TYPE_DIFFERENCE (1,82650936, 2, 63,'CDBA', 100, 0, -610,6);
TBL_DIFFERENCE.extend;
TBL_DIFFERENCE(TBL_DIFFERENCE.COUNT) := TYPE_DIFFERENCE (1,82650936, 2, 69,'COVID', 100, 0, -610,6);
TBL_DIFFERENCE.extend;
TBL_DIFFERENCE(TBL_DIFFERENCE.COUNT) := TYPE_DIFFERENCE (1,82650936, 2, 70,'ZAER', 100, 0, -610,6);
TBL_DIFFERENCE.extend;
TBL_DIFFERENCE(TBL_DIFFERENCE.COUNT) := TYPE_DIFFERENCE (1,82650936, 2, 85,'ADAD', 0, 15, -610,6);
---
TBL_DIFFERENCE.extend;
TBL_DIFFERENCE(TBL_DIFFERENCE.COUNT) := TYPE_DIFFERENCE (1,82650937, 1, 86,'ADAB', 14, 30, 16,1);
--- I want to insert in two tables : TABLE01 and TABLE02
--In TABLE02 there is a COLUMN is FK (ID_TABLE01)
--What is best way to do a loop and to insert data in two tables
--for j IN 1 .. TBL_DIFFERENCE.COUNT loop
-- I tried something like
-- BEGIN
-- INSERT /*+ ignore_row_on_dupkey_index(EXF_BATIMENTO_TRIB, TABLE01_U01) */ INTO TABLE01 (ID, CODE_SITE, SEQUENCEY, STATUS, QTD_ERRORS, TOTAL_DIFFERENCE)
/* VALUES ( TABLE01_SEQ.nextval,
TBL_DIFFERENCE(j).CODE_SITE,
TBL_DIFFERENCE(j).SEQUENCEY,
TBL_DIFFERENCE(j).STATUS,
TBL_DIFFERENCE(j).QTD_ERRORS,
TBL_DIFFERENCE(j).TOTAL_DIFFERENCE) RETURNING ID INTO v_id;
END LOOP;
*/
null;
end;
I wish the data were like this
SQL> select a.*
2 from TABLE01 a;
ID CODE_SITE SEQUENCEY STATUS QTD_ERRORS TOTAL_DIFFERENCE
---------- --------- ---------------- ------ ---------- ---------------------
1 1 82650936 0 6 -610,00
2 1 82650937 0 1 16,00
SQL>
SQL> select b.*
2 from table02 b;
ID ID_TABLE01 CODE_SITE SEQUENCEY SEQ_ITEM ID_COLUMN VALUE_ORIGINAL VALUE_CALCULATE
---------- ---------- --------- ---------------- -------- ---------- --------------------- ---------------------
1 1 1 82650936 2 17 5,0000 0,0000
2 1 1 82650936 2 48 100,0000 1000,0000
3 1 1 82650936 2 63 100,0000 0,0000
4 1 1 82650936 2 69 100,0000 0,0000
5 1 1 82650936 2 70 100,0000 0,0000
6 1 1 82650936 2 85 0,0000 15,0000
7 2 1 82650937 1 86 14,0000 30,0000
7 rows selected
Using
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
thank you in advance
Edited:
When I tried to insert second table (TABLE02) return error
ORA-02291
even though I inserted the record in the parent table table01, same session I haven't committed yet