We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
COURSE of the MONTH
13 days, 21 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Oracle sqlldr question
Posted on 2011-03-09
I have been running sqlldr on Oracle 11g using this command:
sqlldr myUser/myPWD@myCONN control='d:/sqlload/new/te
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?
sqlldr myUser/myPWD@myCONN control='d:/sqlload/new/te
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
)
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
8
6
5- +2
24 Comments
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/te
sqlldr myUser/myPWD@myCONN control='d:/sqlload/new/te
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..
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
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.
therefore you need to look into extending the field length for SUBJECT column and re-run sqlloader.
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.
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?
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/te
sqlldr myUser/myPWD@myCONN control='d:/sqlload/new/te
Active today
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.
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..?
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
Active today
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.
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
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
)
I could not use OPTIONALLY ENCLOSED BY any other characters (except ^) as most others (like " ' etc.) are present in the SUBJECT field contents.
Active today
>>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.
Does mySQL actually separate individual columns with tabs?
Or based on your new questions, are they space separated?
This could be the whole problem.
Active today
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.
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
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
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
There is no \t separator among fields.
But my dump data is not like this:
373af\t2011-03-11 00:17:41\t2011-03-11 07:08:18\t001396f40000c808
There is no \t separator among fields.
Active today
>>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...
then in the sqlLoader control file:
...
fields terminated by '^'
...
>>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...
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...
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.?
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...
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.?
Active today
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.
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.
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
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
Featured Post
The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
This video shows how to Export data from an Oracle database using the Original Export Utility. The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses
Course of the Month13 days, 21 hours left to enroll
800 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.