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 Go to next message
nago
Messages: 10
Registered: January 2012
Junior Member
Hi,

lost my entire 2 days looking into this problem. but no luck.
please help. Embarassed
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 #540155 is a reply to message #540134] Fri, 20 January 2012 07:57 Go to previous messageGo to next message
Michel Cadot
Messages: 59130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
tab FILLER POSITION(01:3)

When the first row is read the fields are shift by one.
Just use "position(1)".

Regards
Michel
Re: sqlldr issue with many tables [message #540192 is a reply to message #540155] Fri, 20 January 2012 11:05 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 436
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 #540388 is a reply to message #540192] Mon, 23 January 2012 04:29 Go to previous messageGo to next message
nago
Messages: 10
Registered: January 2012
Junior Member
Hi, It works.
Can you tell me what is POSITION(1) ?
why dont we give POSITION(01:3) something like this ? why things are not working well with this option ?

Thank you
Nago
Re: sqlldr issue with many tables [message #540389 is a reply to message #540388] Mon, 23 January 2012 04:30 Go to previous messageGo to next message
Michel Cadot
Messages: 59130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Fri, 20 January 2012 14:57
Quote:
tab FILLER POSITION(01:3)

When the first row is read the fields are shift by one.
Just use "position(1)".

Regards
Michel

Re: sqlldr issue with many tables [message #540392 is a reply to message #540389] Mon, 23 January 2012 04:41 Go to previous messageGo to next message
nago
Messages: 10
Registered: January 2012
Junior Member
sorry.
i couldn't understand.
could you elaborate please ?


-nago
Re: sqlldr issue with many tables [message #540398 is a reply to message #540392] Mon, 23 January 2012 05:11 Go to previous messageGo to next message
Michel Cadot
Messages: 59130
Registered: March 2007
Location: Nanterre, France, http://...
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 #540407 is a reply to message #540398] Mon, 23 January 2012 06:00 Go to previous messageGo to next message
nago
Messages: 10
Registered: January 2012
Junior Member
so, is it so that its always better to go with POSITION(1 or 4 or 3..) with delimiter specified, instead of having POSITION(1:3) ?

-nago

[Updated on: Mon, 23 January 2012 06:09]

Report message to a moderator

Re: sqlldr issue with many tables [message #540413 is a reply to message #540407] Mon, 23 January 2012 06:21 Go to previous messageGo to next message
Michel Cadot
Messages: 59130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"POSITION" gives a fixed position and length if you specify the second index.
In your case you only want to indicate the starting position of the filler field, the length is variable.
There is no better choice, there is appropriate choice.

Regards
Michel
Re: sqlldr issue with many tables [message #540440 is a reply to message #540413] Mon, 23 January 2012 10:50 Go to previous message
Barbara Boehmer
Messages: 7984
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.

Previous Topic: Error during expdp on Oracle 11gR2 on Solaris
Next Topic: sqlldr - change the load type from "bulk" to "normal" ?
Goto Forum:
  


Current Time: Thu Sep 18 12:57:34 CDT 2014

Total time taken to generate the page: 0.10198 seconds