Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2005
    Location
    Waukesha WI
    Posts
    78

    Unanswered: sqlldr:Special characters-too large?

    I just wrote my first ctl file and it worked great for the 300K rows of data I have with one exception.

    The input file contains a FirstName field. Someone decided that, as part of the 30 characters, they would use something like "JEROME ****JERRY****".
    When the loader gets to this it tells me that the inserted value is too large for the column. I'm pretty sure it has to do with the special characters since I can view the records in a table in Access and that appears to be the only difference.

    It is a delimited file and I'm doing a straight:
    FIRST_NAME CHAR NULLIF FIRSTNAME=BLANKS,
    for the input.

    I don't really care about the special characters.

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    You might try to substring the column to ensure that you are not trying to pull in more the 30 characters.
    ie;
    NAME char "SUBSTR(:NAME,1,30)"

    hth
    Gregg

  3. #3
    Join Date
    Jun 2005
    Location
    Waukesha WI
    Posts
    78
    thanks Greg.
    I changed the line to:
    FIRST_NAME CHAR NULLIF FIRST_NAME=BLANKS "SUBSTR(:FIRST_NAME ,1,30)" ,

    It still has the same number of errors.
    It's loading it into a VARCHAR2 field with a width of 30.
    The only difference I can see with these fields are that they contain non-alphanumeric characters - '*' or '('

    Would that throw anything off?

    The delimiter is a "|" but I dont think that is a problem since the other lines work good.

    As this is my first sqlldr ctl file, am I doing something wrong?

  4. #4
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    post the piece of the control file you are using as well as a few lines
    from the input file. I'll look at it more closely ... The '*' and '(' shouldn't
    cause a problem...
    By the way, does the table that you are loading into have an insert trigger
    on it ?? If it does, what is it doing ...

    Gregg

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •