Home » RDBMS Server » Server Utilities » SQL loader problem when using sequence (v 10.2.0.3 )  () 1 Vote
SQL loader problem when using sequence [message #658748] Mon, 26 December 2016 00:25 Go to previous message
akash123
Messages: 46
Registered: May 2008
Location: india
Member
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
 
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Previous Topic: sqlldr WHEN clause using <=
Next Topic: how imp new data
Goto Forum:
  


Current Time: Fri Mar 29 08:53:01 CDT 2024