Oooh, Shockerman, dealing with text files in sql is ugly. There are a few approaches you could try, but none are very elegant and you still have a lot of coding to do:

1. I would write something (outside of Oracle and sql) to convert the data to fixed length fields. Believe me, doing that will make your life easier. Then create a temporary table to hold the raw data and use sqlloader to put the data into the table. Write a pl/sql program to read the rows from the temporary table, do whatever checks and data manipulations needed, and update your good table from there.

2. If you don't want to convert your input data into fixed length fields, you could try loading the data into a temporary table that has one huge VARCHAR2 or LONG field. Then write a pl/sql program, using functions such as translate, rtrim, substr, etc, to delineate the data and update the good table.

3. Try www.knightwriters.com/kwshelp.htm for another way to access text files in sql. It does involve messing with the initORA files and I'm always reluctant to do that. But it may be helpful.

If I think of any other suggestions I'll let you know, but it's my humble opinion that you'll be better off using method #1. It just involves a little front end work of massaging the data into fixed length fields. Once that's done, the rest is easy. Good luck!

You might want to give the folks at DataJunction a call. They have a (pretty cheap) product that does a lot of transformations. -- dba