asked on

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

Mark Geerlings

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.

tomatocans

ASKER

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

bkowalski

Use
FIELDS TERMINATED BY "|"
And yes it automatically loops through each record. A record is terminated by an end of line character.
-Brad

bkowalski

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

tomatocans

ASKER

So the control file looks like so:

load data
infile 'asqp_orig.out'
into table asqp_orig
(FIELDS TERMINATED BY "|")

bkowalski

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

ASKER CERTIFIED SOLUTION

sganta

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial