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);