Oracle sqlldr question

try get a log on error records

which will tell you what are the records that are getting error out

run the sqlldr and specify log file. then u can view the log to get the error as suggested by pinkuray:

sqlldr myUser/myPWD@myCONN control='d:/sqlload/new/test/loader1.ctl' log='/sqlload/new/test/loader1.log'

sorry that i left out the 'd:' in the log entry

Thank you all for your help. I have attached the log file. It says it read 60092 records -- actual number of records in the source text file changes2.txt. But only first about 48K records get imported to the db.
The changes2.bad has about 50 records -- it seems some max field length  issue. Whatever that I will look at later but I need to see why that 12K records are not going through..

SQL*Loader: Release 10.2.0.1.0 - Production on Thu Mar 10 00:36:30 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Control File:   D:\sqlload\new\test\loader2.ctl
Data File:      d:/sqlload/new/test/changes2.txt
  Bad File:     D:\sqlload\new\test\changes2.bad
  Discard File:  none specified
 
 (Allow all discards)

Number to load: ALL
Number to skip: 1
Errors allowed: 50
Bind array:     64 rows, maximum of 2000000 bytes
Continuation:    none specified
Path used:      Conventional

Table myTAB1, loaded from every logical record.
Insert option in effect for this table: TRUNCATE
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
CHANGE_KEY                          FIRST     *  WHT      CHARACTER            
CREATED_ON                           NEXT     *  WHT      DATE yyyy-mm-dd hh24:mi:ss
LAST_UPDATED_ON                      NEXT     *  WHT      DATE yyyy-mm-dd hh24:mi:ss
SORT_KEY                             NEXT     *  WHT      CHARACTER            
OWNER_ACCOUNT_ID                     NEXT     *  WHT      CHARACTER            
DEST_PROJECT_NAME                    NEXT     *  WHT      CHARACTER            
DEST_NAME                            NEXT     *  WHT      CHARACTER            
OPEN                                 NEXT     *  WHT      CHARACTER            
STATUS                               NEXT     *  WHT      CHARACTER            
NBR_PATCH_SETS                       NEXT     *  WHT      CHARACTER            
CURRENT_PATCH_SET_ID                 NEXT     *  WHT      CHARACTER            
SUBJECT                              NEXT     *  WHT      CHARACTER            
ROW_VERSION                          NEXT     *  WHT      CHARACTER            
CHANGE_ID                            NEXT     *  WHT      CHARACTER            
TOPIC                                NEXT     *  WHT      CHARACTER            

Record 14424: Rejected - Error on table myTAB1, column SUBJECT.
Field in data file exceeds maximum length
Record 14856: Rejected - Error on table myTAB1, column SUBJECT.
Field in data file exceeds maximum length
Record 14912: Rejected - Error on table myTAB1, column SUBJECT.
Field in data file exceeds maximum length
Record 15956: Rejected - Error on table myTAB1, column SUBJECT.
Field in data file exceeds maximum length
Record 18237: Rejected - Error on table myTAB1, column SUBJECT.
Field in data file exceeds maximum length
Record 19143: Rejected - Error on table myTAB1, column SUBJECT.
Field in data file exceeds maximum length
Record 26699: Rejected - Error on table myTAB1, column SUBJECT.
Field in data file exceeds maximum length
Record 27609: Rejected - Error on table myTAB1, column SUBJECT.
Field in data file exceeds maximum length
Record 29223: Rejected - Error on table myTAB1, column SUBJECT.
Field in data file exceeds maximum length
Record 29224: Rejected - Error on table myTAB1, column SUBJECT.
Field in data file exceeds maximum length
Record 29274: Rejected - Error on table myTAB1, column SUBJECT.
Field in data file exceeds maximum length
Record 32120: Rejected - Error on table myTAB1, column SUBJECT.
Field in data file exceeds maximum length
Record 32346: Rejected - Error on table myTAB1, column SUBJECT.
Field in data file exceeds maximum length
Record 32667: Rejected - Error on table myTAB1, column SUBJECT.
Field in data file exceeds maximum length
Record 32884: Rejected - Error on table myTAB1, column SUBJECT.
Field in data file exceeds maximum length
Record 34081: Rejected - Error on table myTAB1, column SUBJECT.
Field in data file exceeds maximum length
Record 35143: Rejected - Error on table myTAB1, column SUBJECT.
Field in data file exceeds maximum length
Record 35209: Rejected - Error on table myTAB1, column SUBJECT.
Field in data file exceeds maximum length
Record 37902: Rejected - Error on table myTAB1, column SUBJECT.
Field in data file exceeds maximum length
Record 38546: Rejected - Error on table myTAB1, column SUBJECT.
Field in data file exceeds maximum length
Record 38866: Rejected - Error on table myTAB1, column SUBJECT.
Field in data file exceeds maximum length
Record 40836: Rejected - Error on table myTAB1, column SUBJECT.
Field in data file exceeds maximum length
Record 44345: Rejected - Error on table myTAB1, column SUBJECT.
Field in data file exceeds maximum length
Record 44346: Rejected - Error on table myTAB1, column SUBJECT.
Field in data file exceeds maximum length
Record 45865: Rejected - Error on table myTAB1, column SUBJECT.
Field in data file exceeds maximum length
Record 46470: Rejected - Error on table myTAB1, column SUBJECT.
Field in data file exceeds maximum length
Record 47405: Rejected - Error on table myTAB1, column SUBJECT.
Field in data file exceeds maximum length
Record 47406: Rejected - Error on table myTAB1, column SUBJECT.
Field in data file exceeds maximum length
Record 47742: Rejected - Error on table myTAB1, column SUBJECT.
Field in data file exceeds maximum length
Record 49117: Rejected - Error on table myTAB1, column SUBJECT.
Field in data file exceeds maximum length
Record 50241: Rejected - Error on table myTAB1, column SUBJECT.
Field in data file exceeds maximum length
Record 50242: Rejected - Error on table myTAB1, column SUBJECT.
Field in data file exceeds maximum length
Record 52678: Rejected - Error on table myTAB1, column SUBJECT.
Field in data file exceeds maximum length
Record 52697: Rejected - Error on table myTAB1, column SUBJECT.
Field in data file exceeds maximum length
Record 53143: Rejected - Error on table myTAB1, column SUBJECT.
Field in data file exceeds maximum length
Record 55021: Rejected - Error on table myTAB1, column SUBJECT.
Field in data file exceeds maximum length
Record 55022: Rejected - Error on table myTAB1, column SUBJECT.
Field in data file exceeds maximum length
Record 55024: Rejected - Error on table myTAB1, column SUBJECT.
Field in data file exceeds maximum length
Record 55072: Rejected - Error on table myTAB1, column SUBJECT.
Field in data file exceeds maximum length
Record 55077: Rejected - Error on table myTAB1, column SUBJECT.
Field in data file exceeds maximum length
Record 55092: Rejected - Error on table myTAB1, column SUBJECT.
Field in data file exceeds maximum length
Record 55097: Rejected - Error on table myTAB1, column SUBJECT.
Field in data file exceeds maximum length
Record 55765: Rejected - Error on table myTAB1, column SUBJECT.
Field in data file exceeds maximum length
Record 56550: Rejected - Error on table myTAB1, column SUBJECT.
Field in data file exceeds maximum length
Record 56552: Rejected - Error on table myTAB1, column SUBJECT.
Field in data file exceeds maximum length
Record 57114: Rejected - Error on table myTAB1, column SUBJECT.
Field in data file exceeds maximum length
Record 57999: Rejected - Error on table myTAB1, column SUBJECT.
Field in data file exceeds maximum length
Record 58228: Rejected - Error on table myTAB1, column SUBJECT.
Field in data file exceeds maximum length
Record 58717: Rejected - Error on table myTAB1, column SUBJECT.
Field in data file exceeds maximum length
Record 58721: Rejected - Error on table myTAB1, column SUBJECT.
Field in data file exceeds maximum length
Record 60092: Rejected - Error on table myTAB1, column SUBJECT.
Field in data file exceeds maximum length

MAXIMUM ERROR COUNT EXCEEDED - Above statistics reflect partial run.

Table myTAB1:
  60041 Rows successfully loaded.
  51 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Space allocated for bind array:                 247680 bytes(64 rows)
Read   buffer bytes: 2000000

Total logical records skipped:          1
Total logical records read:         60092
Total logical records rejected:        51
Total logical records discarded:        0

Run began on Thu Mar 10 00:36:30 2011
Run ended on Thu Mar 10 00:38:07 2011

Elapsed time was:     00:01:36.44
CPU time was:         00:00:04.49

Select allOpen in new window

the log shows "MAXIMUM ERROR COUNT EXCEEDED - Above statistics reflect partial run." so it actually stop from continuing to insert the rest of the data.

therefore you need to look into extending the field length for SUBJECT column and re-run sqlloader.

>>Errors allowed: 50


Try to change the error parameter to something higher, like 15000 or so.

as OP_Zaharin: said 1st check the column length and then as gatorvip: said change to higher numbers of records to be stored in .bad file.

after making these changes try to rerun and check if you still face any error while importing the data to your DB.

Thank you for the help.
The source table (also in the dump text file) has the subject field value of varchar2(255) and the field length of the target table is varchar2(2000), So it is not the length of the target table causing the matter.
I found if the file has a specific character (tab I think) in the subject field, then the row gets rejected. I attached the screenshot of the subject filed of one of such rows in the source table.
How do I import those records? Also how do I change the error parameter from current (50) default I think to 15000?

you can use that while issuing your sqlloader command like below:

sqlldr myUser/myPWD@myCONN control='d:/sqlload/new/test/loader1.ctl'errors=1000000

try now...

You can mess with the error count all day long.  That doesn't fix the problem.  It just captures more errors.

You say that you think the issue is a 'tab' character in one of the fields.

That just happens to be your field delimeter:
...
fields terminated by X'09'
...

You likely have an extra tab in the file being loaded and what you think is being loaded into the SUBJECT field is likely trying to be placed into a different field causing the error.

Check one of the failed rows and see if you have an extra tab or two.  Maybe a varchar2 field had a tab in it in addition to the delimiters.

Thank you all for the help.
I would rather try to fix the error as I cannot leave the 12K records without loading.
Yes, the problem is with the TAB characters in some of the row values of the the source table SUBJECT field.

If I query the source database -- I see the Tab symbol as in the previous image attachment.
If I look at the dump file -- I see "\t" characters (without the quotes) in for those TAB characters under the SUBJECTS heading in the text file. The delimited for Sql loader to parse the text file is X'09' (which is tab I think). In the dump text file I do not see "\t" characters (without the quotes) as delimiter but the those appear like space character(s).

Attached is a part of one of the failed rows in the sql loader .BAD file under the SUBJECT field.

So SQL loader is taking \t character as tab and trying to put rest part after the tab character in the next field!!!

Should I change the SQL loader delimiter ? Or is there any other way to fix..?

IKOXYZ \tNeed an Album art icon for each video item

Select allOpen in new window

When you extract the data from the table, you need 'optional' delimiters.  Say double quotes or something that CANNOT be found in the text.

Then use: optionally enclosed by '"'
http://www.orafaq.com/wiki/SQL*Loader_FAQ

If you cannot figure this out, please post how you are extracting the data.

Thank you,

I tried with optionally enclosed by '^' but it did not work. The same rows with \t characters in SUBJECT field got excluded.

In the source extraction script I changed to:

-----
#!/bin/sh
mysql -h myco.mot.com -u myuser -pmypwd mydb << -EOF
select f1, f2,..., concat('^', subject, '^') as subject, .. from mytab;
exit
EOF
-----

One sample entry of the dump file is:
373af      2011-03-11 00:17:41      2011-03-11 07:08:18      001396f40000c808      1002402      test      refs/heads/dev_olyyo      Y      n      4      4      ^IKOTEST-11690 \tNeed an Album art icon for each video item \t for Letter "H"^      15      51208      NULL

ANd I changed my sql loader control file as attached

OPTIONS (SKIP = 1 , bindsize = 2000000)
load data
 infile 'd:/sqlload/new/test/test1.txt'
 TRUNCATE
 INTO TABLE myTab1
 FIELDS TERMINATED BY X'09' OPTIONALLY ENCLOSED BY '^'
 TRAILING NULLCOLS
(
f1, 
f2 DATE 'yyyy-mm-dd hh24:mi:ss',  
f3 DATE 'yyyy-mm-dd hh24:mi:ss',
subject, 
f5
)

Select allOpen in new window

I could not use OPTIONALLY ENCLOSED BY any other characters (except ^) as most others (like " ' etc.) are present in the SUBJECT field contents.

>>select f1, f2,..., concat('^', subject, '^') as subject, .. from mytab;

Does mySQL actually separate individual columns with tabs?

Or based on your new questions, are they space separated?

This could be the whole problem.

I will try to use UNIX TR or SED commands to get rid of the \t file after the dump file is created -- in case I cannot resolve the issue with changes in sqlldr script.

Yes mySQL actually separate individual columns with multiple spaces (NOT by \t characters). I have attached some part of the dump file.
dumpdata.txt

Thank you.

But my dump data is not like this:
373af\t2011-03-11 00:17:41\t2011-03-11 07:08:18\t001396f40000c808\t1002402\ttest\trefs/heads/dev_olyyo\tY\tn\t4\t4\t^IKOTEST-11690 \tNeed an Album art icon for each video item \t for Letter "H"^\t15\t51208\tNULL

There is no \t separator among fields.

>>Yes mySQL actually separate individual columns with multiple spaces (NOT by \t characters).
>>There is no \t separator among fields.


That is the problem.  Why are you telling SQL*Loader " fields terminated by X'09'" when the fields are NOT separated by a TAB?

You need to change how you are extracting your data.

sqlLoader either needs delimited fields or fixed width.

Just spooling out the output of variable width text fields will not set up fixed width.

I'm not a MySQL person but try this:
select f1, f2,..., concat(f1,'^',f2,'^',f3...) as myrow, .. from mytab;

then in the sqlLoader control file:
...
fields terminated by '^'
...

By default mySQL dump files' column data are separated by TABS. In my last attached file you will find TAB between adjacent column values in a row. But these is no "\t" character.

And that is why my sql load with FIELDS TERMINATED BY X'09' worked so far except with this specific table that has \t character in some specific field.

=============
select f1, f2,..., concat(f1,'^',f2,'^',f3...) as myrow, .. from mytab;

Then
fields terminated by '^'
=============

I could try the above. But I will need changes in lot of source control files and other codes. Given my current dump file, cannot I continue to use  FIELDS TERMINATED BY X'09' but somehow ignore the \t characters in the specific field.?

You are sending me conflicting information

In http:#a35112699 you said:  Yes mySQL actually separate individual columns with multiple spaces

Now you say: By default mySQL dump files' column data are separated by TABS

These DO NOT mean the same things.

>>But these is no "\t" character.

Please read my post in http:#a35112674

To copy what I said:
You will need to replace '\t' characters with a real TAB character.  I did this just in case the hard-tabs were stripped out when I posted it.

>>but somehow ignore the \t characters in the specific field.?

Then use the optionally enclosed by in the sample I referenced above.