October 17th, 2003, 12:27 AM
i tried populating a table in oracle reading an excel file using sql loader.
a few records werent written.
the error was "record to large to insert". the column was a varchar(128) and what i inserted was much below that!!
October 17th, 2003, 10:00 AM
Need your oracle version...
Top of my head: Run "imp -h" should give you quick help -- or check one of your admin manuals for the args...
There are two that come to mind. There's a buffer setting -- the record has to fit in the buffer... I think it's "BUFFER=n" (in bytes)... try bigger.
Also, lower likelyhood: checkout "COMMIT=Y or N" if you use N then all your records must fit in one rollback segment so that the entire table can be imported or not imported as a single transactions. Fails when you try to import huges talbes. I use COMMIT=Y -- commits each record as it goes, but then when you die on record in the middle you have half a talbe imported...
October 17th, 2003, 10:36 AM
What the exaclty error message you got, is it
ORA-01401 : inserted value too large for column
Then remove the all spaces from beginning or end of each value like this
7559,John Smith ,10, 2500
should replace with:
Or might be you have so many space at the end of each record, remove these spaces also.
Or at the end of each record put your terminating sign, like this:
7559,John Smith,10, 2500
should replace with:
Check the all records where you are getting error message.
Message: Make your life easy, Make others life easier.
December 19th, 2003, 08:48 AM
To get a better handle on your data imports I suggest you go via Excel. The benefits of Excel is that you can easially view and fix the data if any errors occur. It would be an option if your dataset is not very large, ideally under 65,000 rows.
You can directly insert into Oracle - or any other database type - from Excel using the SQL*XL addin. SQL*XL is a bridge between Excel and databases.
Have a look at SQL*XL at www.oraxcel.com
January 5th, 2004, 01:55 AM
You might want to try the order of the fields specified in the .ctl file. If I am statically setting values for a column, I specify that columne/field name after the field names of the file I'm loading in. It helps me keep from getting confused which fields I'm loading.
January 15th, 2004, 05:43 AM
I had this exact same problem. I was trying to insert a synopsis column which would contain varying amounts of text.
I was trying to insert into a blob and then tried varchar(1000) but it had no luck.
I found that when i edited the text down to 250 characters it would work. So i assume this does indeed point to a buffer problem.
How can i display the current size of the buffer?