Reading a fixed-length flat file

[pnad]

There is a fixed length flat file - each 'record' in the file has a length of 80. I need to extract data from each record from different positions and format another fixed length flat file.

e.g: the input file looks like this:
74807932020002100010020808 0000002355 26240992 74807932020002100011020808 0000090000 262409
74807932020002100011020808 0000090000 26240993 74807932020002100012020808 0000078654 262409
74807932020002100012020808 0000078654 26240994 74807932020002100013020808 0000055023 262409

It has fillers in the beginning and in between. There is no specific record delimiter except that the length of each record is fixed (80). I need to extract data such as account number from pos 9 - 12, and then amount from pos 30 - 39 etc. and then I need to put all this data along with some addnl data in another output file. The output file needs to be in a totally different format but thats the second step. The first step is to read the file and extract data accordingly. How can I do this without using any temp tables in Oracle - is this possible using just stored procedures.

Please help !

Thanks.

 

[jimirvine]

Hi, I would suggest external tables. Here is a very quick (untested) example:

CREATE TABLE tst_tab (col1 varchar2(30), col2 varchar2(20), col3 varchar2(20))
ORGANIZATION EXTERNAL
  (TYPE ORACLE_LOADER
  DEFAULT DIRECTORY TST
  ACCESS PARAMETERS
    (RECORDS DELIMITED BY NEWLINE
    NOBADFILE
    NOLOGFILE
    fields terminated by ' '
    (col1 POSITION(1:26) CHAR,
    col2 POSITION(28:38) char,
    col3 POSITION(48:55) char
    ))
    LOCATION ('Myfile.dat'));

You will be required to set this up by creating (or having someone create) a directory object (this is an oracle object that 'points' to a directory on the Oracle Server) The data file must reside in that directory. i.e. I have a directory (folder) on my windows server at 'C:/tst' I created a directory object called tst to point to that OS Directory. I can now reference the OS Directory in Oracle by using the Oracle Directory Object.
The rest is down to you checking out the available syntax for creating External Tables (You can get the documentation at

http://tahiti.oracle.com)