Urgent - Loading from one flatfile to two tables - Getting wrong results [message #70424] |
Wed, 12 June 2002 06:37 |
Swamy
Messages: 78 Registered: June 2002
|
Member |
|
|
Hi,
I am trying to load data from a flat file to two Oracle tables. I need to use SQL Loader and my database is 8.0.5.2.1. I am using one table as parent and the other as child. If the 1st field is “215SF”, then it has to load in to the 1st table, otherwise if the value is “835EFT”, then it has to load in to the 2nd table. I am using a database sequence to generate in to the 1st columns of the two tables.
The problem is, some child rows of the 2nd parent of the flat file are getting the sequence numbers of 1st parent. If I increase some child rows to the 1st parent row in the flat file, the behavior is changing. On the other way, if I alter the control file by showing all the columns of the tables, the behavior is different. I am trying in several ways, but I am unable to figure out in my control file, where I am doing wrong. I request some one help me to solve my problem. I appreciate in advance for the help.
The below are the columns in one table and are same for the 2nd table.
SL_NUM
ATTRIBUTE_1
ATTRIBUTE_2
ATTRIBUTE_3
ATTRIBUTE_4
ATTRIBUTE_5
ATTRIBUTE_6
ATTRIBUTE_7
ATTRIBUTE_8
ATTRIBUTE_9
ATTRIBUTE_10
ATTRIBUTE_11
ATTRIBUTE_12
ATTRIBUTE_13
ATTRIBUTE_14
ATTRIBUTE_15
ATTRIBUTE_16
ATTRIBUTE_17
ATTRIBUTE_18
ATTRIBUTE_19
ATTRIBUTE_20
The below is the sample flat file with delimitation by ‘^’
215SF^469001^20020514^29703100^*
835EFT^X^402^20020514^0830^016001944^N^F^469001^20020514^^^153525^*
835EFT^X^518^20020514^0830^043214787^N^F^469001^20020514^^^45000^*
835EFT^X^608^20020514^0830^020222932^N^F^469001^20020514^^^25000000^*
835EFT^X^650^20020514^0830^050474219^N^F^469001^20020514^^^4575^*
835EFT^X^538^20020514^0830^316014208^N^F^469001^20020514^^^20000^*
215SF^469002^20020515^297030^*
835EFT^X^402^20020515^0920^016001944^N^F^469002^20020515^^^1535^*
835EFT^X^518^20020515^0920^043214787^N^F^469002^20020515^^^450^*
I wrote the control file as below:
LOAD DATA
INFILE '/users/home/t32zvsd/flatfiles/EFT835_051402.dat'
APPEND
INTO TABLE t32zvsd.temp_routableeobs
WHEN (ATTRIBUTE_1='215SF')
TRAILING NULLCOLS (
ATTRIBUTE_1 CHAR TERMINATED BY "^" OPTIONALLY ENCLOSED BY '"',
ATTRIBUTE_2 CHAR TERMINATED BY "^" OPTIONALLY ENCLOSED BY '"',
ATTRIBUTE_3 CHAR TERMINATED BY "^" OPTIONALLY ENCLOSED BY '"',
ATTRIBUTE_4 CHAR TERMINATED BY "^" OPTIONALLY ENCLOSED BY '"',
ATTRIBUTE_5 CHAR TERMINATED BY "^" OPTIONALLY ENCLOSED BY '"',
SL_NUM "TEMPROUTABLE_SEQ.NEXTVAL"
)
INTO TABLE t32zvsd.temp_routableeobs2
WHEN (ATTRIBUTE_1='835EFT')
TRAILING NULLCOLS (
ATTRIBUTE_1 POSITION(1) CHAR TERMINATED BY "^" OPTIONALLY ENCLOSED BY '"',
ATTRIBUTE_2 CHAR TERMINATED BY "^" OPTIONALLY ENCLOSED BY '"',
ATTRIBUTE_3 CHAR TERMINATED BY "^" OPTIONALLY ENCLOSED BY '"',
ATTRIBUTE_4 CHAR TERMINATED BY "^" OPTIONALLY ENCLOSED BY '"',
ATTRIBUTE_5 CHAR TERMINATED BY "^" OPTIONALLY ENCLOSED BY '"',
ATTRIBUTE_6 CHAR TERMINATED BY "^" OPTIONALLY ENCLOSED BY '"',
ATTRIBUTE_7 CHAR TERMINATED BY "^" OPTIONALLY ENCLOSED BY '"',
ATTRIBUTE_8 CHAR TERMINATED BY "^" OPTIONALLY ENCLOSED BY '"',
ATTRIBUTE_9 CHAR TERMINATED BY "^" OPTIONALLY ENCLOSED BY '"',
ATTRIBUTE_10 CHAR TERMINATED BY "^" OPTIONALLY ENCLOSED BY '"',
ATTRIBUTE_11 CHAR TERMINATED BY "^" OPTIONALLY ENCLOSED BY '"',
ATTRIBUTE_12 CHAR TERMINATED BY "^" OPTIONALLY ENCLOSED BY '"',
ATTRIBUTE_13 CHAR TERMINATED BY "^" OPTIONALLY ENCLOSED BY '"',
ATTRIBUTE_14 CHAR TERMINATED BY "^" OPTIONALLY ENCLOSED BY '"',
SL_NUM "TEMPROUTABLE_SEQ.CURRVAL"
)
|
|
|
Re: Urgent - Loading from one flatfile to two tables - Getting wrong results [message #70431 is a reply to message #70424] |
Wed, 12 June 2002 15:48 |
|
Mahesh Rajendran
Messages: 10707 Registered: March 2002 Location: oracleDocoVille
|
Senior Member Account Moderator |
|
|
I am not very sure, whether these help you.
1.)You must use the ROWS option with one in the command line because the records must be
processed one at a time if you want
to have the same sequence number in the two tables:
sqlldr usr/pwd CONTROL=test.ctl ROWS=1
are you doing that?
2.)
This is a sample script from docs
LOAD DATA
INFILE 'my_data.dat'
insert
INTO TABLE my_master
when (1:1) = 'M'
fields terminated by ',' optionally enclosed by '"'
(my_master_key position(1:2) "my_seq.nextval",
my_master_info
)
into TABLE my_detail
when (1:1) = 'D'
fields terminated by ',' optionally enclosed by '"'
(my_detail_key position(1:2) "my_seq.currval",
my_detail_info
)
In the control file you will see position(1:2)
for BOTH MASTER AND DETAIL TABLES. This tells
Oracle that the INFO fields begin in position 3
of each data line.
are you doing this?
[Updated on: Wed, 07 July 2010 13:32] Report message to a moderator
|
|
|
|
Re: Urgent - Loading from one flatfile to two tables - Getting wrong results [message #70458 is a reply to message #70431] |
Mon, 17 June 2002 06:18 |
Swamy
Messages: 78 Registered: June 2002
|
Member |
|
|
I used DIRECT=TRUE and I am getting the following error:
SQL*Loader-417: SQL string (on column SL_NUM) not allowed in direct path.
So, I can't use this and I don't think that we can't solve this problem with SQL*Loader. May be I need to use UTL_FILE package. Otherwise, if I switchover to Oracle 9i, then may be I can use Oracle External Tables. I don't know why Oracle doesn't have solution for this type of problem.
|
|
|
|