SQL DBLOADER on Oracle 8i

I have the following input:

AA                   1 N381AA      JFK               LAX                      99           1           1           5         900        1212         900        1212         913         929        1154        1200           0            0          13           -12                -25               372         347          16         325           6
VV                  1 N372AA      JFK               LAX                      99           1           2           6         900        1212         900        1212         901         916        1145        1149           0            0           1           -23                -24               372         348          15         329           4
AA                   1 N358AA      JFK               LAX                      99           1           3           7         900        1212         900        1212         923         944        1203        1211           0            0          23            -1                -24               372         348          21         319           8
PP                   1 N353AA      JFK               LAX                      99           1           4           1         900        1212         900        1212         904         919        1133        1141           0            0           4           -31                -35               372         337          15         314           8

How do you create the column and field specifications in the control file to input these into a table defined as such:

SQL> create table asqp
  2  (airline_id char(2) not null,
  3  flight_num number not null,
  4  tail_number char(10),
  5  departure_airport char(3) not null,
  6  arrival_airport char(3) not null,
  7  year number(2) not null,
  8  month number(2) not null,
  9  day number(2) not null,
 10  dow number(1) not null,
 11  out_oag number,
 12  in_oag number,
 13  out_crs number not null,
 14  in_crs number not null,
 15  out_act number,
 16  off_act number,
 17  on_act number,
 18  in_act number,
 19  pb_diff number,
 20  at_gate_diff number,
 21  pb_delay number,
 22  at_gate_delay number,
 23  pb_to_at_gate_diff number,
 24  pb_to_at_gate_crs number not null,
 25  out_to_in number,
 26  out_to_off number,
 27  off_to_on number,
 28  on_to_in number);

Table created.

The positions I am reading about that need definition in the control file seem hard coded. How can you make it flexible enough to handle positions that are not always the same length.


Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark GeerlingsDatabase AdministratorCommented:
Oracle's SQL*Loader handles two basic types of data: fixed format or delimited.  In fixed format files, each column is the same place in each record and the positions of the column can be hard-coded in the control file.  Delimited files usually have a comma or some other distinctive character in between data elements and SQL*Loader uses the delimiter to to divide the data into columns in the table.  It looks like you have a "hybrid" data file where the columns are not quite fixed.

One option is to create an intermediate table with one large varchar2 column in it.  Use SQL*Loader to load each line of input data into one record, then use a PL\SQL procedure to read each record form the intermediate table, dividing  the data into columns and do the insert into your intended table.  This is not as fast or as easy as SQL*Loader by itself, but SQL*Loader is not very sophisticated.
tomatocansAuthor Commented:
If you have a delimited file say dividing the fields by | , how would you change the control file to input this data: so far here is he control file:

load data

infile 'asqp_orig.out'

into table asqp_orig

( what goes here to load a variable length file delimited by a |)

Also, will it automatically loop through each record and load it into the table or does it need a loop statement.

And yes it automatically loops through each record.  A record is terminated by an end of line character.
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Sorry, from the Oracle doc, a record is terminated by "a newline character (`\n'), or a carriage return followed by a newline ("\r\n"). The record terminator used is platform dependent."
Have you got access to Oracle doc?  If not, check the documentation area on http://technet.oracle.com.  You have to sign up, but membership is free.  SQL*Loader doc will be under Oracle8 Database, Oracle8 Utilities.
tomatocansAuthor Commented:
So the control file looks like so:

load data
infile 'asqp_orig.out'
into table asqp_orig
I don't use brackets or quotes (I don't know if it matters...), and I specify full pathnames to files, so for example:
load data
infile /data01/oracle/admin/loader/asqp_orig.out
into table asqp_orig
fields terminated by "|"
Your data appears like in the same specified positions :

OK Do the following

  I am giving brief description about to load the data into ORACLE tables.

 1. Write a control file which loads the data into ORACLE table
  INTO TABLE emp_table
    ename  POSITION(11:30) CHAR,
    sex       POSITION(31:40) "DECODE('MALE','M','FEMALE','F'),
    join_date POSITION(41,51) DATE 'DD-MON-YYYY'

  This is the simple example , which I am giving,
   If you have any specific doubts, pl. let me know.

run this control file like this at the UNIX prompt

sqlldr userid=username/password control=<Control_File_name> Data=<data_file_name>

Hope this helps you

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.