SQL loader position

Question

New to SQL loader and am a bit confused about the POSITION. Let's use the following sample data as reference:

Munising  49862 MI
Shingleton49884 MI
Seney     49883 MI

And here is the load statement:

LOAD DATA
   INFILE 'zipcodes.dat'
   REPLACE INTO TABLE zipcodes (
      city_name POSITION(1) CHAR(10),
      zip_code POSITION(*) CHAR(5),
      state_abbr POSITION(*+1) CHAR(2)
      )

In the load statement, the city_name POSITION is 1. How does SQLLDR know where it ends? Is CHAR(10) the trick here? Counting the two spaces behind 'Munising', it has 10 characters.

Also why is zip_code assigned with CHAR even though it contains nothing but numbers?

Thank You

Answer 1

Yes, when end position is not specified, it is derived from the datatype. This documentation explains the POSITION clause.

• city_name POSITION(1) CHAR(10)

  Here the starting position of data field is 1. Ending position is not specified, but is derived from the datatype, that is 10.

• zip_code POSITION(*) CHAR(5)

  Here * specifies that, data field immediately follows the previous field and should be 5 bytes long.

• state_abbr POSITION(*+1) CHAR(2)

  Here +1 specifies the offset from the previous field. Sqlloader skips 1 byte and reads next 2 bytes, as derived from char(2) datatype.

As to why zipcode is CHAR, zip code is considered simply a fixed length string. You are not going to do any arithmetic operations on it. So, CHAR is appropriate for it. Also, have a look at SQL Loader datatypes. In control file you are telling SQL*Loader how to interpret the data. It can be different from that of table structure. In this example you could also specify INTEGER EXTERNAL for zip code.