Extracting rows from a DB including depedent rows

Question

I'd like to generate insert-strings for a row in my Oracle database including all its dependent rows in other tables (and their dependent rows).

Example:

CREATE TABLE a (
  a_id number PRIMARY KEY,
  name varchar2(100)
);
CREATE TABLE b (
  b_id number PRIMARY KEY,
  a_id number REFERENCES a(a_id)
);

When I extract the row from a with a_id = 1, the result should be an insert-string for that row and dependent rows:

INSERT INTO a(a_id, name) VALUES (1, 'foo');
INSERT INTO b(b_id, a_id) VALUES (1, 1);
INSERT INTO b(b_id, a_id) VALUES (2, 1);
INSERT INTO b(b_id, a_id) VALUES (3, 1);

The reason why I want to do this is, that I have large database with many different tables and constraints between then and I'd like to extract a small subset of the data as test data.

Answer 1

I just use plain old SQL to do these tasks - use the select statements to generate your inserts:

set pagesize 0
set verify off

  SELECT 'INSERT INTO a(a_id, name) VALUES ('
         || a_id || ', '
         || '''' || name || ''');'
    FROM a
   WHERE a_id = &&1;

  SELECT 'INSERT INTO b(b_id, a_id) VALUES ('
         || b_id || ', '
         || a_id || ');'
    FROM b
   WHERE a_id = &&1;