Home » RDBMS Server » Server Utilities » sqlldr issue with many tables (Linux OS)
| sqlldr issue with many tables [message #540134] |
Fri, 20 January 2012 06:45  |
 |
nago
Messages: 10
Registered: January 2012
|
Junior Member |
|
|
Hi,
lost my entire 2 days looking into this problem. but no luck.
please help. 
I want to load data into more tables from many files ,based on first column value,which is FILLER field.
i am trying to test this scenario with two oracle tables with similar definition. and load one record on each table using WHEN/POSITION keywords. for this , i added first column as reference column in the data which i have in ctl file itself.
1st table loaded with 1st record. But, 2nd record not loading.
could you tell me if i missed anything with WHEN/POSITION keyword ?
This is the error in log file for 2nd table(WD1):
>>>>>
Record 2: Rejected - Error on table WD1, column TAB.
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
Table WD1:
0 Rows successfully loaded.
1 Row not loaded due to data errors.
1 Row not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
<<<<<<<<<
Here is the ctl file i use.
----------------------------
OPTIONS
(
PARALLEL=FALSE,DIRECT=TRUE,SILENT=FEEDBACK,ERRORS=999999
)
UNRECOVERABLE
load data
infile *
append
into table WD WHEN tab='WD'
FIELDS TERMINATED BY ','
( tab FILLER CHAR,
DA DATE "YYYY-MM-DD",
YR INTEGER EXTERNAL,
CNT1 INTEGER EXTERNAL,
CNT2 INTEGER EXTERNAL,
CNT3 INTEGER EXTERNAL,
DA2 DATE "YYYY-MM-DD"
)
into table WD1 WHEN tab='WD1'
( tab FILLER POSITION(01:3),
DA DATE "YYYY-MM-DD",
YR INTEGER EXTERNAL,
CNT1 INTEGER EXTERNAL,
CNT2 INTEGER EXTERNAL,
CNT3 INTEGER EXTERNAL,
DA2 DATE "YYYY-MM-DD"
)
begindata
WD,2012-01-13,2012,1,2,58,2012-01-17
WD1,2012-01-13,2012,1,2,58,2012-01-17
much thanks
nago
|
|
|
|
|
|
| Re: sqlldr issue with many tables [message #540192 is a reply to message #540155] |
Fri, 20 January 2012 11:05  |
 |
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
|
Senior Member |
|
|
You may also need to repeat the "FIELDS..." option:
OPTIONS ( DIRECT=TRUE,SILENT=FEEDBACK,ERRORS=99999)
load data
infile *
append
into table WD
WHEN tab = 'WD'
FIELDS TERMINATED BY ',' TRAILING NULLCOLS
( tab FILLER CHAR,
DA DATE "YYYY-MM-DD",
YR INTEGER EXTERNAL,
CNT1 INTEGER EXTERNAL,
CNT2 INTEGER EXTERNAL,
CNT3 INTEGER EXTERNAL,
DA2 DATE "YYYY-MM-DD"
)
into table WD1
WHEN tab = 'WD1'
FIELDS TERMINATED BY ',' TRAILING NULLCOLS
(
tab FILLER POSITION(1),
DA DATE "YYYY-MM-DD",
YR INTEGER EXTERNAL,
CNT1 INTEGER EXTERNAL,
CNT2 INTEGER EXTERNAL,
CNT3 INTEGER EXTERNAL,
DA2 DATE "YYYY-MM-DD"
)
begindata
WD,2012-01-13,2012,1,2,58,2012-01-17
WD1,2012-01-13,2012,1,2,58,2012-01-17
[Updated on: Sat, 21 January 2012 01:04] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: sqlldr issue with many tables [message #540398 is a reply to message #540392] |
Mon, 23 January 2012 05:11 |
 |
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
You say your filler is in position 1 to 3 then you have the other columns starting at position 4.
So when the filler is not exactly 2 characters then the first field (DA) which now starts at position 4 is wrong.
Here's an example to show you:
SQL> create table t (c1 varchar2(20), c2 varchar2(20));
Table created.
SQL> host type t1.ctl
LOAD DATA
INFILE *
INTO TABLE T
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
C FILLER POSITION(1:3),
C1 CHAR,
C2 CHAR
)
BEGINDATA
1,col11,col12
22,col21,col22
333,col31,col32
4444,col41,col42
SQL> host sqlldr michel/michel control=t1.ctl
SQL*Loader: Release 10.2.0.4.0 - Production on Lun. Janv. 23 12:08:07 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Commit point reached - logical record count 4
SQL> select * from t;
C1 C2
-------------------- --------------------
ol11 col12
col21 col22
col31
4 col41
4 rows selected.
If you mention just "position(1)", you say the filler field starts at this position and ends at the comma:
SQL> drop table t;
Table dropped.
SQL> create table t (c1 varchar2(20), c2 varchar2(20));
Table created.
SQL> host type t1.ctl
LOAD DATA
INFILE *
INTO TABLE T
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
C FILLER POSITION(1),
C1 CHAR,
C2 CHAR
)
BEGINDATA
1,col11,col12
22,col21,col22
333,col31,col32
4444,col41,col42
SQL> host sqlldr michel/michel control=t1.ctl
SQL*Loader: Release 10.2.0.4.0 - Production on Lun. Janv. 23 12:09:33 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Commit point reached - logical record count 4
SQL> select * from t;
C1 C2
-------------------- --------------------
col11 col12
col21 col22
col31 col32
col41 col42
4 rows selected.
Regards
Michel
|
|
|
|
|
|
|
|
| Re: sqlldr issue with many tables [message #540440 is a reply to message #540413] |
Mon, 23 January 2012 10:50 |
 |
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
The problem is the mixture of fixed-length and delimited terminology. When you tell it to use the comma as a default delimiter, then tell it to check the first 3 characters for the first filler field, it is still expecting another field before the first comma. If you add another filler, then it works. However, it is best just to use position(1) as previously stated. The following is just to demonstrate what the problem actually is, for better understanding. There are also other methods such as making the length one character longer and including the comma in the value.
-- test.ctl control file with extra filler field after second when clause:
OPTIONS
(
PARALLEL=FALSE,DIRECT=TRUE,SILENT=FEEDBACK,ERRORS=999999
)
UNRECOVERABLE
load data
infile *
append
into table WD WHEN tab='WD'
FIELDS TERMINATED BY ','
( tab FILLER CHAR,
DA DATE "YYYY-MM-DD",
YR INTEGER EXTERNAL,
CNT1 INTEGER EXTERNAL,
CNT2 INTEGER EXTERNAL,
CNT3 INTEGER EXTERNAL,
DA2 DATE "YYYY-MM-DD"
)
into table WD1 WHEN tab='WD1'
FIELDS TERMINATED BY ','
( tab FILLER POSITION(1:3),
comma FILLER,
DA DATE "YYYY-MM-DD",
YR INTEGER EXTERNAL,
CNT1 INTEGER EXTERNAL,
CNT2 INTEGER EXTERNAL,
CNT3 INTEGER EXTERNAL,
DA2 DATE "YYYY-MM-DD"
)
begindata
WD,2012-01-13,2012,1,2,58,2012-01-17
WD1,2012-01-13,2012,1,2,58,2012-01-17
-- tables, load, and results:
SCOTT@orcl_11gR2> create table wd
2 (da date,
3 yr number,
4 cnt1 number,
5 cnt2 number,
6 cnt3 number,
7 da2 date)
8 /
Table created.
SCOTT@orcl_11gR2> create table wd1
2 (da date,
3 yr number,
4 cnt1 number,
5 cnt2 number,
6 cnt3 number,
7 da2 date)
8 /
Table created.
SCOTT@orcl_11gR2> host sqlldr scott/tiger control=test.ctl log=test.log
SCOTT@orcl_11gR2> select * from wd
2 /
DA YR CNT1 CNT2 CNT3 DA2
--------- ---------- ---------- ---------- ---------- ---------
13-JAN-12 2012 1 2 58 17-JAN-12
1 row selected.
SCOTT@orcl_11gR2> select * from wd1
2 /
DA YR CNT1 CNT2 CNT3 DA2
--------- ---------- ---------- ---------- ---------- ---------
13-JAN-12 2012 1 2 58 17-JAN-12
1 row selected.
|
|
|
|
Goto Forum:
Current Time: Thu Jan 30 19:51:35 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
