SQL & PL/SQL

I'm looking at external tables to solve a problem with loading very wide CSV files into Oracle. We have CSV files with up to 1200 columns, which is too big for loading into a single database table. We're looking at loading the 1200-column CSV files into 3 x 400-column Oracle tables instead.

External tables look like a good solution, as we can treat them like regular tables for reading data from the "wide" source files and processing it further in our system.

But I'm having problems getting the column/field specifications right in my external table CREATE command (on Oracle 10g). The table seems to be created OK, but no data gets loaded and SELECTs return no rows. Smaller tables created from smaller CSV files in the same way seem to work OK. Not sure what the problem is here - I'm investigating that.

In the meantime, there is also the issue of how to split the 1200-column field into 3 external tables. As far as I can tell, I have to specify all 1200 fields in the ACCESS PARAMETERS section, e.g. as VAR1, VAR2...VAR1200, but I only need to specify the 400 columns I want for the current table table in the CREATE TABLE section, i.e. (VAR1, VAR2...VAR400) for the first of the 3 tables.

Is this correct? Is there no way to skip straight to the columns I need for each table in the ACCESS PARAMETERS section i.e. columns 1-400 for table 1, cols 401 to 800 for table 1, and cols 801 to 1200 for table 3? Is there no equivalent of the FILLER clause in SQL*Loader to indicate you want to skip a column?

Finally, while I investigate the problems I'm having loading data, can anybody tell me if there is an upper limit on the number of fields you can have in your source file for an external table?

Thanks for any help!

Chris

Edited by: chriswebster on Nov 13, 2010 2:32 PM