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: 9101
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 |
akash123
Messages: 46
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 |
|
Barbara Boehmer
Messages: 9101
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 |
akash123
Messages: 46
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 |
|
Barbara Boehmer
Messages: 9101
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: Mon Dec 23 03:07:25 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
