At 05:59 PM 26/01/2003 +0000, Dave Bullard wrote:

>So I try to use DataPump.

Dave, the scenario below isn't a DataPump situation...

>I have a table of 'bookings'
>I have a table of 'addresses'; a table of 'people'; a table of 'items'
>Each booking can have one or more 'addresses'
>each address one or more people.
>each person one or more items.
>['As I was going to St Ives' etc].
>
>So I make a Master-detail-subdetail-subsubdetail link between the
>tables then I only have to select from bookings where booknum = 12345
>and I have the lot contrained.
>
>Then I send the booking record, then the first address record then
>the first person at that first address, then that person's first
>item, then his second item ..etc Then the second person at the first
>address. And so on in a nested fashion.

I guess this thread is now in one big woolly mess. Where do you "send"
these data?

>I can do this in a 'while not eof .... next' loop with loops within
>loops, but how to do it with a datapump?

You would typically use a datapump for a non-interactive procedure, which
is what appeared to be the case in your previous description. Now, the
scenario above has me totally confused. I'll try to finger it and you can
correct my wrong assumptions...

This structure above is a query over tables in database B.
You want an interactive user to pinpoint one complete Bookings structure
and copy it across to exactly matching tables in database A. Database A
has different primary keys on all tables, but you want to preserve the
relationships as formed in database B.

Please answer this question clearly.

>Or is there another way...perhaps not enforcing the ref integrty
>until all is sent?
>
>If i have 4 datapumps One for each table can I break into the
>bookings datapump flow at 'after fetch row'or somewhere to set the
>address datapump going etc etc ?

Not. Datapump maps columns in a source set with columns in a target
set. What you appear to have here is a replication system that lacks one
essential: global keys. If this is the case, then you have to get your db
structures sorted before you try to implement something.

>ANother problem I cannot see my way around with a datapump is if I
>use a trigger to set the primary keys at the server how can I get the
>values to put as foreign keys in the level below ?

Exactly. With a datapump or anything else, this isn't "another thing", but
the main thing you need to address. There's not enough detail so far about
the timing of this replication operation, but the IBO replication
components provide good encapsulation for what I think you want to do.

But I emphasise the need to get the db structures right first.

Helen