| Grouping different records using SQL Loader [message #237542] |
Mon, 14 May 2007 18:15  |
krispenmetsa
Messages: 1
Registered: May 2007
|
Junior Member |
|
|
I intend to parse a text file using SQL loader. This file contains financial transaction information in various batches and each batch belongs to a customer. This file has 3 types of records.
1) Batch Header record that starts with character "1"
2) Detail record that starts with character "2"
3) Batch Trailer record that starts with character "3"
The file contains several batches and each batch has a Batch Header and Batch Trailer record with one or many Detail records between them. Each type of record has a different set of fields that are delimited by pipe (|) symbol. The Batch Trailer record will have a field that contains the number of Detail records enclosed in that particular batch.
I intend to load the file using SQL Loader to 3 separate temp tables - Batch_Header, Batch_Detail, Batch_Trailer. The main requirement is - I have to validate each batch by counting the number of Detail records in each batch and comparing it with the value in the Batch Trailer record (I intend to do this in a PL/SQL procedure after loading the file using SQL Loader and selecting file data from the temp tables). In order to do this I need a Batch Id that must be associated with all the 3 types of records that constitute each batch.
Can anyone tell me how to generate a Batch Id when the Batch Header is encountered and use it to insert with all the records in that batch? Please remember that this file will have multiple batches and the Batch Id needs to be incremented each time a new Batch Header record is encountered.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
