SQL*Loader and DB sequences
417676Mar 24 2004 — edited Mar 24 2004OK, here's the story. I have a set of flat files that I need to load into Oracle tables. The data files consist of a header record, then several detail records, another header record, then several more detail records, another header record, several detail records, etc... For loading, I need to load the header record data into two different tables, and then the detail records get loaded into a third table. I want to key them up using a sequence.NEXTVAL/CURRVAL structure on the DB as there are other data sources using the same target tables and we don't want any key conflicts. So each 'set' of data remains keyed together throughout the process. I have the control file set up so that it loads the primary header record and uses the expression seq.NEXTVAL on the insert. Then the insert on the secondary header table and the detail records all use expression seq.CURRVAL so they would have the same key value as the primary header record, thus ending up keyed together in a set. My first sample data file has 3 header records, with 10 details for each header. (header, 10 detail, header, 10 details, header, 10 details). The problem I am seeing is that the primary header records all have the proper key values (57,58,59). However, all 3 secondary headers and all 30 detail records have been keyed to the last set (59). It would appear that these loads are not happening in the same order that the source data records are read in. It's not getting the CURRVAL for the secondary headers and details until it's already loaded the third primary header, and it's using that CURRVAL.
If I try it with 50 detail records per header, it gets even stranger. The three primary headers are all coded right, the first two secondary headers are coded to the second set, and the third secondary header is coded correctly. The detail records are in worse shape, with none coded to the first set, the 50 that belong to the first set and 12 that belong to second set are coded to the second set, and the other 38 that belong in the second set and all 50 for the third set getting coded for the third set. It's committing after every 64 records, if it matters.
Anyone have an idea why SQL*Loader doesn't appear to process the data in the same order that it reads it in?