| sql loader [message #310550] |
Tue, 01 April 2008 10:54  |
jinga
Messages: 116
Registered: January 2003
|
Senior Member |
|
|
My text file has this date in the following format...
date captured -> 2/28/2008 10:59:17.750 AM
LOAD DATA
INFILE Rep_Unit.txt
REPLACE
INTO TABLE rep_unit
FIELDS TERMINATED BY '\\'
TRAILING NULLCOLS
(SURVEY_UNIT_CODE,
DATE_CAPTURED date 'MM/DD/YYYY HH24:MI:SS' terminated by '.',
IPP_PPI_OVERLAP)
I am getting error ora-12899.value too large for the column.
Anu
[Updated on: Tue, 01 April 2008 10:59] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: sql loader [message #310557 is a reply to message #310550] |
Tue, 01 April 2008 11:21  |
jinga
Messages: 116
Registered: January 2003
|
Senior Member |
|
|
Thanks for pointing out that i missed the closure of the tag.!!!!
I have 3 columns.
My text file looks like this..
A\2/28/2008 10:59:17.750 AM\B
C\2/29/2008 10:59:17.750 AM\C
LOAD DATA
INFILE Rep_Unit.txt
REPLACE
INTO TABLE rep_unit
FIELDS TERMINATED BY '\\'
TRAILING NULLCOLS
(SURVEY_UNIT_CODE,
DATE_CAPTURED date 'MM/DD/YYYY HH24:MI:SS' terminated by '.',
IPP_PPI_OVERLAP)
In my table rep_unit.
survey_unit_code is varchar2(1)
Date_captured is date
ipp_ppi_overlap varchar2(1).
|
|
|
|
|
|
| Re: sql loader [message #310567 is a reply to message #310557] |
Tue, 01 April 2008 12:38 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
| Ananthi wrote on Tue, 01 April 2008 12:21 |
My text file looks like this..
A\2/28/2008 10:59:17.750 AM\B
C\2/29/2008 10:59:17.750 AM\C
DATE_CAPTURED date 'MM/DD/YYYY HH24:MI:SS' terminated by '.',
|
Format does not match data.
|
|
|
|
| Re: sql loader [message #310645 is a reply to message #310567] |
Tue, 01 April 2008 22:55 |
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
|
Senior Member |
|
|
I tried this out i don't see any problem in loading data.Data is successfully loaded into my table.I had declared DATE_CAPTURED as DATE type in my table.
1) Post your Table structure(rep_unit).
2) Error Log while loading
|
|
|
|
| Re: sql loader [message #310646 is a reply to message #310645] |
Tue, 01 April 2008 23:02 |
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
|
Senior Member |
|
|
I increased the size of survey_unit_code,IPP_PPI_OVERLAP to varchar2(10).
And i think i got the problem.
When i increased the size of the column the data inserted is as follows:-
A 2/28/2008 10:59:17 AM 750 AM
C 2/29/2008 10:59:17 AM 750 AM
Please Check your control file.
|
|
|
|
| Re: sql loader [message #310648 is a reply to message #310646] |
Tue, 01 April 2008 23:26 |
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
|
Senior Member |
|
|
The problem is occuring because when you are terminating the data via '.' to get date.The part that is remained after '.' is loaded into IPP_PPI_OVERLAP.
Make following change to your control file:-
LOAD DATA
INFILE Rep_Unit.txt
REPLACE
INTO TABLE rep_unit
FIELDS TERMINATED BY '\\'
TRAILING NULLCOLS
(
SURVEY_UNIT_CODE,
DATE_CAPTURED date 'MM/DD/YYYY HH24:MI:SS' terminated by '.',
Useless_Variable FILLER,
IPP_PPI_OVERLAP
)
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
