Home » RDBMS Server » Server Utilities » sql loader (9.2)
sql loader [message #310550] Tue, 01 April 2008 10:54 Go to next message
jinga
Messages: 115
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 #310551 is a reply to message #310550] Tue, 01 April 2008 10:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
101 posts and you don't know that a tag ends with /tag.

What is the table description?
Can its columns support your data?

Regards
Michel
Re: sql loader [message #310557 is a reply to message #310550] Tue, 01 April 2008 11:21 Go to previous messageGo to next message
jinga
Messages: 115
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 #310563 is a reply to message #310557] Tue, 01 April 2008 12:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Can you put the log file.

Regards
Michel
Re: sql loader [message #310567 is a reply to message #310557] Tue, 01 April 2008 12:38 Go to previous messageGo to next message
joy_division
Messages: 4642
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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
)



Previous Topic: SQL LOADER
Next Topic: Data uploading from legacy system to Oracle database
Goto Forum:
  


Current Time: Thu Dec 08 04:04:15 CST 2016

Total time taken to generate the page: 0.22380 seconds