Solved

Oracle sqlldr question

Posted on 2011-03-09
24
2,676 Views
Last Modified: 2012-05-11
I have been running sqlldr on Oracle 11g using this command:

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

It works.

But I just noticed that out of the 62K records of test1.txt, the sqlldr is only importing first 48K records. It is ignoring the remaining records of test1.txt. It is not the data issue because the same data get imported if those are at the 1st part of the test1.txt file.

Is bindsize = 2000000 has got to do anything with this?

OPTIONS (SKIP = 1 , bindsize = 2000000)
load data
 infile 'd:/sqlload/new/test/test1.txt'
 TRUNCATE
 INTO TABLE myTab1
 fields terminated by X'09'
 TRAILING NULLCOLS
(
f1, 
f2 DATE 'yyyy-mm-dd hh24:mi:ss',  
f3 DATE 'yyyy-mm-dd hh24:mi:ss',
f4, 
f5
)

Open in new window

0
Comment
Question by:toooki
  • 8
  • 6
  • 5
  • +2
24 Comments
 
LVL 4

Expert Comment

by:pinkuray
ID: 35091852
try get a log on error records
0
 
LVL 4

Expert Comment

by:pinkuray
ID: 35091853
which will tell you what are the records that are getting error out
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35091888
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'
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35091891
sorry that i left out the 'd:' in the log entry
0
 

Author Comment

by:toooki
ID: 35092016
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

Open in new window

0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35092526
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.
0
 
LVL 20

Expert Comment

by:gatorvip
ID: 35094731
>>Errors allowed: 50


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

Expert Comment

by:pinkuray
ID: 35096660
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.
0
 

Author Comment

by:toooki
ID: 35098438
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? subject field contentAlso how do I change the error parameter from current (50) default I think to 15000?
0
 
LVL 4

Expert Comment

by:pinkuray
ID: 35103234
you can use that while issuing your sqlloader command like below:

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

Expert Comment

by:pinkuray
ID: 35103235
try now...
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35103394
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.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:toooki
ID: 35111828
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

Open in new window

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35111873
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.
0
 

Author Comment

by:toooki
ID: 35112326
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
)

Open in new window

0
 

Author Comment

by:toooki
ID: 35112335
I could not use OPTIONALLY ENCLOSED BY any other characters (except ^) as most others (like " ' etc.) are present in the SUBJECT field contents.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35112588
>>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.
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 375 total points
ID: 35112674
Here's a test that loads.

Given the table:
drop table tab1 purge;
create table tab1(
f1 varchar2(10),
f2 DATE,
f3 DATE,
f4 varchar2(20),
f5 varchar2(10),
f6 varchar2(20),
f7 varchar2(20),
f8 char(1),
f9 char(1),
f10 number,
f11 number,
f12 varchar2(100),
f13 number,
f14 number,
f15 varchar2(20)
);


The control file is below.  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.
load data
 infile *
 TRUNCATE
 INTO TABLE Tab1
 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',
f4,
f5,
f6,
f7,
f8,
f9,
f10,
f11,
f12,
f13,
f14,
f15
) 
begindata
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

Open in new window

0
 

Author Comment

by:toooki
ID: 35112699
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
0
 

Author Comment

by:toooki
ID: 35112708
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.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35112732
>>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 '^'
...



0
 

Author Comment

by:toooki
ID: 35112822
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.?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35113216
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.
0
 
LVL 23

Assisted Solution

by:OP_Zaharin
OP_Zaharin earned 100 total points
ID: 35118491
i've done some projects that requires me to handle fields that contain special characters (rtf format, html or etc) from other database into Oracle. some of the issues is varchar field in Oracle are not able to handle these data. even though it looks like that data is only a few lines but it actually exceed the varchar2 datatype limits .

there are few methods that i would use:

1- use CLOB datatype in Oracle.

2- specify a start and end 'marker' during creation of the text file for that particular field. and specify that marker in sqlloader script for that particular field. then do a cleanup after its loaded:  
subject     ENCLOSED BY '<stload>' AND '<enload>'

3- specify field size in the sql script. i suggest to use the max of varchar2(4000) for subject field in your table and specify char(4000) in the sqlloader script:
f1,
f2 DATE 'yyyy-mm-dd hh24:mi:ss',  
f3 DATE 'yyyy-mm-dd hh24:mi:ss',
subject char(4000),
f5
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now