Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>There may be some tool that does it already, but to arbitrarily extract all rows tables from a starting table is a small development task in itself. I can't write the whole thing for you, but I can get you started - I started to write it, but after about 20 minutes, I realized it was a little more work that I wanted to commit to a unpaid answer.</p> <p>I can see it being done best by a recursive PL/SQL procedure that would use dbms_ouput and user_cons_columns &amp; user_constraints to create inserts statement for the source table. You can cheat a little by writing all the inserts as if the columns were char values, since Oracle will implicitly convert any char values to the right datatype, assuming your NLS parameters are identical on the source &amp; target system.</p> <p>Note, the package below will have problems if you have circular relationships in your tables; also, on earlier versions of Oracle, you may run out of buffer space with dbms_output. Both problems can be solved by inserting the generated sql into a staging table that has a unique index on the sql, and aborting the recursion if you get a unique key collision. The big time saver below is the MakeParamList function, which converts a cursor that returns a list of columns into either a comma separated list, or a single expression that will display the values of those columns in a quoted, comma separated form when run as the select clause in a query against the table.</p> <p>Note also that the following package won't really work until you modify it further (one of the reasons I stopped writing it): The initial insert statement generated is based on the assumption that the constraint_vals argument passed in will result in a single row being generated - of course, this is almost certainly not the case once you start recursing (since you will have many child rows for a parent). You'll need to change the generation of the first statement (and the subsequent recursive calls) to be inside a loop to handle the cases where the call to the first EXECUTE IMMEDIATE call generates multiple rows instead of a single one. The basics of getting it working are here, you just need to grind out the details and get the outer cursor working.</p> <p>One final note also: It is unlikely that you could run this procedure to generate a set of rows that, when inserted into a target system, would result in a "clean" set of data, since although you would get all dependent data, that data may depend on other tables that you didn't import (e.g., the first child table you encounter may have other foreign keys that point to tables unrelated to your initial table). In that case, you may want to start with the detail tables and work your way up instead of down; doing that, you'd also want to reverse the order to the statements you generated, either using a scripting utility, or by inserting the sql into a staging table as I mention above, with a sequence, then selecting it out with a descending sort.</p> <p>As for invoking it, you pass the comma separated list of columns to constrain as constraint_cols and the corresponding comma separated list of values as constraint_vals, e.g.:</p> <pre><code>exec Data_extractor.MakeInserts ('MYTABLE', 'COL1, COL2', '99, 105') </code></pre> <p>Here it is:</p> <pre><code>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 || ');'); -- Now we construct the constraint_vals parameter for subsequent calls: SELECT makeparamlist(CURSOR( SELECT column_name FROM user_cons_columns ucc , user_constraints uc WHERE uc.table_name = source_table AND ucc.constraint_name = uc.constraint_name ORDER BY position) , 1) INTO tempsql FROM DUAL; extractsql := 'SELECT ' || tempsql || ' FROM ' || source_table || ' WHERE ' || constraint_cols || ' = ' || constraint_vals; EXECUTE IMMEDIATE extractsql INTO childconstraint_vals; childconstraint_vals := childconstraint_vals; -- Now iterate over the dependent tables for this table -- Cursor on this statement: -- SELECT uc.table_name child_table, uc.constraint_name fk_name -- FROM user_constraints uc -- , user_constraints ucp -- WHERE ucp.table_name = source_table -- AND uc.r_constraint_name = ucp.constraint_name; -- For each table in that statement, find the foreign key -- columns that correspond to the rows -- in the parent table -- SELECT column_name -- FROM user_cons_columns -- WHERE constraint_name = fk_name --ORDER BY POSITION; -- Pass that columns into makeparamlist above to create -- the constraint_cols argument of the call below: -- makeinserts(child_table, ChildConstraint_cols, childconstrain_vals); END; END; END data_extractor; </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POExtracting rows from a DB including depedent rows
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USJeff Atwood
    1. USSteve Broberg
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POExtracting rows from a DB including depedent rows
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThanks for this excellent answer! I'll need some time to work through this, but I think it contains everything I need to get to my desired solution by myself.
      singulars
      1. PO
      1. USKarl Bartel
    2. CO+1 for all the effort, if nothing else!
      singulars
      1. PO
      1. USDCookie
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload