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  |
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
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  |
 |
Barbara Boehmer
Messages: 9106
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 #658752 is a reply to message #658751] |
Mon, 26 December 2016 01:16 |
|
Barbara Boehmer
Messages: 9106
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 #658756 is a reply to message #658753] |
Mon, 26 December 2016 02:12 |
|
Barbara Boehmer
Messages: 9106
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.
|
|
|
|
|
|
Goto Forum:
Current Time: Wed Jul 16 03:50:41 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
