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!

Need to generate CREATE TABLE scripts and INSERT scripts in order

Albert ChaoMar 12 2022
CREATE TABLE a (
    a_id  NUMBER(10),
    city  VARCHAR2(255),
    CONSTRAINT pk_a PRIMARY KEY ( a_id )
);

CREATE TABLE b (
    b_id  NUMBER(10),
    a_id  NUMBER(10),
    city  VARCHAR2(255),
    CONSTRAINT pk_b PRIMARY KEY ( b_id ),
    CONSTRAINT fk_b_a FOREIGN KEY ( a_id )
        REFERENCES a ( a_id )
);

INSERT INTO a VALUES(1,'Mumbai');
INSERT INTO a VALUES(2,'Pune');
INSERT INTO b VALUES(1,1,'Mumbai');
INSERT INTO b VALUES(2,2,'Pune');

COMMIT;

I need to generate a create table DDL scripts in order of parent-child. Suppose, I have two tables A and B then the script should give me DDL for table A and then for B.
From the below query I can get the parent-child tables but how to generate DDL out of it.

SELECT p.table_name PARENT_TABLE, c.table_name CHILD_TABLE
FROM dba_constraints p, dba_constraints c
WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U')
AND c.constraint_type = 'R'
AND p.constraint_name = c.r_constraint_name
AND p.table_name = UPPER('A')
and p.owner='TAM';

And same for INSERT DML scripts. I need to have the DML scripts in the order of parent-child. I am stuck in generating the scripts.

Comments
Post Details
Added on Mar 12 2022
12 comments
8,458 views