There may be some tool that does this already, but to arbitrarily extract all the row tables from the start table is a small development task in itself. I can’t write all this for you, but I can start with you - I started writing, but after about 20 minutes I realized that it was a little work that I wanted to do with an unpaid answer.
I see that this is done best with the recursive PL / SQL procedure, which will use dbms_ouput and user_cons_columns and user_constraints to create the inserts statement for the source table. You can trick it a bit by writing all the inserts as if the columns were char values, since Oracle will implicitly convert any char values to the right data type, assuming that your NLS parameters are identical in the source and target systems.
Note that there will be problems in the package below if you have circular relationships in your tables; In addition, in earlier versions of Oracle, you can exit the buffer using dbms_output. Both problems can be solved by inserting the generated sql into a staging table with a unique index in sql and interrupting the recursion if you get a unique key clash. A screensaver with a longer time below is the MakeParamList function, which converts a cursor that returns a list of columns to a comma-separated list, or a single expression that will display the values of these columns in a quoted form with a comma when run as select in a table query.
Please also note that the following package will not work until you change it (one of the reasons I stopped writing it): The initial initialization statement, based on the assumption that the constraint_vals argument is passed, will result in one line being generated - of course, this is almost not the case as soon as you start recursing (since you will have many child lines for the parent). You will need to change the generation of the first statement (and subsequent recursive calls) inside the loop in order to handle cases where calling the first EXECUTE IMMEDIATE call generates several rows instead of one. The basics of its work here, you just need to grind the details and make the external cursor work.
Finally, note: it is unlikely that you can run this procedure to create a set of rows that, when inserted into the target system, will lead to “clean” data sets, because although you get all the dependent data, the data may depend on other tables that you not imported (for example, the first child table you encounter may have other foreign keys that point to tables that are not related to your original table). In this case, you can start with detailed tables and work up, not down; by doing this, you also want to reverse the order for the instructions you created, either by using the scripting utility, or by inserting sql into the staging table, as I mentioned above, with a sequence, and then selecting it with a descending sort.
As for calling it, you pass a list of columns separated by commas, to limit as constraint_cols and the corresponding list of values separated by commas, as restrictions, for example:
exec Data_extractor.MakeInserts ('MYTABLE', 'COL1, COL2', '99, 105')
Here he is:
CREATE OR REPLACE PACKAGE data_extractor IS TYPE column_info IS RECORD( column_name user_tab_columns.column_name%TYPE ); TYPE column_info_cursor IS REF CURSOR RETURN column_info; FUNCTION makeparamlist( column_info column_info_cursor , get_values NUMBER ) RETURN VARCHAR2; PROCEDURE makeinserts( source_table VARCHAR2 , constraint_cols VARCHAR2 , constraint_vals VARCHAR2 ); END data_extractor; CREATE OR REPLACE PACKAGE BODY data_extractor AS FUNCTION makeparamlist( column_info column_info_cursor , get_values NUMBER ) RETURN VARCHAR2 AS BEGIN DECLARE column_name user_tab_columns.column_name%TYPE; tempsql VARCHAR2(4000); separator VARCHAR2(20); BEGIN IF get_values = 1 THEN separator := ''''''''' || '; ELSE separator := ''; END IF; LOOP FETCH column_info INTO column_name; EXIT WHEN column_info%NOTFOUND; tempsql := tempsql || separator || column_name; IF get_values = 1 THEN separator := ' || '''''', '''''' || '; ELSE separator := ', '; END IF; END LOOP; IF get_values = 1 THEN tempsql := tempsql || ' || '''''''''; END IF; RETURN tempsql; END; END; PROCEDURE makeinserts( source_table VARCHAR2 , constraint_cols VARCHAR2 , constraint_vals VARCHAR2 ) AS BEGIN DECLARE basesql VARCHAR2(4000); extractsql VARCHAR2(4000); tempsql VARCHAR2(4000); valuelist VARCHAR2(4000); childconstraint_vals VARCHAR2(4000); BEGIN SELECT makeparamlist(CURSOR(SELECT column_name FROM user_tab_columns WHERE table_name = source_table), 0) INTO tempsql FROM DUAL; basesql := 'INSERT INTO ' || source_table || '(' || tempsql || ') VALUES ('; SELECT makeparamlist(CURSOR(SELECT column_name FROM user_tab_columns WHERE table_name = source_table), 1) INTO tempsql FROM DUAL; extractsql := 'SELECT ' || tempsql || ' FROM ' || source_table || ' WHERE (' || constraint_cols || ') = (SELECT ' || constraint_vals || ' FROM DUAL)'; EXECUTE IMMEDIATE extractsql INTO valuelist; -- This prints out the insert statement for the root row DBMS_OUTPUT.put_line(basesql || valuelist || ');');