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
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: 65143
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


[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: 2102
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

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.


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

Report message to a moderator

Previous Topic: Return array values to refcursor
Next Topic: select rows
Goto Forum:

Current Time: Sat Aug 19 02:24:32 CDT 2017

Total time taken to generate the page: 0.09806 seconds