Home » SQL & PL/SQL » SQL & PL/SQL » Export rows with all dependent rows (Oracle 9i)
icon5.gif  Export rows with all dependent rows [message #363394] Sat, 06 December 2008 12:31 Go to next message
frobfoo
Messages: 1
Registered: December 2008
Junior Member
Hello everyone!

I have a rather complex database schema with a lot of parent-child-relationships. On demand, I want to export a single row identified by its primary key from, let's say table 'TABLE_A' to another Oracle database which has got the same schema but is located on a different host.

The problem here is that I'd like to "automatically" locate all of the dependent objects (foreign key relationships in 'TABLE_A') so that those rows get exported as well!

Is sth like this possible using just database features?

Thanks for any hints!
Re: Export rows with all dependent rows [message #363395 is a reply to message #363394] Sat, 06 December 2008 13:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Nothing built-in.
You have to create your own.

The question is also do you want:
- the row and all its children
- the row and all its parents
- both

Regards
Michel

[Updated on: Sat, 06 December 2008 13:05]

Report message to a moderator

Re: Export rows with all dependent rows [message #364281 is a reply to message #363395] Sun, 07 December 2008 17:15 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
If this is a one time deal, then you should look at your database tools. It is possible one of them offers such a feature. For example TOAD has an export data feature. I have never used it so it may not do exactly what you want.

As an alternative, you could roll your own. I have done this before many years ago when the tools available to DBAs and Developers were not as advanced. It is a matter of following the constraints around in your database. Areas where you will have some issues are

Quote:
1) dealing with tables that foreign key back to themselves.
2) dealing with rows that refernence each other either directly, or through a chain of references.
3) dealing as always witih longs, and clobs
4) dealing with foreign keys that allow nulls


Other than that it is pretty simple code to write, and a good exercies for teaching newbies to use the data dictionary to save time, and to appreciate the value of metadata.

Kevin

[Updated on: Sun, 07 December 2008 17:19]

Report message to a moderator

Previous Topic: Return array values to refcursor
Next Topic: Need help on inserting price values
Goto Forum:
  


Current Time: Fri Dec 09 00:13:10 CST 2016

Total time taken to generate the page: 0.11292 seconds