Search code examples
databaseoracle-databaseoracle11gexternal

Oracle 11g External Table (file) about skipping data

I am using the external table approach with Oracle 11g to load data from files to the database.

The files come from external sources and are of standard format. However, my application does not need all the columns (fields in file) for each row. Is there a way I can specify which columns should be sourced and which should be ignored (by, for example, providing an offset).

Solution

  • Since your data file is fixed width, you can simply omit the portions of the line that you're not interested in. There is no requirement that your field specifications are continuous.

    For example, rather than specifying

    (
  col1 (1:1) CHAR(1),
  col2 (2:2) CHAR(1),
  col3 (3:3) CHAR(1)
)

    to load the first three characters in the line into a separate column, you could skip the second column and just load the first, third, and fourth positions in the file into three separate columns.

    (
  col1 (1:1) CHAR(1),
  col2 (3:3) CHAR(1),
  col3 (4:4) CHAR(1)
)