Forum Stats

  • 3,781,431 Users
  • 2,254,519 Discussions
  • 7,879,691 Comments

Discussions

Categories

Export table row and its ancestors

muttleychess
muttleychess Member Posts: 1,496 Bronze Badge
edited May 31, 2021 1:07PM in SQL & PL/SQL

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

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

 

 


Tagged:
· Share on TwitterShare on Facebook

Best Answer

  • James Su
    James Su Member Posts: 1,114 Gold Trophy
    Accepted Answer 
    with d1 as (
 select p_pk.table_name parent_table
      ,listagg(pc.column_name,',') within group(order by pc.position) parent_pkey_col
      ,c.table_name child_table
      ,listagg(cc.column_name,',') within group(order by cc.position) child_fkey_col
  from user_constraints p_pk    ---- parent's parimary key
       join user_cons_columns pc ---- parent's parimary key column
           on p_pk.owner=pc.owner and p_pk.table_name=pc.table_name and p_pk.constraint_name=pc.constraint_name
       join user_constraints c ---- child's foreign key
            on p_pk.owner=c.r_owner and p_pk.constraint_name=c.r_constraint_name
       join user_cons_columns cc  ---- child's foreign key column
            on c.owner=cc.owner and c.table_name=cc.table_name and c.constraint_name=cc.constraint_name
where c.constraint_type='R'
group by c.constraint_name,p_pk.table_name,c.table_name
)
,d as (
select * from d1
union all
select null,null,parent_table,parent_pkey_col from d1 where not exists (select null from d1 d2 where d2.child_table=d1.parent_table)
)
,t(sql1,sql2,parent_table,parent_pkey_col,lvl) as (
select 'select * from '||child_table||' where id = 2'
      ,'select '||child_fkey_col||' from '||child_table||' where id = 2'
      ,parent_table
      ,parent_pkey_col
      ,1
  from d
 where child_table=upper('tbl_xpt04')
union all
select 'select * from '||t.parent_table||' where ('||t.parent_pkey_col||') in ('||t.sql2||')'
      ,'select '||d.child_fkey_col||' from '||d.child_table||' where ('||t.parent_pkey_col||') in ('||t.sql2||')'
      ,d.parent_table
      ,d.parent_pkey_col
      ,lvl+1
  from t,d
 where t.parent_table=d.child_table
)
cycle parent_table,lvl set cycle_flag TO 'Y' DEFAULT 'N'
select sql1 from t group by sql1 order by max(lvl) desc

    Now you have some select statements. What are you going to do with them? I still don't get the idea how you can load one single text file into different target tables. Are you going to create a loader tool? Isn't it easier to generate insert statements?

    muttleychess
    · Share on TwitterShare on Facebook

Answers

  • James Su
    James Su Member Posts: 1,114 Gold Trophy

    It's possible to use the data dictionary views for help. What is your goal here? Are you trying to make a code generator to produce those select statements? Or do you need to generate insert statements?

    If your table contains self reference, like manager-employee hierarchy, then it becomes tricky. I've seen reference cycle in some models, that is difficult too.

    · Share on TwitterShare on Facebook
  • muttleychess
    muttleychess Member Posts: 1,496 Bronze Badge 
    thanks for the answer, no i don't need the insert commands, just the data in text format, similar to the one generated by the sql loader, but in the correct order so that there is no error in the target environment


    · Share on TwitterShare on Facebook
  • James Su
    James Su Member Posts: 1,114 Gold Trophy

    So you want to make a data unloader, this is not trivial work. It takes a lot of time to implement. You will need to generate dynamic queries in correct order, execute them, parse the results and write into target files. Since you mention "correct order", do you mean putting data from different tables into one single text file? Then how will you load into the target db? How can it recognize which table to load into? If some of the data already exists on the target db, how do you guarantee there's no error when loading into the target db?

    · Share on TwitterShare on Facebook
  • muttleychess
    muttleychess Member Posts: 1,496 Bronze Badge


    Thank you

    I imagine so, most tables will be cleared (delete or truncate) in the target environment, only those with UK should be preserved, so I imagined putting some line in the text identifying the table name and whether it is PK or UK for be used, my difficulty is finding the correct order of inserts (updates)

    · Share on TwitterShare on Facebook
  • James Su
    James Su Member Posts: 1,114 Gold Trophy

    Here's a start point:

    with d as (
 select p_pk.table_name parent_table
      ,listagg(pc.column_name,',') within group(order by pc.position) parent_pkey_col
      ,c.table_name child_table
      ,listagg(cc.column_name,',') within group(order by cc.position) child_fkey_col
  from user_constraints p_pk    ---- parent's parimary key
       join user_cons_columns pc ---- parent's parimary key column
           on p_pk.owner=pc.owner and p_pk.table_name=pc.table_name and p_pk.constraint_name=pc.constraint_name
       join user_constraints c ---- child's foreign key
            on p_pk.owner=c.r_owner and p_pk.constraint_name=c.r_constraint_name
       join user_cons_columns cc  ---- child's foreign key column
            on c.owner=cc.owner and c.table_name=cc.table_name and c.constraint_name=cc.constraint_name
where c.constraint_type='R'
group by c.constraint_name,p_pk.table_name,c.table_name
)
,t(sql1,sql2,parent_table,parent_pkey_col,lvl) as (
select 'select * from '||child_table||' where id = 2'
      ,'select '||child_fkey_col||' from '||child_table||' where id = 2'
      ,parent_table
      ,parent_pkey_col
      ,1
  from d
 where child_table=upper('tbl_xpt04')
union all
select 'select * from '||d.child_table||' where ('||t.parent_pkey_col||') in ('||t.sql2||')'
      ,'select '||d.child_fkey_col||' from '||d.child_table||' where ('||t.parent_pkey_col||') in ('||t.sql2||')'
      ,d.parent_table
      ,d.parent_pkey_col
      ,lvl+1
  from t,d
 where t.parent_table=d.child_table
)
cycle parent_table,lvl set cycle_flag TO 'Y' DEFAULT 'N'
select sql1 from t group by sql1 order by max(lvl) desc

    output:

    select * from TBL_XPT02 where (ID) in (select XPT02_ID from TBL_XPT03 where (ID) in (select XPT03_ID from TBL_XPT04 where id = 2))

    select * from TBL_XPT03B where (ID) in (select XPT03B_ID from TBL_XPT04 where id = 2)

    select * from TBL_XPT03 where (ID) in (select XPT03_ID from TBL_XPT04 where id = 2)

    select * from TBL_XPT04 where id = 2


    You still have a long way to go, good luck.

    · Share on TwitterShare on Facebook
  • muttleychess
    muttleychess Member Posts: 1,496 Bronze Badge

    Very, very goodl thank how can I to include the table TBL_XPT01 ?

    · Share on TwitterShare on Facebook
  • James Su
    James Su Member Posts: 1,114 Gold Trophy
    Accepted Answer 
    with d1 as (
 select p_pk.table_name parent_table
      ,listagg(pc.column_name,',') within group(order by pc.position) parent_pkey_col
      ,c.table_name child_table
      ,listagg(cc.column_name,',') within group(order by cc.position) child_fkey_col
  from user_constraints p_pk    ---- parent's parimary key
       join user_cons_columns pc ---- parent's parimary key column
           on p_pk.owner=pc.owner and p_pk.table_name=pc.table_name and p_pk.constraint_name=pc.constraint_name
       join user_constraints c ---- child's foreign key
            on p_pk.owner=c.r_owner and p_pk.constraint_name=c.r_constraint_name
       join user_cons_columns cc  ---- child's foreign key column
            on c.owner=cc.owner and c.table_name=cc.table_name and c.constraint_name=cc.constraint_name
where c.constraint_type='R'
group by c.constraint_name,p_pk.table_name,c.table_name
)
,d as (
select * from d1
union all
select null,null,parent_table,parent_pkey_col from d1 where not exists (select null from d1 d2 where d2.child_table=d1.parent_table)
)
,t(sql1,sql2,parent_table,parent_pkey_col,lvl) as (
select 'select * from '||child_table||' where id = 2'
      ,'select '||child_fkey_col||' from '||child_table||' where id = 2'
      ,parent_table
      ,parent_pkey_col
      ,1
  from d
 where child_table=upper('tbl_xpt04')
union all
select 'select * from '||t.parent_table||' where ('||t.parent_pkey_col||') in ('||t.sql2||')'
      ,'select '||d.child_fkey_col||' from '||d.child_table||' where ('||t.parent_pkey_col||') in ('||t.sql2||')'
      ,d.parent_table
      ,d.parent_pkey_col
      ,lvl+1
  from t,d
 where t.parent_table=d.child_table
)
cycle parent_table,lvl set cycle_flag TO 'Y' DEFAULT 'N'
select sql1 from t group by sql1 order by max(lvl) desc

    Now you have some select statements. What are you going to do with them? I still don't get the idea how you can load one single text file into different target tables. Are you going to create a loader tool? Isn't it easier to generate insert statements?

    muttleychess
    · Share on TwitterShare on Facebook