sql * loader question -- pls help!

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

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.

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!!!

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]

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!