DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: sql * loader question -- pls help!

Hybrid View

  1. #1
    Join Date
    May 2001
    Posts
    285

    Angry

    Hi gurus,

    After using bcp to export a big table's data from SQL Server, I used SQL*Loader to load that data into Oracle. Among 3 million rows of records, 4,000+ records were rejected because 'Field in data file exceeds maximum length'.

    However, I have EXACTLY the same table definition in both dbms which is varchar2(2000). So if that column(CallEventLog) fits SQL Server, it should fit Oracle as well. And also, by looking at the data in the .bad file, I don't think they are even close to 2000 characters. I even substr(:CallEventLog, 1, 2000) in my control file for that column, but still got the same erros.

    Any idea?

    FYI -- the problem column(CallEventLog)'s data have '\n' in it. Does it play a role inside?

    I am using Oracle 9i on a win2K box.

    Let me know if you need any other info.

    Thanks a lot,
    Elaine

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Could you post your control file here?

    Is it possible that your data was in delimited (not fixed) format and that your CallEventLog was declared as CHAR in the controlfile, without explixitely providing its length? In that case Oracle takes the default value of 256 (or is it 255?) and rejects anything that is larger than that.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    May 2001
    Posts
    285

    Here is the control file...

    Actually, I didn't decalre this column in the control file (is CHAR the default?), since when I tried to decalre it as varchar2(2000), I got the error message

    SQL*Loader-350: Syntax error at line 34.
    Expecting "," or ")", found "varchar2".
    CallEventLog varchar2(2000) "substr(:CallEventLog,1,2000)",


    And my control file looks like:

    options (direct=true, rows=50000)
    load data
    infile 'e:\temp\mig\cd_bcp_all_test.csv' "str '+++'"
    append
    discardmax 10000
    into table calldetail_bcp_new
    fields terminated by '%%'
    TRAILING NULLCOLS
    (CallId,
    CallType,
    ... -- omit some columns here
    CallEventLog varchar2(2000) "substr(:CallEventLog,1,2000)",

    ...)

    Thanks!!!


  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    I'm pretty sure your problems come from not declaring that field's type and its length. Change your controlfile to :

    ...
    into table calldetail_bcp_new
    fields terminated by '%%'
    TRAILING NULLCOLS
    (CallId,
    CallType,
    ... -- omit some columns here
    CallEventLog char (2000) "substr(:CallEventLog,1,2000)",
    ...)

    BTW, I don't understand why you are using "substr ()" part, you said the data was originaly stored in VARCHAR2(2000) column! I would simply throw that out from control file.

    Now why were you rejected with VARCHAR2 specification in the controlfile? Because SQL*Loader does not recognise that type as its valid internal or external datatype. Don't confuse this with database datatype, we are talking about SQL*Loader's own datatypes. So specifying CHAR datatype in SQL*Loader simply means the data in that field is character string, can be with variable or fixed length. Do not confuse this with database type CHAR which is fixed length.

    When you don't specify field's type SQL*Loader takes it a CHAR (external) datatype with implicit length limit of 255. For longer fields you have to declare datatype explicitely, specifying also its maximum length.


    [Edited by jmodic on 01-30-2002 at 05:01 PM]
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    May 2001
    Posts
    285

    It works!!!

    Thanks Jurij for all your wonderful explanation!

    It appears to work now...

    I will take the substr part out. The reason for me to put it over there is since I got 'Field in data file exceeds maximum length' error on that column AND I assume that column should be able to hold 2000 characters. But actually, as you just point outed, that column by default will only hold 256 characters, which is the real reason for me to get that nasty error.

    Thanks again and really appreciate your help!





Posting Permissions

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


Click Here to Expand Forum to Full Width