CODEWITHSUNDEEP
oracle-databasetestingmockingdbunit
Cosmin Cosmin
Cosmin Cosmin

Reputation: 1556

Generate oracle database data automatically via FKs whole hierarchy


Is there a tool (for Oracle) that will generate data to all referenced tables starting from the table you want.
Example:

Table

ORDER has 3 not null FKs (CLIENT, PRODUCT, COUNTRY)
CLIENT has 2 not null FKs (COUNTRY, COMPANY)
  COMPANY has 2 not null FKs (COUNTRY, CONTINENT)
COUNTRY has 1 not null FK (CONTINENT)
PRODUCT has 5 not null FKs (MATERIAL, SIZE, ...
 MATERIAL has 2 not null FKs (...

And the hierarchy keeps going.
I just want to add an ORDER in my Database, to test something (with DBUnit), but I don't want to add values in 100 tables.
The generated data could be dummy: 0 for numbers, X for char/varchars, sysdate for date, systimestamp for timestamp. Only the FKs would need to be consistent.

It should work across Schemas (you can have SCHEMA1.FK to SCHEMA2.TABLE).
It should prompt the user for input for columns with Check Constraints (other than 'IS NOT NULL') and present them the text of the Check Constraint.
It should refresh after insert (because maybe there are triggers there).

Upvotes: 0

Views: 78

Answers (1)

TenG
TenG

Reputation: 4004

Short answer - I don't think so, at least not in the times I needed to do this.

I resorted to writing a script to do this.

I won;t give you the code, but here is what I wrote in outline:

  1. A generic function that accepted a table name, owner, and where clause as inpits, and returned XMLTYPE of data from XMLAGG/XMLELEMENT based queries on the table.
  2. A function that return a list of FK children tables for the given owner/table.
  3. Given 1 and 2, you can construct a recursive procedure that has a starting point (table and data/where clause) and then descends down the tree.

Each return adds the XMLTYpe returned it its XMLdata.

The end result is an XMLTYpe showing the recursive data, in a form that you should be able to process to recursively INSERT.

The XMLTYpe is then written to a file.

I did another similar one some years previously with a similar process but this copied data across a database link.

It took me a couple of days to get it all working, but saved a lot of time in support when we needed to reproduce issues in production.

Upvotes: 1

Related Questions