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 next message
akash123
Messages: 42
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
Re: SQL loader problem when using sequence [message #658750 is a reply to message #658748] Mon, 26 December 2016 00:55 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8751
Registered: November 2002
Location: California, USA
Senior Member
You can use CONCATENATE 4 to concatenate each set of 4 rows, as demonstrated below.

-- data file:
SCOTT@orcl_12.1.0.2.0> HOST TYPE test_data.txt
1HHHHH
2MMMMM
3ccccc
4TTTTT
1HHHHH
2MMMMM
3ccccc
4TTTTT
1HHHHH
2MMMMM
3ccccc
4TTTTT

-- SQL*Loader control file:
SCOTT@orcl_12.1.0.2.0> HOST TYPE samp.ctl
load data
infile 'test_data.txt'
append
concatenate 4
into table test_data
trailing nullcols
( dat_a position(2:6)
, dat_b position(8:12)
, dat_c position(14:18)
, dat_d position(20:24) )

-- table:
SCOTT@orcl_12.1.0.2.0> CREATE TABLE TEST_DATA
  2  (
  3    DAT_A      VARCHAR2(10 BYTE),
  4    DAT_B      VARCHAR2(10 BYTE),
  5    DAT_C      VARCHAR2(10 BYTE),
  6    DAT_D      VARCHAR2(10 BYTE)
  7  )
  8  /

Table created.

-- load:
SCOTT@orcl_12.1.0.2.0> host SQLLDR scott/tiger@orcl control=samp.ctl log=samp_log.txt

SQL*Loader: Release 12.1.0.2.0 - Production on Sun Dec 25 22:51:40 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 3

Table TEST_DATA:
  3 Rows successfully loaded.

Check the log file:
  samp_log.txt
for more information about the load.

-- results:
SCOTT@orcl_12.1.0.2.0> select * from test_data
  2  /

DAT_A      DAT_B      DAT_C      DAT_D
---------- ---------- ---------- ----------
HHHHH      MMMMM      ccccc      TTTTT
HHHHH      MMMMM      ccccc      TTTTT
HHHHH      MMMMM      ccccc      TTTTT

3 rows selected.
Re: SQL loader problem when using sequence [message #658751 is a reply to message #658750] Mon, 26 December 2016 01:06 Go to previous messageGo to next message
akash123
Messages: 42
Registered: May 2008
Location: india
Member
Thank you for the reply,
but here i dont have to insert the data in dat_c column. i mean if row starts with 3 then it has to be excuded and remaining 1,2 and 4 has to be clubbed together.

expected output should be like this
DAT_A      DAT_B      DAT_C      DAT_D
---------- ---------- ---------- ----------
HHHHH      MMMMM                 TTTTT
HHHHH      MMMMM                 TTTTT
HHHHH      MMMMM                 TTTTT
Re: SQL loader problem when using sequence [message #658752 is a reply to message #658751] Mon, 26 December 2016 01:16 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8751
Registered: November 2002
Location: California, USA
Senior Member
Just remove dat_c from the control file, as shown below.

SCOTT@orcl_12.1.0.2.0> HOST TYPE test_data.txt
1HHHHH
2MMMMM
3ccccc
4TTTTT
1HHHHH
2MMMMM
3ccccc
4TTTTT
1HHHHH
2MMMMM
3ccccc
4TTTTT

SCOTT@orcl_12.1.0.2.0> HOST TYPE samp.ctl
load data
infile 'test_data.txt'
append
concatenate 4
into table test_data
trailing nullcols
( dat_a position(2:6)
, dat_b position(8:12)
, dat_d position(20:24) )

SCOTT@orcl_12.1.0.2.0> CREATE TABLE TEST_DATA
  2  (
  3    DAT_A      VARCHAR2(10 BYTE),
  4    DAT_B      VARCHAR2(10 BYTE),
  5    DAT_C      VARCHAR2(10 BYTE),
  6    DAT_D      VARCHAR2(10 BYTE)
  7  )
  8  /

Table created.

SCOTT@orcl_12.1.0.2.0> host SQLLDR scott/tiger@orcl control=samp.ctl log=samp_log.txt

SQL*Loader: Release 12.1.0.2.0 - Production on Sun Dec 25 23:14:53 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 3

Table TEST_DATA:
  3 Rows successfully loaded.

Check the log file:
  samp_log.txt
for more information about the load.

SCOTT@orcl_12.1.0.2.0> select * from test_data
  2  /

DAT_A      DAT_B      DAT_C      DAT_D
---------- ---------- ---------- ----------
HHHHH      MMMMM                 TTTTT
HHHHH      MMMMM                 TTTTT
HHHHH      MMMMM                 TTTTT

3 rows selected.
Re: SQL loader problem when using sequence [message #658753 is a reply to message #658752] Mon, 26 December 2016 01:29 Go to previous messageGo to next message
akash123
Messages: 42
Registered: May 2008
Location: india
Member
sorry for mis guiding you. Here in data file C can come multiple times and i will be loading it into a saperate table. so i cannot fix the position value for D which comes after. how can i concatenate based on the position or using WHEN clause.

modified data file
1HHHHH
2MMMMM
3ccccc
3ccccc
3ccccc
4TTTTT
1HHHHH
2MMMMM
3ccccc
3ccccc
3ccccc
3ccccc
3ccccc
4TTTTT
1HHHHH
2MMMMM
3ccccc
3ccccc
4TTTTT
Re: SQL loader problem when using sequence [message #658756 is a reply to message #658753] Mon, 26 December 2016 02:12 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8751
Registered: November 2002
Location: California, USA
Senior Member
You could use a SQL*Loader sequence and load the data into a staging table, then use SQL to insert the data from the staging table to the target table, as shown below.

SCOTT@orcl_12.1.0.2.0> HOST TYPE test_data.txt
1HHHHH
2MMMMM
3ccccc
3ccccc
3ccccc
4TTTTT
1HHHHH
2MMMMM
3ccccc
3ccccc
3ccccc
3ccccc
3ccccc
4TTTTT
1HHHHH
2MMMMM
3ccccc
3ccccc
4TTTTT

SCOTT@orcl_12.1.0.2.0> HOST TYPE samp.ctl
load data
infile 'test_data.txt'
append
into table staging
when col1 != '3'
trailing nullcols
( col1    position(1)
, dat     position(2) terminated by whitespace
, seq     sequence )

SCOTT@orcl_12.1.0.2.0> CREATE TABLE staging
  2    (col1      NUMBER,
  3     dat       VARCHAR2(10 BYTE),
  4     seq       NUMBER)
  5  /

Table created.

SCOTT@orcl_12.1.0.2.0> host SQLLDR scott/tiger@orcl control=samp.ctl log=samp_log.txt

SQL*Loader: Release 12.1.0.2.0 - Production on Mon Dec 26 00:11:24 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 19

Table STAGING:
  9 Rows successfully loaded.

Check the log file:
  samp_log.txt
for more information about the load.

SCOTT@orcl_12.1.0.2.0> CREATE TABLE TEST_DATA
  2  (
  3    DAT_A      VARCHAR2(10 BYTE),
  4    DAT_B      VARCHAR2(10 BYTE),
  5    DAT_C      VARCHAR2(10 BYTE),
  6    DAT_D      VARCHAR2(10 BYTE)
  7  )
  8  /

Table created.

SCOTT@orcl_12.1.0.2.0> INSERT INTO test_data (dat_a, dat_b, dat_d)
  2  SELECT MAX (DECODE (col1, 1, dat)),
  3         MAX (DECODE (col1, 2, dat)),
  4         MAX (DECODE (col1, 4, dat))
  5  FROM   (SELECT col1, dat,
  6                 ROW_NUMBER () OVER (PARTITION BY col1 ORDER BY seq) rn
  7          FROM   staging)
  8  GROUP  BY rn
  9  /

3 rows created.

SCOTT@orcl_12.1.0.2.0> select * from test_data
  2  /

DAT_A      DAT_B      DAT_C      DAT_D
---------- ---------- ---------- ----------
HHHHH      MMMMM                 TTTTT
HHHHH      MMMMM                 TTTTT
HHHHH      MMMMM                 TTTTT

3 rows selected.
Re: SQL loader problem when using sequence [message #658758 is a reply to message #658756] Mon, 26 December 2016 03:31 Go to previous message
akash123
Messages: 42
Registered: May 2008
Location: india
Member
BARBARA BOEHMER, yes i can do this way,

Thanks alot.
Previous Topic: how imp new data
Next Topic: SQL Loader double quotes in clob data (3 threads from 2 sub-forums merged by bb)
Goto Forum:
  


Current Time: Sun Nov 19 23:40:33 CST 2017

Total time taken to generate the page: 0.00916 seconds