How to do incremental updates (SQL*Loader?)?

I'm supporting a site that will be keeping an Oracle database synchronized with another database running on a VMS machine. The've got programs to extract the necessary information from the VMS db, and automatically create .sql, .ctl, and .dat files, which they ftp to the Unix box (an HP 9000 K210 running HP-UX 10.10 and Oracle 7.3.2) and load using SQL*Loader. This is working great for the initial transfer.

The problem is keeping things up to date. These tables are large, and some of them take hours to load. They're going to want to do a nightly update, with only the changed data. They can have their extract programs output .ctl and .dat files containing only new data in the .dat file, in a format compatible with SQL*Loader and the .ctl file. However, this will be a mix of new records and existing records whose data has been altered. None of the SQL*Loader options (INSERT INTO TABLE, REPLACE, TRUNCATE, etc.) seem to work with this.

Any suggestions?

• Cos (Ofer Inbar) -- cos_at_leftbank.com cos_at_cs.brandeis.edu
• The Left Bank Operation -- lbo_at_leftbank.com http://www.leftbank.com/ try some greeen eggsses and sssspam, my precioussssss...
  • Gollum-I-Am