Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Export table row and its ancestors

muttleychessMay 31 2021 — edited May 31 2021

I have to export tables to another environment that will not necessarily be Oracle, so I have to export in text format (maybe even loader),
but in any case I need to obey the contained integrity, because the other environment will also validate these contraints

<img src="https://objectstorage.us-phoenix-1.oraclecloud.com/p/BqK85Rn1zA5MP0vYiqbAdPgs7Z6OmMxw8SD3WCFVm5kY8uReidZ1KPIKkgJ1hCkG/n/axciphqpnohg/b/forums-legacy/o/uploads/0FM1GJVTQ0SI/modelo.png" alt="modelo.png">Insert into TBL_XPT01   (ID, COLUMN01, COLUMN02) Values (1, 'ABCDE', 'ABCDE');
Insert into TBL_XPT01   (ID, COLUMN01, COLUMN02) Values (2, 'FGHIJ', 'FGHIJ');
Insert into TBL_XPT01   (ID, COLUMN01, COLUMN02) Values (3, 'VCXZ', 'VCXZ');
COMMIT;

Insert into TBL_XPT02  (ID, XPT01_ID, MANAGER) Values  (1, 1, 'CLERCK');
Insert into TBL_XPT02  (ID, XPT01_ID, MANAGER) Values  (2, 1, 'BORIS');
Insert into TBL_XPT02  (ID, XPT01_ID, MANAGER) Values  (3, 2, 'BOZO');
COMMIT;

Insert into TBL_XPT03  (ID, XPT02_ID, COLUMN01, COLUMN02) Values   (1, 1, 'KARPOV', '1975');
Insert into TBL_XPT03 (ID, XPT02_ID, COLUMN01, COLUMN02) Values   (2, 1, 'KASPAROV', '1984');
Insert into TBL_XPT03 (ID, XPT02_ID, COLUMN01, COLUMN02) Values   (3, 3, 'MESQUINHO', '2100');
COMMIT;

Insert into TBL_XPT03A (ID, COLUMN01, COLUMN02) Values   (1, 'QWER', 'QWER');
Insert into TBL_XPT03A (ID, COLUMN01, COLUMN02) Values   (2, 'ASDF', 'ASDF');
Insert into TBL_XPT03A (ID, COLUMN01, COLUMN02) Values   (3, 'ZXCV', 'ZXCV');
COMMIT;

Insert into TBL_XPT03B (ID, XPT03A_ID, COLUMN01, COLUMN02, COLUMN03) Values   (1, 1, 'SP', 'MG', 'MILK');
Insert into TBL_XPT03B (ID, XPT03A_ID, COLUMN01, COLUMN02, COLUMN03) Values   (2, 1, 'SP', 'RJ', 'BEAN');
Insert into TBL_XPT03B (ID, XPT03A_ID, COLUMN01, COLUMN02, COLUMN03) Values   (3, 2, 'RJ', 'SP', 'MILK02');
COMMIT;

Insert into TBL_XPT04 (ID, XPT03_ID, XPT03B_ID, COLUMN01) Values   (1, 1, 1, 'VCXZ');
Insert into TBL_XPT04 (ID, XPT03_ID, XPT03B_ID, COLUMN01) Values   (2, 1, 2, 'ZXCV');
Insert into TBL_XPT04 (ID, XPT03_ID, XPT03B_ID, COLUMN01) Values   (3, 2, 3, 'ASDF');
COMMIT;


This scheme is just an example, which reflects various types of situations that I am having difficulty with how to export the data, for example  suppose I want to export from table TBL_XPT04 the ID line equal to 2

SQL> select *
  2     from tbl_xpt04 t
  3     where t.id = 2;
 
        ID   XPT03_ID  XPT03B_ID COLUMN01TBL_XPT01 ==> ID=1
---------- ---------- ---------- ------------------------------
         2          1          2 ZXCV

In this case I must export the following ROWS from the tables

TBL_XPT03B==>ID=2,
TBL_XPT03A==> ID =1,
 TBL_XPT03 ==> ID=1,
TBL_XPT02 ==> ID=1,
TBL_XPT01 ==> ID=1

In this example, I know what the FKs of the tables are, and would be able to start from the last table (TBL_XPT04) and go through all its ancestors, I would like to know if there is a way to be able to export a row and automatically identify a fk and find its parent and export your row too

   Suppose you have to export the row from any other table, and it has one or two FKs, and you need to identify all of your ancestors , and that on importing do this in the correct order, so that there is no integrity problem

In the other environment, some tables, the rows are deleted before the loading of these new rows, but there are some tables that the rows are not deleted, those that have a UK, for example the table TBL_XPT03B

in short it is this: Given a row of a table, I must export all its ancestors, so that the other environment can import in the correct order, is there a way to do this automatically? or I have to map all tables manually

forgot it
CREATE TABLE TBL_XPT01
(
  ID        NUMBER,
  COLUMN01  VARCHAR2(10 BYTE),
  COLUMN02  VARCHAR2(15 BYTE)
)
NOLOGGING
NOCOMPRESS
NOCACHE
MONITORING;


CREATE UNIQUE INDEX TBL_XPT01_PK ON TBL_XPT01
(ID)
NOLOGGING;

ALTER TABLE TBL_XPT01 ADD (
  CONSTRAINT TBL_XPT01_PK
  PRIMARY KEY
  (ID)
  USING INDEX TBL_XPT01_PK
  ENABLE VALIDATE);
 
CREATE TABLE TBL_XPT02
(
  ID        NUMBER,
  XPT01_ID  NUMBER,
  MANAGER   VARCHAR2(20 BYTE)
)
NOLOGGING
NOCOMPRESS
NOCACHE
MONITORING;


CREATE UNIQUE INDEX TBL_XPT02_PK ON TBL_XPT02
(ID)
NOLOGGING;

ALTER TABLE TBL_XPT02 ADD (
  CONSTRAINT TBL_XPT02_PK
  PRIMARY KEY
  (ID)
  USING INDEX TBL_XPT02_PK
  ENABLE VALIDATE);

ALTER TABLE TBL_XPT02 ADD (
  CONSTRAINT TBL_XPT02_R01
  FOREIGN KEY (XPT01_ID)
  REFERENCES TBL_XPT01 (ID)
  ENABLE VALIDATE);

CREATE TABLE TBL_XPT03
(
  ID        NUMBER,
  XPT02_ID  NUMBER,
  COLUMN01  VARCHAR2(10 BYTE),
  COLUMN02  VARCHAR2(15 BYTE)
)
NOLOGGING
NOCOMPRESS
NOCACHE
MONITORING;


CREATE UNIQUE INDEX TBL_XPT03_PK ON TBL_XPT03
(ID)
NOLOGGING;

ALTER TABLE TBL_XPT03 ADD (
  CONSTRAINT TBL_XPT03_PK
  PRIMARY KEY
  (ID)
  USING INDEX TBL_XPT03_PK
  ENABLE VALIDATE);

ALTER TABLE TBL_XPT03 ADD (
  CONSTRAINT TBL_XPT03_R01
  FOREIGN KEY (XPT02_ID)
  REFERENCES TBL_XPT02 (ID)
  ENABLE VALIDATE);
 
CREATE TABLE TBL_XPT03A
(
  ID        NUMBER,
  COLUMN01  VARCHAR2(10 BYTE),
  COLUMN02  VARCHAR2(10 BYTE)
)
NOLOGGING
NOCOMPRESS
NOCACHE
MONITORING;


CREATE UNIQUE INDEX TBL_XPT03A_PK ON TBL_XPT03A
(ID)
NOLOGGING;

ALTER TABLE TBL_XPT03A ADD (
  CONSTRAINT TBL_XPT03A_PK
  PRIMARY KEY
  (ID)
  USING INDEX TBL_XPT03A_PK
  ENABLE VALIDATE);
 
CREATE TABLE TBL_XPT03B
(
  ID         NUMBER,
  XPT03A_ID  NUMBER,
  COLUMN01   VARCHAR2(2 BYTE),
  COLUMN02   VARCHAR2(2 BYTE),
  COLUMN03   VARCHAR2(10 BYTE)
)
NOLOGGING
NOCOMPRESS
NOCACHE
MONITORING;


CREATE UNIQUE INDEX TBL_XPT03B_PK ON TBL_XPT03B
(ID)
NOLOGGING;

CREATE UNIQUE INDEX TBL_XPT03B_U01 ON TBL_XPT03B
(COLUMN01, COLUMN02)
NOLOGGING;

ALTER TABLE TBL_XPT03B ADD (
  CONSTRAINT TBL_XPT03B_PK
  PRIMARY KEY
  (ID)
  USING INDEX TBL_XPT03B_PK
  ENABLE VALIDATE,
  CONSTRAINT TBL_XPT03B_U01
  UNIQUE (COLUMN01, COLUMN02)
  USING INDEX TBL_XPT03B_U01
  ENABLE VALIDATE);

ALTER TABLE TBL_XPT03B ADD (
  CONSTRAINT TBL_XPT03B_R01
  FOREIGN KEY (XPT03A_ID)
  REFERENCES TBL_XPT03A (ID)
  ENABLE VALIDATE);

CREATE TABLE TBL_XPT04
(
  ID         NUMBER,
  XPT03_ID   NUMBER,
  XPT03B_ID  NUMBER,
  COLUMN01   VARCHAR2(30 BYTE)
)
NOLOGGING
NOCOMPRESS
NOCACHE
MONITORING;


CREATE UNIQUE INDEX TBL_XPT04_PK ON TBL_XPT04
(ID)
NOLOGGING;

ALTER TABLE TBL_XPT04 ADD (
  CONSTRAINT TBL_XPT04_PK
  PRIMARY KEY
  (ID)
  USING INDEX TBL_XPT04_PK
  ENABLE VALIDATE);

ALTER TABLE TBL_XPT04 ADD (
  CONSTRAINT TBL_XPT04_R01
  FOREIGN KEY (XPT03_ID)
  REFERENCES TBL_XPT03 (ID)
  ENABLE VALIDATE,
  CONSTRAINT TBL_XPT04_R02
  FOREIGN KEY (XPT03B_ID)
  REFERENCES TBL_XPT03B (ID)
  ENABLE VALIDATE);

 

 

This post has been answered by James Su on Jun 2 2021
Jump to Answer

Comments

Processing

Post Details

Added on May 31 2021
7 comments
107 views