Home » RDBMS Server » Server Utilities » External tables Fixed Format Data (Oracle 10)
External tables Fixed Format Data [message #354130] Thu, 16 October 2008 08:59 Go to next message
Jaffa6
Messages: 1
Registered: October 2008
Location: norfolk, england
Junior Member
I need to create an external table from a flat fixed format data file, the data is not comma delimited or anything like that. Is this possibel and if so how do I do it?
Also there are three records types in the flat file (one header record, one footer record and multiple transaction records). Is it possible to define all three record types in a single external table rather than three external tables?
Bytes 1-2 define the record type, bytes 6-25 hold the record key in the transaction records.

Example file

012820080229
10MPY58001044 98255 1IX20080229
10MPY58002006 90689 1IX20080229
10MPY58002009 90689 1IX20080229
10MPY58006409 90969 1IX20080229
10MPY58006410 90969 1IX20080229
10MPY58006442 90922 1IX20080229
10MPY58006443 90922 1IX20080229
10MPY58006445 90922 1IX20080229
10MPY58006446 90922 1IX20080229
10MPY58006449 90922 1IX20080229
99000000010
Re: External tables Fixed Format Data [message #354154 is a reply to message #354130] Thu, 16 October 2008 11:40 Go to previous message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
Quote:
I need to create an external table from a flat fixed format data file, the data is not comma delimited or anything like that. Is this possibel and if so how do I do it?


Yes, use POSITION.

Quote:
Also there are three records types in the flat file (one header record, one footer record and multiple transaction records). Is it possible to define all three record types in a single external table rather than three external tables?
Bytes 1-2 define the record type, bytes 6-25 hold the record key in the transaction records.


You can use the WHEN clause to load only certain rows based on the first two bytes.

Syntax and examples for external tables are available in the online documentation.
Previous Topic: DATABASE ANALYZE ISSUE
Next Topic: Export/import full database query
Goto Forum:
  


Current Time: Thu Dec 08 10:19:37 CST 2016

Total time taken to generate the page: 0.07567 seconds