Forum Stats

  • 3,759,245 Users
  • 2,251,515 Discussions
  • 7,870,550 Comments

Discussions

Read a table of Record an insert two tables

muttleychess
muttleychess Member Posts: 1,496 Bronze Badge
edited Apr 15, 2021 1:24PM in SQL & PL/SQL

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

Tagged: