[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 555
  • Last Modified:

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.

Thanks

Tom
0
tomatocans
Asked:
tomatocans
1 Solution
 
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.
0
 
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.

Thanks
0
 
bkowalskiCommented:
Use
  FIELDS TERMINATED BY "|"
And yes it automatically loops through each record.  A record is terminated by an end of line character.
-Brad
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
bkowalskiCommented:
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.
-Brad
0
 
tomatocansAuthor Commented:
So the control file looks like so:



load data
infile 'asqp_orig.out'
into table asqp_orig
(FIELDS TERMINATED BY "|")
0
 
bkowalskiCommented:
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 "|"
-Brad
0
 
sgantaCommented:
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
  LOAD DATA
  APPEND
  INTO TABLE emp_table
    (
    empno  POSITION(1:10) DECIMAL EXTERNAL,
    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
                      Regards
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now