Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2004
    Posts
    16

    Unanswered: Problem with SQL Loader, not able to load a clolumn data of type varchar2(4000)

    Hi,

    I am trying to load data from a .csv file into the table that contains few columns of type varchar2(4000).

    I am not able to insert the data successfully into the table.
    The below is the error I am encountering( in the log file ).

    Record 6: Rejected - Error on table PROJECT_DET, column PROJ_SCOPE.
    Field in data file exceeds maximum length

    where PROJECT_DET is the table created with one of the columns as PROJ_SCOPE VARCHAR2(4000).

    This error is coming for all the records in which PROJ_SCOPE length is more that 500 characters.

    Please suggest me other possible alternatives to load the data with larger length(4000 chars).

    Help in this regard is greatly needed.

    Thanks in advance
    sudhav17

  2. #2
    Join Date
    Feb 2004
    Location
    inida
    Posts
    62
    Record 6: Rejected - Error on table PROJECT_DET, column PROJ_SCOPE.
    Field in data file exceeds maximum length

    I also had got the same error, whn i waz trying to load thru sql loader.
    It is not because of the size is more than 500 or not.
    i found in my data comma (,) waz not given properly. i mean if the seperator of the data is comma this error occurs.

    eg:if my table is
    test- a varchar2(200),
    - b number,
    - c varchar2(4000),
    - d varchar2(1)

    file which is having insert data waz like this
    xxxx,5,asdfghjk,lll,6 - this is wrong because the data inm trying to insert is

    "xxxx" for column a
    5 for column b
    "asdfghjk,lll" for column c
    6 for column d

    but as per the above file it vll b taking as
    "xxxx" for column a
    5 for column b
    "asdfghjk" for column c
    and "lll" for column d

    so either u can replace comma in ur data with some other symbol and replace back after insertion .
    or u can give data in quotes .

    hope i dint confuse u.

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    Basically he is saying your delimeter is also inside your varchar(4000) data
    so sqlloader thinks it is the next column
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Sep 2004
    Posts
    16
    I am actually using the control file with each field comma seperated and data is enclosed by '^'.
    And in the data that I am trying to insert there is no '^'.

    I think though the delimiter is a comma, since the data is enclosed within '^',
    it should be fine with inserting into table.

    Did anyone of you tried loading data of type more than 2000 char length.
    Could you please suggest any other alternative to loading data.

    Thanks in advance
    sudhav17

  5. #5
    Join Date
    Apr 2004
    Posts
    246
    by default sqlloader truncs all columns to 2000 chars. you need to specify the column length as 4000 within the ctl file.
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  6. #6
    Join Date
    Sep 2004
    Posts
    16

    thanks a lot, it worked

    thank you Shoblock, I tried by specifying the length and it worked.
    Thanks a lot
    sudhav17

Posting Permissions

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