The Mainframe systems put a file out each night containing data that needs to be downloaded to Oracle Daily INITIAL LOAD The Input file (from the mainframe) is read and all records are put into as "shadow" file which is kept out on the server. This "shadow" file is read and each Row is inserted into the database. SUBSEQUENT LOADS The Input file (from the mainframe) is read in and each record is compared with the "shadow" file from yesterday's run. IF the Row is not on the shadow file THEN it is placed in an "INSERT" file IF the Row is on the shadow file THEN each field is compared IF there is any difference in any of the fields This Row is placed in an "UPDATE" file IF there are no differences Ignore this Row IF the Row is on shadow file - but NOT on the input file THEN this Row is put in the "DELETE" file Now, we read these 3 files in (INSERT, UPDATE, DELETE) and take them against the actual Database. We first the "INSERT" file. A select statement is done to verify that the Row is not already on the database. IF it is already on the database THEN the proceed to the update section of the program ELSE They INSERT the Row. We read the "UPDATE" file A select statement is done to verify that the Row is still on the database. IF it is NOT on the Database, They go back to the Insert Routine IF it is on the Database They compare each field to see IF there are any differences IF there are differences An UPDATE statement is performed setting all fields that changed IF there are no differences Ignore this Row. We Read the "DELETE" file A select statement is done to verify that the Row is still there. IF it is still on the Database, DELETE the Row IF it is not on the database Ignore the Row. Let me know the best methodology within oracle 9i With Regards, Prashant Shah

Software/Hardware used: