Home » RDBMS Server » Server Utilities » Grouping different records using SQL Loader
Grouping different records using SQL Loader [message #237542] Mon, 14 May 2007 18:15 Go to next message
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.







Re: Grouping different records using SQL Loader [message #237583 is a reply to message #237542] Tue, 15 May 2007 01:00 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use external table instead of SQL*Loader then you have the power of SQL and PL/SQL and then it is easy to achieve what you want.

Regards
Michel
Previous Topic: Ora errors while calling a funtion
Next Topic: to insert data based on the count of col's data
Goto Forum:
  


Current Time: Sun Dec 04 12:20:22 CST 2016

Total time taken to generate the page: 0.07662 seconds