Home » RDBMS Server » Server Utilities » Urgent - Loading from one flatfile to two tables - Getting wrong results
Urgent - Loading from one flatfile to two tables - Getting wrong results [message #70424] Wed, 12 June 2002 06:37 Go to next message
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 Go to previous messageGo to next message
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 #70437 is a reply to message #70431] Thu, 13 June 2002 12:24 Go to previous messageGo to next message
Swamy
Messages: 78
Registered: June 2002
Member
Mahesh,
Thanks a lot for your suggestion. I tested for a small data and it worked fine. Now I referred a book on SQL Loader by Jonathan Gennick & Sanjay Mishra and it says ROWS=n, commits the database n times. Is this leads to performance issue for a large data? Anyhow once again thanks for your help.
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 Go to previous messageGo to next message
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.
Re: Urgent - Loading from one flatfile to two tables - Getting wrong results [message #70462 is a reply to message #70431] Mon, 17 June 2002 08:17 Go to previous message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Direct=true has a lot of restrictions. you can find them in manuals.
Hope, we CAN find a workaround....
Previous Topic: Re: Error 1053: The service did not respond to the start or control request in a timely fashion.
Next Topic: how do i import a dump file created in oracle 8.1.5 into oracle 8.0.6
Goto Forum:
  


Current Time: Mon Apr 29 05:27:55 CDT 2024