2

I want to dump some data out of a database based on only some of the data that lies in the DB. Say I have a customer table and an order table. order has a Foreign-Key to customer. An order is built out of many orderline (own table) which in turn have item (own table). Each are linked via FK.

In order to extract test data out of this DB, I want to get the data in a way I can process it, for say 5 orders. The data should contain everything I need to use these orders in my application (e.g. the customers, the items, the orderlines). And I only want to specify some conditions based on the orders I want.

If it does matter, the environment is:

  • JavaEE5 on a JBoss
  • Hibernate
  • Oracle 11g

I'm not opposed to use any language or tool that lets me get this data in a simple way.

Update: I found the following question which deals with a similar problem.

Community
  • 1
  • 1
boutta
  • 24,189
  • 7
  • 34
  • 49

1 Answers1

1

It's a good question.

Oracle released a test management pack in 12c that i think takes care of that - I haven't got into it so much but maybe it's compatible with 11g as well. Informatica has a similar product.

If you don't want to use a pricey solution you may be able to use the COPY command. The principal of the sqlplus COPY command is similar to the old exp/imp but it allows you to export using a query.

1.Export lookup tables.

2.Export main (fact) table with a filter. 

copy from=user/pass@prod to user/pass@test create big_fact using select * from big_Fact where update_date > '01/01/2013'

3.Copy related table by joining to the base table

copy from=user/pass@prod to user/pass@test create related_table1 using select related_table1.* from related_table1 join big_fact on (join_condition) where big_fact.update_date > '01/01/2013'

And so on ...

This is not a robust solution and it's compatible for a schema with very few core tables and simple constraints.

haki
  • 9,389
  • 15
  • 62
  • 110