External Tables (Trimming Data)

Hello to all! This is a great group. Thanks in advance.

I was wondering if any knows how to trim data (not whitespace) with external tables in oracle. I'm getting data from a homegrown app in the form of flat files. Fome some columns I do not what length to expect in the file. Obviously, I know the length of the destination column. I've been setting the length of the columns to varchar2(4000) in the external table definition and then substringing the data down to length in the transformation. I would love it if there was a way for me to specify a "trim" length for the column and the column could be trimmed/truncated when it's read from the file.

For example:

Datafile:
Firstname, lastname
Billy,Johansensonton

I would like to create an external table where the lengths of the columns would be something like this:
firstname varchar2(10),
lastname varchar2(10)...

and querying the table would return:

FIRSTNAME LASTNAME
Billy Johansenso

In other words, I don't want to worry about how long the datatype is in the file. I just want it trimmed down to my specification. This is a simple example but there may be cases where the length of the column is over 4000 chars and I just want the first 50.

Any ideas are greatly appreciated.
Thanks,

Mike Received on Sat Jan 28 2006 - 14:10:21 CST