Home » SQL & PL/SQL » SQL & PL/SQL » CTL file options
CTL file options [message #199017] Thu, 19 October 2006 11:57 Go to next message
ankurgoyal
Messages: 1
Registered: October 2006
Junior Member
Hi,

I am having some problem trying to load two type of records from the same file into two different tables using a single CTL file.

The Data file looks something like this
Record Type, Rest of the Information
1 sdafi;ga
2 fpojv
2 dsvj;j
1 flkjvh
2 efjv;dokj
2 df;fkjb
2 ;fodj;

My CTL file looks like this

OPTIONS

(SKIP = 0)



LOAD DATA
INFILE 'C:\\salesdeal_sep.txt'
BADFILE 'C:\\salesdeal_sep.bad'
DISCARDFILE 'C:\\salesdeal_sep.dsc'



REPLACE
INTO TABLE H_TFM_ACTUALS_ACCRUAL_HDR
WHEN (RECORD_TYPE = '1') TRAILING NULLCOLS
(
RECORD_TYPE POSITION(1:1) char,
REST_OF_THE_INFO POSITION(3:15) char,
UNQ_SEQ "db_seq.nextval"
)

INTO TABLE H_TFM_ACTUALS_ACCRUAL_dtl
WHEN (RECORD_TYPE = '2') TRAILING NULLCOLS
(
RECORD_TYPE POSITION(1:1) char,
REST_OF_THE_INFO POSITION(3:15) char ,
UNQ_SEQ "db_seq.nextval"
)

I am able to successfully load these records into the two tables, but I need to relate the record type 1 with the corresponding record type 2. How do i do that ?

Can I declare a variable in the CTL file which would get incremented when a record type 1 is encountered or a sequence which could relate these two type of records ?

[Updated on: Thu, 19 October 2006 12:00]

Report message to a moderator

Re: CTL file options [message #199026 is a reply to message #199017] Thu, 19 October 2006 13:07 Go to previous message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>but I need to relate the record type 1 with the corresponding record type 2. How do i do that ?
Please rephrase.
How do you want to "relate"? something like parent/key child key?
Something like this?
http://www.orafaq.com/forum/m/146758/42800/?srch=sequence+sqlldr#msg_146758
Previous Topic: instance names
Next Topic: sql query
Goto Forum:
  


Current Time: Fri Dec 09 13:36:52 CST 2016

Total time taken to generate the page: 0.12910 seconds