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). 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. 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. 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. 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 & target system. I can see it being done best by a recursive PL/SQL procedure that would use dbms_ouput and user_cons_columns & user_constraints to create inserts statement for the source table. 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. 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.
0 Comments
Leave a Reply. |