SQL loader position

3
votes

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

2
oraclesql-loader

2 Answers

1
votes

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.

1
votes

we need three text file & 1 batch file for Load Data:
Suppose your file location 'D:\loaddata'
input file 'D:\loaddata\abc.CSV'
1. D:\loaddata\abc.bad -- empty
2. D:\loaddata\abc.log -- empty
3. D:\loaddata\abc.ctl "Write Code Below"

    OPTIONS ( SKIP=1, DIRECT=TRUE, ERRORS=10000000, ROWS=5000000) 
    load data 
    infile 'D:\loaddata\abc.CSV' 
    TRUNCATE
    into table Your_table
  (
     a_column          POSITION   (1:7) char,
     b_column          POSITION   (8:10) char,
     c_column          POSITION   (11:12) char,
     d_column          POSITION   (13:13) char,
     f_column          POSITION   (14:20) char
  )

  1. D:\loaddata\abc.bat --- For execution

     sqlldr db_user/db_passward@your_tns control=D:\loaddata\abc.ctl log=D:\loaddata\abc.log

    After double click "D:\loaddata\abc.bat" file you data will be load desire oracle table. if anything wrong check you "D:\loaddata\abc.bad" and "D:\loaddata\abc.log" file