| How to avoid ORA-01438 while using SQL Loader(urgent) [message #167832] |
Mon, 17 April 2006 06:55  |
d_indrani
Messages: 8
Registered: November 2005
Location: Bangalore, India
|
Junior Member |
|
|
Hi,
I have a table like below:
CREATE TABLE TEST1
(
dum1 NUMBER(4),
dum2 VARCHAR2(30),
dum3 VARCHAR2(30),
dum4 VARCHAR2(20)
);
I am loading data using SQL Loadar.
My data File:
------------
123498,'aaaa','bbbb','cccc'
2356,'xxxx','yyyy','zzzz','qqqq'
4856,'aaa','bbb'
Now if I try to load the above data 1st, 3rd records are getting rejected.
In case of 1st record, it is rejected as value for the first column [DUM1 Number(4)] is larger than the assigned one.
But what I want is that this record should not get rejected,
it should load 1234 in first column of that table and should truncate all extra numbers/digit.
Is there any fix/option while writing the control file?
Control File:
-----------
LOAD DATA
APPEND
INTO TABLE TEST1
FIELDS TERMINATED BY ','
(
dum1,
dum2,
dum3,
dum4
)
Thanks
|
|
|
|
| Re: How to avoid ORA-01438 while using SQL Loader(urgent) [message #167835 is a reply to message #167832] |
Mon, 17 April 2006 07:08  |
 |
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
Thanks for providing TableDDL/sample data/controlfile. Makes it easier to respond.
oracle@mutation#clear
oracle@mutation#cat test1.ctl
LOAD DATA
infile 'test1.dat'
truncate
INTO TABLE TEST1
FIELDS TERMINATED BY ',' trailing nullcols
(
dum1 "substr(:dum1,1,4)",
dum2,
dum3,
dum4
)
oracle@mutation#cat test1.dat
123498,'aaaa','bbbb','cccc'
2356,'xxxx','yyyy','zzzz','qqqq'
4856,'aaa','bbb'
oracle@mutation#desc mutation scott.test1
Table:scott.test1
Name Null? Type
----------------------------------- -------- ------------------------
DUM1 NUMBER(4)
DUM2 VARCHAR2(30)
DUM3 VARCHAR2(30)
DUM4 VARCHAR2(20)
oracle@mutation#sqlldr userid=scott/tiger control=test1.ctl
SQL*Loader: Release 9.2.0.4.0 - Production on Mon Apr 17 08:07:41 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Commit point reached - logical record count 3
oracle@mutation#query mutation scott.test1
DUM1 DUM2 DUM3 DUM4
---------- ------------------------------ ------------------------------ --------------------
1234 'aaaa' 'bbbb' 'cccc'
2356 'xxxx' 'yyyy' 'zzzz'
4856 'aaa' 'bbb'
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
