Re: Using DBLINK to update data in a production environment

From: Tim X <timx_at_spamto.devnul.com>
Date: 04 Mar 2003 18:44:05 +1100
Message-ID: <87el5n7f0a.fsf@tiger.rapttech.com.au>

>>>>> "WillBrenn" == WillBrenn <bill_brennum_at_grainger.com> writes:

 WillBrenn> I am involved in a project where we are considering
 WillBrenn> linking SAP to an external Oracle database.  The method we
 WillBrenn> would like to use for the link is Oracle's DBLINK
 WillBrenn> functionality.  I would like to know if anyone is
 WillBrenn> currently using DBLINK to maintain data on a remote
 WillBrenn> database in a production environment.  The info that I
 WillBrenn> would like to obtain is:

 WillBrenn> 1.  The versions of Oracle used.  2.  Is there Insert,
 WillBrenn> Update and Delete activity against the remote DB.  2.  How

 WillBrenn> long in production. 3. Issues if any. 4. Is there any  WillBrenn> connection to an SAP system?

Yes, we do this. The link is from an Oracle 8.1.7 db to an Oracle 7.3.4. We mainly retrieve data from the 7.3.4 db, but some updates are written back from the 8.1.7 db to the 7.3.4.

The only real issue we had was with the use of a ssequence in the update back to the 7.3.4 db. Rather than doing

update table
  set column = sequence.nextval,
  ....

we had to do a

select sequence.nextval
  into var
  from dual

update table
  set column = var,
  ...

This was necessary because we wanted to have the sequence defined on the 8.1.7 db and the only way to get that to work over a db link with the sequence actually in the update statement is to set global_names=true. I guess we could have defined the sequence on the remote db and it would have been OK, but we didn't want to do that as we preferred to have all the control additional objects defined on the 8.1.7 db.

The only other thing I would do is make sure you connect from the later versioned db to the earlier one rather than doing it the other way around. My justification for this is I feel it is more likely the later database knows more about the features/design/implementation of an older database than an older db would know about a later one, plus it means our plsql is running on the 8.1.7 db, so our packages are able to take advantage of later plsql constructs/features.

The data retrieved does involve a couple of joins, but this hasn't caused any problems. The system has been running in production for about 10 months now. No problems (yet!). Data is transferred at least once a day.

sorry, we don't use SAP (and from what I've read, I'm very happy about that!).

Tim

-- 
Tim Cross
The e-mail address on this message is FALSE (obviously!). My real e-mail is
to a company in Australia called rapttech and my login is tcross - if you 
really need to send mail, you should be able to work it out!

Received on Tue Mar 04 2003 - 01:44:05 CST