Sample External Tables

When creating external tables in Oracle, it is much easier to start with a sample definition and then just modify it to fit your specific needs. So that is what this post is all about, is providing some sample definitions that all of us can use the next time we need to come up with an external table.

With all of these, you will need to adjust your DEFAULT DIRECTORY definition in the external table definition if you just want to try out my files.

Comma Separated Values

Probably the most popular flat file format. The sample file contains data that has a header row and all of the values are quoted.

The sample file is states.csv

CREATE TABLE import_states_csv
(
    abbreviation        VARCHAR2(50),
    state_name          VARCHAR2(30),
    date_of_statehood   DATE,
    capital_city        VARCHAR2(30)
)
ORGANIZATION EXTERNAL
(
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY data_import
    ACCESS PARAMETERS
    (
        RECORDS DELIMITED BY NEWLINE
        SKIP 1
        FIELDS TERMINATED BY ',' ENCLOSED BY '"'
        MISSING FIELD VALUES ARE NULL
        REJECT ROWS WITH ALL NULL 
        FIELDS
        ( 
          abbreviation,
           state_name,
           date_of_statehood DATE "YYYY-MM-DD",
           capital_city
        )
    )
    LOCATION ('states.csv') 
); 

Tab Separated Values

This type of file is not seen as often these days, but you still may run into this from time to time.  Notice how in the external table definition the field separator character is specified as hex value 9 (which is the tab character).

The sample data file is states.tsv

CREATE TABLE import_states_tsv
(
    abbreviation        VARCHAR2(50),
    state_name          VARCHAR2(30),
    date_of_statehood   DATE,
    capital_city        VARCHAR2(30)
)
ORGANIZATION EXTERNAL
(
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY data_import
    ACCESS PARAMETERS
    (
        RECORDS DELIMITED BY NEWLINE
        SKIP 1
        FIELDS TERMINATED BY X'9' OPTIONALLY ENCLOSED BY '"'
        MISSING FIELD VALUES ARE NULL
        REJECT ROWS WITH ALL NULL 
        FIELDS
        ( 
           abbreviation,
           state_name,
           date_of_statehood DATE "YYYY-MM-DD",
           capital_city
        )
    )
    LOCATION ('states.tsv') 
); 

Fixed Width Data File

You can download a list of all of the US banks that participate in ACH transactions from the US Federal Reserve at this link - http://www.fededirectory.frb.org/download.cfm.

What is useful about this is that you can put this list in a database table, and then if oyu have customers enter an ACH number on your website, you can validate that the routing number they gave you is an actual bank that exists.  Yes, you can calculate a checksum on a routing number, but this is one step better.

So how would we import this file into Oracle using an external table.  Simple, the definition is below.  

What is also interesting about this is that we can use the NULLIF operator.  Banks that have underwent mergers have a new routing number assigned, but for most banks this is "000000000", so we can use the NULLIF operator to translate this value to NULL in our external table.  The same is true with zip code.

 CREATE TABLE fedwire_ach_file_import
(
    routing_number           VARCHAR2(9),
    last_name                VARCHAR2(1),
    servicing_frb_number     VARCHAR2(9),
    record_type_code         VARCHAR2(1),
    change_date              DATE,
    new_routing_number       VARCHAR2(9),
    customer_name            VARCHAR2(36),
    address                  VARCHAR2(36),
    city                     VARCHAR2(20),
    state_code               VARCHAR2(2),
    zip_code                 VARCHAR2(5),
    zip_code_extension       VARCHAR2(4),
    telephone                VARCHAR2(10),
    institution_status_code  VARCHAR2(1),
    data_view_code           VARCHAR2(1)
)
ORGANIZATION EXTERNAL
(
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY data_import
    ACCESS PARAMETERS
    (
      RECORDS FIXED 157 
      LOGFILE data_import:'fedwire_import.log'
      BADFILE data_import:'fedwire_import.bad'   
      FIELDS    
   (
          routing_number           CHAR(9),
          last_name                CHAR(1),
          servicing_frb_number     CHAR(9),
          record_type_code         CHAR(1),
          change_date              CHAR(6) DATE_FORMAT DATE MASK "MMDDYY",
          new_routing_number       CHAR(9) NULLIF new_routing_number = '000000000',
          customer_name            CHAR(36),
          address                  CHAR(36),
          city                     CHAR(20),
          state_code               CHAR(2),
          zip_code                 CHAR(5),
          zip_code_extension       CHAR(4) NULLIF zip_code_extension = '0000',
          telephone                CHAR(10),
          institution_status_code  CHAR(1),
          data_view_code           CHAR(1),   
          filler                   CHAR(5)
      ) 
 )
  LOCATION ('FedACHdir.txt')
)
REJECT LIMIT UNLIMITED;