I am facing a problem in loading data file using sqlldr command.
my data file is test_data.txt
1HHHHH
2MMMMM
3ccccc
4TTTTT
1HHHHH
2MMMMM
3ccccc
4TTTTT
1HHHHH
2MMMMM
3ccccc
4TTTTT
Here every four rows belongs to one set of data so i am trying to generate a common sequence number for every four lines using NEXTVAL and CURRVAL.
How to handle this sequence so that it generate a new number after the fourth record gets inserted.
kindly help.
my ctl file
load data
infile 'test_data.txt'
append
into table test_data
when (1) = '1'
(
seq_id "to_char(SYSDATE,'RRDDD')|| lpad(SEQ_BTH_PROCESS.nextval,6,'0')",
LINE_TYPE constant "A",
dat POSITION(2:6)
)
into table test_data
when (1)='2'
(
seq_id "to_char(SYSDATE,'RRDDD')|| lpad(SEQ_BTH_PROCESS.currval,6,'0')",
LINE_TYPE constant "B",
dat_b POSITION(2:6)
)
into table test_data
when (1)='2'
(
seq_id "to_char(SYSDATE,'RRDDD')|| lpad(SEQ_BTH_PROCESS.currval,6,'0')",
LINE_TYPE constant "B",
dat_c POSITION(2:6)
)
into table test_data
when (1) = '4'
(
seq_id "to_char(SYSDATE,'RRDDD')|| lpad(SEQ_BTH_PROCESS.currval,6,'0')",
LINE_TYPE constant "D",
dat_d POSITION(2:6)
)
sql loader command
SQLLDR test/test123@orcl control=samp.ctl log=samp_log.txt
table script CREATE TABLE CENTRAL.TEST_DATA
(
DAT VARCHAR2(10 BYTE),
LINE_TYPE CHAR(1 BYTE),
SEQ_ID VARCHAR2(12 BYTE),
DAT_B VARCHAR2(10 BYTE),
DAT_C VARCHAR2(10 BYTE),
DAT_D VARCHAR2(10 BYTE)
)
sequence script
CREATE SEQUENCE SEQ_BTH_PROCESS
START WITH 1
MAXVALUE 999999
MINVALUE 0