Thread: SQL Loader

    #1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Location
    Pune
    Posts
    9
    Rep Power
    0

    Question SQL Loader


    Hi,
    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!!
    ne suggestions

    thnax
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    60
    Rep Power
    11
    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...

    -c
  4. #3
  5. No Profile Picture
    Senior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Canada
    Posts
    305
    Rep Power
    11
    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:
    7559,John Smith,10,2500

    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:
    7559,John Smith,10,2500,

    Check the all records where you are getting error message.

    Message: Make your life easy, Make others life easier.

    Regards,
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Location
    Manchester, UK
    Posts
    23
    Rep Power
    0
    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

    Gerrit-Jan Linker
    www.oraxcel.com (SQL*XL)
  8. #5
  9. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Location
    Tokyo
    Posts
    1
    Rep Power
    0
    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.
  10. #6
  11. No Profile Picture
    Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2002
    Posts
    68
    Rep Power
    13
    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?

IMN logo majestic logo threadwatch logo seochat tools logo