export with referential integrity intact

[suggs]

Here's a bit of a doozie
I have written a an sql package to export data from tables owned by a schema entered at run time by the user. The script exports the data to a comma delimeted file this is fine but I have to take referential integrity into consideration and export the data in the order that it needs to be imported back into the database if needed. I know that I need to look at the user_constraints table but does anyone know how I deduce what order I need to export the data, I am only concerned on a table by table situation but I don't know how to see what foreign keys are associated with what tables. Basiccaly the first data written should be the ultimate parent and so on.
Any sugestions at all would be great.
Suggs.

 

[carp]

The two views you are interested in for the FK information are DBA_CONSTRAINTS and DBA_CONS_COLUMNS.

Also, it appears that you are assuming all tables will be in a simple hierarchy.
Have you given any thought as to how you will handle mutual references (e.g., Table A has a FK that references Table B and Table B has a FK that references Table A)?

The way that the Export utility gets around this is to write the code to create the tables without the FK constraints, write out the data, then write out ALTER TABLE commands to add the FK constraints AFTER the data is already in place.